Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hey all,
Quick question. I have a fact table that has 3 levels of granularity that will need to be aggregated. For example...Sales Counts/Price, Job Cost, and Item Cost. I'll need to sum each level. The data is pulled together by left joins...so Sales, Price, and Job Cost all repeat rows with item cost. The table is ~30Mil rows. Would you recommend breaking these down into 3 individual fact tables at their own granularity...or keep it in 1 fact table and use dax to calculate appropriately. Breaking them down would make 1 table ~13Mil, 2 table ~25Mil, and 3 table around 27Mil...so I'd assume the model would be much larger this way. Mainly concerned about the quickness and ease of dax calculations and using best practice. Thanks!!
Hey @codyraptor ,
can you give an example how the data would look like?
Do they have columns in common? If not, then there is nothing wrong with using multiple fact tables. But it really depends.
Best regards
Denis
@selimovd So here's an example of the data 'if' I were to use a single fact table.
Customer ID | Price | Job ID | Job Cost | Item ID | Item Cost |
1 | 200 | 4 | 500 | 1 | 250 |
1 | 200 | 4 | 500 | 2 | 250 |
1 | 200 | 3 | 600 | 1 | 300 |
1 | 200 | 3 | 600 | 2 | 300 |
The result would be Customer 1 has a Price Sum $200...Job 4 cost is $500 and Job 3 cost is $600...both broken down by item cost. Imagine this rough sketch...30mil rows. There are more dimensions obviously...but this is the idea. The alternative would be create a Customer Table w/Price...Job table w/Job Cost..and Item table with Item Cost...and join all 3 with dimension keys.
@selimovd Checking in to see if you had a chance to look over this. The able above is just a simple example...but all 3 levels share dimensions such as State, Product, etc... I currently have the model has a star schema with 3 fact tables. However....The requirements continue to grow and the model is slowing. Just making sure I'm using best practice.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
76 | |
63 | |
51 | |
48 |
User | Count |
---|---|
211 | |
86 | |
64 | |
59 | |
56 |