Our OLTP systems use several surrogate keys .Now we want to create a dimensional model for our system for analysis. Should we keep OLTP system surrogate keys and natural keys and also create one more datamart surrogate key? or shall we ignore the OLTP system surrogate key and just keep the natural key from OLTP and datamart surrogate key?
Asked
Active
Viewed 63 times
1 Answers
0
The dimensional model's surrogate keys are specific to the dimensional model, and independent of any source keys you might have. You should definitely keep the natural keys and create a datamart surrogate key, but whether it is useful to also bring in the OLTP system's surrogate key as a back reference depends on whether it is useful in identifying rows back in the OLTP system- i.e. how important is that OLTP surrogate key? Normally I'd stick with just the new surrogate in the dimension and the natural key, but sometimes the surrogate key serves as the natural key too.
Rich
- 2,207
- 1
- 23
- 27