Data Warehouse modeling has two components:
- Foundation to support medium to long-term capabilities, without the need to unsettle the structure time and again.
- The individual phases for developments of Data Marts eventually merge into the enterprise wide Data Warehouse.
A project has to address both the foundation and phase elements. Every stage in the Data Warehouse project will address these two elements in distinct and overt manner. For dimensional modeling, the following foundation setting elements will work like reusable components. They will be same across the Data-Marts/Data Warehouse for current and the future phases of developments:
Standard set of foundation or conformed dimensions. This means that:
- Dimensions are super-sets of all possible attributes for that dimension. For example, customer 'age' attribute may not be required for sales analysis, but required for Credit Analysis. Therefore, when creating the standard dimensions, one make the superset of attributes.
- Dimensions include all possible levels of business hierarchy. For example- A portfolio analysis of a channel may not require the branch level location, but the agent productivity analysis could.
- Dimensions to include not only categories, but descriptive textual attributes as well wherever needed.For example- A textual detail for a location code could be needed for distribution analysis, but many not be needed for portfolio analysis.
- Make the dimension most granular- Many a times the analysis does not need to go down to the most granular level of customer ID. In case, customer moves from his existing customer segment, the whole dimensional modeling could lead to issues, if the dimension is starting from customer group upwards
examples of foundation dimensions are- Customer, Location, Channel, Sales Lead etc. PLEASE REFER Universal Dimensions for more examples.
Standard set of foundation or conformed facts. This means that:
- A fact table will include all possible units of measures for given set of dimensions. For example sales by numbers could need only the number of 'Crates' in one data mart and 'Pieces' in the other. However, both units for the given measure should be included even if there is a standard conversion rate. These standards conversion rates keep on changing with time.
- A Fact table logically groups a business instance. For example you could require distribution of a 'product' to retail outlet for distribution analysis. However, you will require the fact on final sale to the end customer for sales analysis. As a guideline, a highly linked business process should get combined in a single fact.
Standard set of foundation measures. This means that
- All the measures and their possible units to be listed out.
- Measures are most susceptible to having confusing definitions OR to be mis-named. Detailed formulas behind measures are must. Refer Sales Revenue Fact-Measure as an example.
examples of foundation measures are- Sales Measures, Customer Measures, etc. PLEASE REFER FACTS-Base Measures for more examples.
|
