Transformer / Star Schema

[robinsks]

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

[mariorubbo]

I would think that both methods would work fine.