Role-playing Dimensions in Data Model
It is very common to have role-playing dimensions for things like date dimensions. Is it possible to designate a table as a role-playing dimension on the Data Model tab of a model? I tried to create a join between the date key of a date dimension and multiple foreign keys representing different dates in a fact table, but could not figure out a way to do it. Help?
2 replies
-
Hi Dave,
This can be achieved by adding multiple Select items in the Data Flow tab.
For example, if you have three date columns in the fact table, you will need to add three Select nodes in the Data Flow tab one for each of the columns. Then rename the resulting table name by clicking Select node.
Please see screenshot.
I hope this helps, -
While that will technically work, it is very inefficient as it loads multiple copies of the same dimension data into the model. I would suggest (and will likely submit a feature request for it) that there should be a more efficient solution for handling role-playing dimensions.
Dave F.