My first Publication Agile-Data-Warehouse-Design-eBook

Modeling Business Dimensions 77 Multi-Parent Hierarchies The time hierarchy in Figure 3-6 is single parent hierarchy because each child level value rolls up to just one parent level value. In contrast, Figure 3-9 shows a multi- parent product hierarchy where a product (iPipPhone) belongs to more than one Product Type (it is part telephone, part media player). In a multi-parent hierarchy each child level can roll up to multiple parents. If a multi-parent product hierarchy is used to roll up sales to the Product Type level, something must be done to account for products that fall into multiple types. Their sales will need to be care- fully allocated; otherwise revenue for products with two parents will be double- counted at the Product Type or Subcategory level. Multi-valued hierarchies contain members with two or more parents at the same level Figure 3-9 Multi-valued Product hierarchy Multi-parent hierarchies can also be ragged or variable depth. The latter are typi- cally represented in source systems by M:M recursive relationships. Multi-parent hierarchies and variable depth hierarchies cannot be modeled directly in dimen- sion tables. Chapter 6 covers additional structures (hierarchy maps) for coping with these complex hierarchies and handling fact allocation at query time across multiple parents. For the remainder of this chapter, assume hierarchies to be single parent hierarchies that are modeled within dimension tables. Multi-parent, variable-depth hierarchies represent M:M recursive relationships A dimension can contain multiple hierarchies of different types. You should model at least one balanced hierarchy for each dimension to help discover additional attributes and common levels for comparisons across processes, and to enable default BI drill-down facilities. Hierarchy Charts Hierarchy charts are simple, quick to draw diagrams used to model single or multiple hierarchies. On a hierarchy chart a dimensional hierarchy is represented by a vertical bar with the dimension name at the bottom and the highest-level attribute of the hierarchy at the top. The levels are represented as marks on the bar, in ascending order. Figure 3-10 shows three example hierarchy charts for Time and Product. Hierarchy charts are based on Multidimensional Domain Structures (MDS) de- scribed in Microsoft OLAP Solutions, Erik Thomsen et al., Wiley, 1999. Hierarchy charts are a quick way to visualize hierarchies

My first Publication Agile-Data-Warehouse-Design-eBook | Page 98