Forum Discussion
Questions about Data Structure
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.
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?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
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'