Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Learning how to setup a Star Schema

    Hello everyone,   Prior to this I have only used flat tables so the world of data modeling is still really new to me and I hope to learn how to utilize it as it looks to be way more per...
Data Protection
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    Anonymous Well, in "proper" star schema design, your fact table really only contains columns that are used to relate to dimension tables and the actual fact (numbers) you wish to report on. The dimensional columns in this table repeat values. The dimension tables should only contain a single row for each dimensional identifier and thus these tables are the 1 side of the relationship and the fact table is the many (*) side of the relationship. So your dimension tables filter your fact table but not vice-versa. 

     

    The AdventureWorks database is a good example of a database setup to provide a star schema data model. Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

     

    The issue with this overall though is that rarely, if ever, in the real world that data can perfectly conform to a star schema. It's tough to tell from just an image of your data model, but it would appear to me that you have multiple fact tables involved and you are going to need to merge those into a single fact table for starters.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

Anonymous Well, in "proper" star schema design, your fact table really only contains columns that are used to relate to dimension tables and the actual fact (numbers) you wish to report on. The dimensional columns in this table repeat values. The dimension tables should only contain a single row for each dimensional identifier and thus these tables are the 1 side of the relationship and the fact table is the many (*) side of the relationship. So your dimension tables filter your fact table but not vice-versa. 

 

The AdventureWorks database is a good example of a database setup to provide a star schema data model. Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

 

The issue with this overall though is that rarely, if ever, in the real world that data can perfectly conform to a star schema. It's tough to tell from just an image of your data model, but it would appear to me that you have multiple fact tables involved and you are going to need to merge those into a single fact table for starters.