Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Help on my star schema

Hello everyone,

 

Here is an example of my star schema i'm planning to do on Power Bi.

 

Sorry but the schema is in french but i will try to explain 🙂

 

I have 3 fact tables :

- FAIT_FACTURE (it is actually a list of invoices)

- FAIT_FACTURE_LIGNE (the details of each lines of each factures with product quantity etc....)

- FAIT_REGLEMENT (which are the payments and a facture can have multiple payments)

And then i have a multitude of dimensions tables.

 

I know that it is a bad idea to connect directly the fact_table between them. That's why i created a dimension table (DIM_FACTURE) that connect all 3 fact tables with the invoice ID.

At this point i think i'm on the right path.

 

The question i have is on the dim_calendar table on the right side of the schema. I have a date on FAIT_FACTURE and FAIT_REGLEMENT table. And i need to be able to analyse data by both dates.

Should i create only one dimension time table and connect it to both fact table (like it is on the schema) OR should i create two date tables, one for each fact table ?

 

I hope that i'm clear 😁

 

Thanks in advance for your answer

Need Help
Tips and Tricks

6 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    2 years ago

    Anonymous,

     

    It's best to use one date table in your model. This will enable you to filter each fact table using a central date table. Your model is actually a snowflake schema due to dimension tables having relationships with other dimension tables. In a star schema, DIM_PRODUCT and DIM_PRODUCT_CATEGORIE would not exist in the model but would instead be merged into one table DIM_PRODUCTS_CATEGORIES. The same would apply to other dimension tables with this structure.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thanks for your answer. Actually i made a small modification to my schema, here is the final version :

       

      Does it look more like a star schema right now ?

       

      Also i've created the schema in power bi desktop. And i can't create two active relationship between date table and the two fact table. Only one would be active and one inactive (dotted line).  This is a normal behaviour ? And this is where USERELATIONSHIP() comes in ?

       

      As the FAIT_FACTURE table is the central fact table of my model, the relation that has to be active is the one between FAIT_FACTURE and the calendar table ? All other relations between fact table and time table are secondary ?

       

      Sorry for all the questions and thanks again.

      • DataInsights's avatar
        DataInsights
        Icon for Super User rankSuper User
        2 years ago

        Anonymous,

         

        It's still a snowflake schema. In a star schema, dimension tables don't have relationships with other dimension tables. These three tables would be merged into one table at the product grain, with a column for each product attribute:

         

        The date table needs only one relationship with each fact table.