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