Data Modeling large Fact table with multi granularity vs Multi Facts

[codyraptor]

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!!

[selimovd]

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

[codyraptor]

@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.

[codyraptor]

@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.

[Jack00711]

Hello @codyraptor,

I am in a similar situation as yours. Were you able to find a solution for this?