Forum Discussion
Data Modeling large Fact table with multi granularity vs Multi Facts
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!!
4 Replies
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.
