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

Aversion

separate fact table for each measure? can you be more specific on what you are thinking? – Gallivant

separate fact table for every measure means that there will be as many fact tables as there is measures - every with only one measure, otherwise there can be one big fact table with all measures. – Aversion

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

Belldas

Answer 2

If the granularity of all the measures are the same, then keep them in the same table. You only start using multiple fact tables when you have facts of differing levels of granularity. Seeing as you said all of your facts are linked to all of your dimensions, then at this stage it looks like you only need one fact table.

Akeylah

Ther can be various change requests like make dimension more or less granular, adding facts, adding dimensions, and on top of these change requests new ETL would be needed to do, and ETL can be consuming a lot of cpu power for long time - millions of rows would be computed again etc. So I rather look on that in this way - make extensibility and maintenance as easy as posible. – Aversion

changing dimension granularity, adding facts, adding diemsnison...all of these will require ETL dev-work regardless of whether or not you isolate each fact in separate fact tables or not. – Belldas