Role Playing dimension among multiple fact tables

0
votes

I am modelling cube in SSAS. Cube has around 20 dimensions and 6 fact tables. Some of the dimensions are common among the fact tables. e.g. Time dimension. Fact_PNL has 3 date columns for those we have 3 role playing dimensions in the dimension usages.Another fact table has 5 date columns for them as well we have separate role playing dimensions in dimension usage tab. We have a common dimension Company which is foreign key in all fact tables. We might need to combine the data from multiple facts to get final output.

  1. Should i create 6 role playing dimension for each of the fact table or use the same dimension for all fact tables?

  2. Role playing dimensions should be created when we have multiple columns pointing to the same dimension ?

1
ssasolapcube

1 Answers

1
votes
  1. It's up to you. If the role playing dimension plays the same logical role for each fact table, then I would use the same RPD for the same logical role in each fact table. But if you want to use separate ones for each fact table, maybe because you think in the future they might be used differently, then you can.

In short, either way works fine, so whatever makes the most intuitive sense to you and other users is the way you should go.

  1. Yes, that is the purpose of Role Playing Dimensions. When two or more columns in the same fact table reference the same dimension.