Forum Discussion

ddumas's avatar
ddumas
Frequent Visitor
8 years ago
Solved

creating a multi-fact star schema

I am new to Power BI, and am trying to understand managing relationships .  I am well versed in using star schemas.

 

Let say I have 2 fact tables:

 

Fct_Sales

Fct_Service

 

Both of these share common dimensions of:

 

Customer

Product

Time

 

Fct_sales has other non-shared dimensons of

 

SalesPerson

 

Fct_service has other non-shared dimensions of:

 

ServicePriority

 

---------------

 

I want to corrolate sales (sales$) and service (# service requests) accross common dimensions.

 

How is the data modeled in Power BI such that there are no circular relationships?  I do not want to create "alias tables" as a kluge to avoid circular relationships.  This is a very simplistic example.  Many of my projects have several fact tables sharing common dimensions.

  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi ddumas,

     

    Maybe your relationships could be like this. No loops here. You can have a try.

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

3 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    8 years ago

    Hey,

     

    basically it's no problem to have more than one fact table, some shared dimension tables and some dimension tables that are not shared.

     

    Indeed it can become a little more difficult if there are more complex relationship you have to handle, like cascading many-to-many.

     

    I strongly recommend to invest some money and buy the "Analyzing Data with Power BI and Power Pivot for Excel" by Albertro Ferrari and Marco Russo. This book is just about the data modeling aspects we have to tackle.

     

    Hopefully this gets you started

     

    Regards

    Tom

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi ddumas,

     

    Maybe your relationships could be like this. No loops here. You can have a try.

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi ddumas,

     

    Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

     

    Best Regards!
    Dale