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

Data Mart Dimension Fact table Matrix in Data Warehouse Dimensional Model	Derived Facts table in Data Warehouse Dimensional Model

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

Data Mart Fact Table Grain Matrix in Data Warehouse Dimensional Model

Data Mart typically has one to one link with fact table. The fact table grain is the most granular level of business event or entity which can provide the level of detail needed by Data Mart.

Next step after Data-Marts + Business themes matrix is to firm-up the fact table grain for your dimensional model. What is fact table grain?

A fact table grain is the event OR entity, which represents the smallest grain of the Data Mart. For example, in sales revenue analysis data mart of a life insurance company, We have the following options in terms of a grain:

Life Insurance Policy Sold
The life insurance coverage sold- A life insurance policy can be a combination of multiple coverages. I can have a policy with base coverage of Pure Term, with additional coverages like Critical illness and accidental death benefit.

As you see from this example, the level of detail for a data mart could vary in terms of the detail you want to follow. The second option will have more granular data. With this level you will be able to analyze at the level of individual coverages within the policy.

Apart from 'what detail you want to measure', there is another aspect of fact table grain. This is 'what you want to measure', For example a sales analysis data mart could have the following alternatives for grains:

A sales lead
An interest in product by the customer
A delivered product

The above list shows the progressive certainty of making a sale. If you take sales lead as the grain, you will end up with data mart, which not only provides you the final sales analysis, but also tells you on how well you have been converting the leads. This should make a sales lead as the best choice?? May not be. In case the strike rate on sales leads is low, and sales leads data is coming from a different (and probably a less reliable) system, it may not end up being a best choice. Another reason on why sales lead may not be the best choice for fact table grain is that you may have some sales, which have not come through the sales lead (like a sale done to an existing customer).