Help on my star schema

Question

Hello everyone,&nbsp;Here is an example of my star schema i'm planning to do on Power Bi.&nbsp;Sorry but the schema is in french but i will try to explain 🙂&nbsp;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.&nbsp;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.&nbsp;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 ?&nbsp;I hope that i'm clear 😁&nbsp;Thanks in advance for your answer

datainsights · Answer

Anonymous,
&nbsp;
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.

datainsights · Answer

Anonymous,
&nbsp;
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:
&nbsp;

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

anonymous · Answer

Thanks for your answer. Actually i made a small modification to my schema, here is the final version :&nbsp;Does it look more like a star schema right now ?&nbsp;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).&nbsp; This is a normal behaviour ? And this is where USERELATIONSHIP() comes in ?&nbsp;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 ?&nbsp;Sorry for all the questions and thanks again.

anonymous · Answer

DataInsightsthanks.&nbsp;When you say "The date table needs only one relationship with each fact table.", you mean one "active" relationship with each fact table ?&nbsp;For the schema itself, and the snowflake part, should i merged everything ? A product can be in multiple categories so if i merge everything, i can have multiple line with the same product (but different category) in that new table. Is this a problem ?&nbsp;Thanks again for your answers

datainsights · Answer

Anonymous,
&nbsp;
Yes, one active relationship between the date table and each fact table.
&nbsp;
Would you be able to provide sample data for the product and category tables, as well as the relevant fact table?

Forum Discussion

Related content

Star Schema Fundamentals

Star Schema: Multiple Dates

Star schema

Star Schema Relationships

Snowflake to Star Schema conversion