Consolidated Fact Table

Transactional Fact and Snapshot Fact tables are usually designed from the corresponding individual business process. Consolidated Fact table is usually designed from particular analysis (report) which required consolidation of data (derive) from multiple business processes, which they can be presented in a same grain.

The Consolidated Fact table is helpful to consolidate data from multiple processes which avoid joining fact table with fact table in the user queries. As we discussed in previous post, performance issue happened when joining two big tables. Instead, ETL processes usually use the drilling across technique to propagate the data into the consolidated fact table.

Example:

• Daily Sales Fact Table is
• aggregated sales from Sales Fact table (Transactional Fact table); and 
• consolidated the stock on hand balance from the Daily Stock On Hand Fact table (Periodic Snapshot Fact table)
• Monthly Sales Comparison Fact Table is
• aggregated sales from Sales Fact table (Transactional Fact table); and 
• consolidated the sales from this and last year showing side by side; and/or
• consolidated the sales target.