Forum Discussion
Questions about Data Structure
Hi All,
I am currently working on a data structure for PowerBI. I have the following main tables.
Accounts
Contacts (1:Many relationship to accounts, 1 account can have multiple contacts)
Services (1:Many relationship to accounts, 1 account can have multiple services)
And a Transaction report that has a list of all payments made - it has a relationship to both Accounts and Services (1: Many in each case)
This is not the final list of Tables, eventually we will be including a list of all orders placed, Cross-Sell Matrix and likely some other metrics.
Initially I had my Data Model including just a Date Dimension table, however as I've added more and more things, the Data model has gotten incresingly 'Messy' and this has resulted in one report that we designed being wrong because of multiple joins.
Reading about this - I've come across the concept of Star and Snowflake Schemas whereby a single 'Fact' table is surrounded by multiple 'Dimension' tables.
My understanding is that my Fact table is data pulled from the Source system(s) and that my Dimension table(s) are external things that I want to Model around it - e.g. Date - I'm not sure what else would be considered a Dimension - Brand? Subscription type?
I've also read that some calculations should be in a fact table whereas some calculations should be a Dimension (Sum, Average, Count etc.).
So to ask the main questions:
Of the tables listed above, what is a Fact and what is a Dimension?
Should I be moving some of the columns from what is currently a Fact table to a Dimension table (e.g. a text field for Brand in the account table, replace with an Integer and create a brand dimension table with a relationship)?
How does the Star/Snowflake schema work with 1:Many relationships (e.g. Account to Service)
What are some good training resources that could help me further (I've watched a paintload of YT videos, some of which really helped, I've read a lot from here, Blogs et.c)?
Of the tables listed above, what is a Fact and what is a Dimension?
In you case, your main fact table is almost certainly your Transactions table. The other tables you list seem like dimensions.
Should I be moving some of the columns from what is currently a Fact table to a Dimension table (e.g. a text field for Brand in the account table, replace with an Integer and create a brand dimension table with a relationship)?
Hard to know for sure without seeing some sample data.
How does the Star/Snowflake schema work with 1:Many relationships (e.g. Account to Service)
Just fine. This is generally the case, you will have a 1 on your dimension side and a Many on your fact side most often.
What are some good training resources that could help me further (I've watched a paintload of YT videos, some of which really helped, I've read a lot from here, Blogs et.c)?
A good learning resource is here: https://powerbi.microsoft.com/en-us/guided-learning/
7 Replies
Of the tables listed above, what is a Fact and what is a Dimension?
In you case, your main fact table is almost certainly your Transactions table. The other tables you list seem like dimensions.
Should I be moving some of the columns from what is currently a Fact table to a Dimension table (e.g. a text field for Brand in the account table, replace with an Integer and create a brand dimension table with a relationship)?
Hard to know for sure without seeing some sample data.
How does the Star/Snowflake schema work with 1:Many relationships (e.g. Account to Service)
Just fine. This is generally the case, you will have a 1 on your dimension side and a Many on your fact side most often.
What are some good training resources that could help me further (I've watched a paintload of YT videos, some of which really helped, I've read a lot from here, Blogs et.c)?
A good learning resource is here: https://powerbi.microsoft.com/en-us/guided-learning/
2 Different answers, 2 different suggestions - no wonder I'm struggling haha!
Based on the info provided (thanks very much - has been enlightening to read), I'm still trying to work out how multiple Stars that have common relationships work.
e.g. The transactions table is a table of all payments made (where money changed hands) - if I set that as my Fact table, and have Account, Service and Date as my Dimensions, then when I have an Order table (a table containing all ordered services, regardless of whether they have been paid for or not) and set that as another Fact table, also with Account, Service and Date as a Dimension.
Basically the exact scenario described here: https://www.thoughtspot.com/fact-and-dimension/schemas-scale-how-avoid-common-data-modeling-traps - The solution as written, I'm not understanding - if someone could elaborate on the situation where 2 fact tables sharing common dimensions, that might help me progress this.
PowerBIPilgrim See image below. You use your dimensions for the columns that are shared between fact tables so that everything filters correctly.
PowerBIPilgrim , Try to be in Start Schema as far as possible.
Dimensions are typically table against which we measure the performance like Account, Date(Time) and category. In your case contact and service are facts. Contains what need to measured
In the case of start Schema, If we look at the fact. It is in central. When you have multiple facts and look at a dimension that also looks at central.https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/ and https://www.sqlbi.com/blog/marco/2017/10/02/why-data-modeling-is-important-in-powerbi/
Youtube has lot of good resources around it. Power BI MBAS Gallery has good resources https://community.powerbi.com/t5/MBAS-Gallery/bd-p/MBAS_Gallery
