Questions about Data Structure

[PowerBIPilgrim]

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)?

[Greg_Deckler · Accepted solution]

@PowerBIPilgrim 

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/ 

[PowerBIPilgrim]

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.

[Greg_Deckler]

@PowerBIPilgrim See image below. You use your dimensions for the columns that are shared between fact tables so that everything filters correctly.

[PowerBIPilgrim]

Thanks for that - I've created a new test PowerBI setup and implemented a Star Schema - it has made the table mapping a lot more simple. I've also been able to replicate some of the reports I'd authored, without too much difficulty.

The Fact table being the Transaction report and the Dimensions being Account, Contact, Service and Date

However, one of the reports looks at the start and end date of a Subscription in the Service table only, in order to display how many active subscriptions we had last month, the month before etc. etc.

If my current understanding is correct - I shouldn't be reporting solely on a Dimension table?

In which case - do I need to make service a Fact table or do I need to change the columns in the Finance report (either in the SQL SP or via calculated fields in PowerBI) to create this report?

[Greg_Deckler]

@PowerBIPilgrim Well, data modeling gets messy and very rarely will you have a 100% "pure" data model where everything is perfectly need and tidy. Besides, when I classified fact or dimension, that was just a guess on my part without seeing anything other than your description!!

Sounds like you want something like Open Tickets though - https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147

[PowerBIPilgrim]

I think I have all that I need for now, If it's a case of 'in the real world, this happens because the real world isn't perfect' then I can live with that as opposed to 'you shouldn't do this because bad things will happen'

[amitchandak]

@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