One or multiple fact tables?

Question

I am trying to build a data mart.

I have lot of dimensions, and couple of measures - facts. Every measure is connected to all dimensions in term of business. There is the standard approach that there will be one big fact table with all measures.

But I have an idea: What If I have separate fact tables for each measure? What it will do with database performance, solution extensibility etc?

EDIT::: there will be huge solution based on olap cubes in really complex corporate environment. So the needs are easy extensibility and maintenance at first, then performance

Answer 1

cliff notes: I see scalability issues galore with the notion of one fact table / measure.

if your goal is "extensibility and maintenance", then you might consider following an industry-wide standard methodology...such as Kimball and group your facts first by business process and second by granularity with conformed dimensions. This, I'd be happy to argue, will provide maximum flexibility & scalability while also ensuring extensibility and maintenance via standardization.

Plus, your SAN-admins will hate your guts if you design a separate fact table for each measure because you are effectively increasing the space requirements by the number of measures...at least initially while all your measures currently fit in a single fact table.

Query performance against the DW will also be an issue...reports and/or ad-hoc queries will have to scan (or seek...if your san-admins are still talking to you and are generous enough to grant you the necessary space to properly index your litter of fact tables) anytime you need to combine more than 1 measure.

We haven't even discussed the olap cubes yet, but I'm already dreading the processing issues you will have. Full table-scans galore ...or partition-scans...if you implement partitioning in your fact tables...which you should now with SQL 2012 on the horizon...I'm looking at you ColumnStore Index!!