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