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