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).
Overall here are the considerations for deciding the fact table grain for dimensional modeling:
- the level of granularity you want to have the data.
- the quality of the data available to you.
- the kind of analysis you want to do.
TIP- Keep your data as granular as possible
PLEASE REFER Execution-MiHPractice Tool Data Mart + Fact Table Grain Matrix
|
