One or multiple fact tables?

database-design data-warehouse business-intelligence extensibility fact-table
John picture John · Mar 19, 2012 · Viewed 8.4k times · Source

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

Bill Anton picture Bill Anton · Mar 22, 2012

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

Related questions

Benefits of using Staging Database while designing Data Warehouse

I am in process of designing a Data Warehouse Architecture. While exploring various options to Extract data from Production and putting into Data Warehouse, I came across many articles which mainly suggested following two approaches - Production DB ----> …

Read More
Why primary key is (not) required on fact table in dimensional modelling?

I have heard a few references that pk is not required on fact table. I believe every single table should have a pk. How could a person understand a row in a fact table if there is no pk and 10+ …

Read More
Should OLAP databases be denormalized for read performance?

I always thought that databases should be denormalized for read performance, as it is done for OLAP database design, and not exaggerated much further 3NF for OLTP design. PerformanceDBA in various posts, for ex., in Performance of different aproaches to …

Read More