Establishing 'Making it Happen' as a 'Formal & Predictable' Discipline

Data Mart Fact Table Grain Matrix in Data Warehouse Dimensional Model	Derived Dimension Attributes Table

ENCYCLOPEDIA→ Enterprise Intelligence → - Data-Warehouse/Mart → - DW Dimensional Modeling Process →

Derived Facts table in Data Warehouse Dimensional Model

With Data Marts mapped with facts and dimensions, its time to list the set of facts and the derived facts to be used ACROSS the data warehouse and data marts.

Just like Dimensions + Attributes+ Derived Attributes matrix is the master table for details on Dimensions, the 'Fact and Derivation table' is the master table for the facts.

The FACT table listing also provides the listing of the fact tables having aggregate snapshots. Aggregate facts should be listed as separate measure with a different name in the list of Facts. However, never mix the aggregate and detail facts in one fact table. The reason for the same is that a detail grain fact is linked to the grain dimension. An aggregate fact will be linked to higher level of dimensions, which need to be represented in a separate table.

For example- I will not put the sales revenue per sales agent and the sales revenue per sales office (which is aggregation of the sales revenue across all agents linked to the office). This is because the a single record in fact table should always be linked one-on-one to the record in the 'sales agent' dimensional table. if you don't want to create the aggregate table, you can do the dynamic online data aggregation, instead of storing it.

The base fact is generally defined, when a fact can be picked-up from the source system without any derivation. The derived fact is a data field which is derived from the base fact .

TIP- It is possible that a grain of fact is not required in the dimension model, but the derived fact is needed. example, the base bill amount is not needed, but the billed-amount with tax (base billed amount * 1.2 for 20% service tax) is needed. IN these kinds of cases, it is always advisable to have the base information (base billed amount) included in the fact table, though it may not be used in the analysis. Any derived fact should have its base/source fact included in the Fact table.

Facts have different shades (please refer Sales Revenue as an example). One has to be careful in terms of giving different names to these different shades. For example, sales revenue can be: