question

ovonemty avatar image
0 Votes"
ovonemty asked CarrinWu-MSFT commented

How to model Role Playing Dimensions in SSAS?

For each row in a fact table I have a Client Key and a Final Client key.



(‘Final Client’ is sort of a ‘Parent’ of a ‘Client’.)



In the backend I only have the Fact table and DimClient.



Fact:
- Client Key
- Final Client Key


Client:
- Client Key



I always join to Client… I use Client or Final Client Key depending on what I want to retrieve…



I am trying to model it in SSAS to later consume it from excel and/or power bi…





My question is: what is more performant and better in terms of modeling… To consume DimClient twice in SSAS, one as Client and one as Final Client (and then create 2 different relationships). Or is it better to consume DimClient once, and then use a calculated table to create Final Client?










sql-server-analysis-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @maca128-1653, we have not get a reply from you. Did any answers could help you? If there have any answers helped you, please do "Accept Answer". If not, please let us know. By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
AlexeiStoyanovsky avatar image
0 Votes"
AlexeiStoyanovsky answered

The design will primarily depend on the analyses that need to be performed. One other option is to move FinalKey to the dimension.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered

Hi @maca128-1653,

Welcome to Microsoft Q&A!

Please refer to below blogs:

Role-playing Dimensions
SQL Server Analysis Services Best Practices for Performance Optimization - Part 2 of 4


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.