After the Data Marts + Fact Table Grain matrix has been established, it is time to proceed onto the next level of details for Dimensional Modeling. From a business perspective, a Data (schema) Mart consists of dimensions and facts. This listing is helped by the fact table grain. The listing will include:
- The list of Data Marts on one axis, and the Dimensions followed by measures on the other axis.
- List out all possible dimensions and measures, as you have gathered in your functional specifications sessions.
- The application dimensions and measures for a line of Data Mart, will be ticked in the appropriate box of intersection.
You may like to open Execution-MiHPractice Tool Data Mart + Dimensions +facts matrix, on the side to see how this matrix looks like.
As you will see in the subsequent sections- data warehouse is mostly designed with the Foundation Dimensions and Facts, so that a dimension is like a reusable object for any data-mart. One may question that 'why can't we list the universal dimensions first?', instead of being driven by which Data-Mart would require which dimension.
The reason is that by first mapping dimensions with Data-Marts one is able to understand the depth & scope of a dimension, OR the attribute, which will go along with it. After this exercise, you may keep a dimension having more attributes OR less attribute OR you may end up splitting a dimension OR combining the dimensions.
In other words, this step is building dimensions and facts keeping the business theme in mind. While you are identifying the dimensions and facts, you have to keep on asking the question 'does it meet the information needs for the business theme for medium term?' (~3 years). Apart from listing the dimensions and facts, one should also provide the free-flow description of the same in the matrix. In nutshell, this exercise gives you all the inputs you need to ultimately decide on what foundation/superset dimensions you have to create. The same principle applies on Facts as well.
Another care one has to take is to make the names of dimensions and facts pretty self-explanatory. When you go to the next step of listing dimensions and facts (irrespective of which data-mart they belong to.), you will be doing a fair degree of splitting, combining the dimensions etc, and the naming should help you in managing the same. (For example-you may like to name the dimensions like 'billed customer' and 'newly acquired customer' instead of just 'Customer')
TIP- The generation of this matrix is iterative. The purpose of making this matrix, before you create the details on dimensions listing and facts listing, is to understand, what all dimensions and facts could exist to support the business themes and data marts. Once you are done, you can proceed to detail out the dimensions and facts. As you work on this next step, you may realize that a single dimension needs to be split into multiple dimensions. With this realization, you can go back to your Data Mart+Dimensions+Facts matrix.
PLEASE REFER Execution-MiHPractice Tool Data Mart + Dimensions +facts matrix |
