If you have a Dimension in Transformer with several levels, and each of those levels, contains a source which is a fact, say a code representing a data item in a fact table.
For example ColACode,ColBCode,ColCCode etc.
There are also 3 lookup tables containg corresponding codes with a description for example
LKP 1 ( ColACode, descriptionA), LKP 2 ( ColBCode, descriptionA) and LKP 3 ( ColACode, descriptionC).
Which is the correct method of using the lookups in the levels. Is it best to keep the lookups as separate tables and have an arrangement like:-
DIMENSION NAME LKP's
Level 1 Source = ColACode Source LKP 1 ( ColACode)
Label LKP 1 (descriptionA)
Level 2 Source = ColBCode Source LKP 2 ( ColBCode)
Label LKP 2 (descriptionB)
Level 3 Source = ColCCode Source LKP 3 ( ColCCode)
Label LKP 3 (descriptionC)
Or is it better to merge the lookups into one table for that dimension so that you have the following arrangement. LKP X (ColACode, descriptionA, ColBCode, descriptionA, ColACode, descriptionC).
DIMENSION NAME LKP
Level 1 Source = ColACode Source LKP X ( ColACode)
Label LKP X (descriptionA)
Level 2 Source = ColBCode Source LKP X ( ColBCode)
Label LKP X (descriptionB)
Level 3 Source = ColCCode Source LKP X ( ColCCode)
Label LKP X (descriptionC)
Is one method correct and the other wrong?
I hope that this makes sense, but I think its something quite basic but which is also difficult to find any information about.
Thanks
Kevin
I would think that both methods would work fine.