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/
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'