Forum Discussion
Help on my star schema
Super UserAnonymous,
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.
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
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.
DataInsightsthanks.
When you say "The date table needs only one relationship with each fact table.", you mean one "active" relationship with each fact table ?
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 ?
Thanks again for your answers
- DataInsights
Anonymous,
Yes, one active relationship between the date table and each fact table.
Would you be able to provide sample data for the product and category tables, as well as the relevant fact table?