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

76 Chapter 3 Ragged Hierarchies Ragged hierarchies have missing levels (with zero members) that unbalance them Ragged (or unbalanced) hierarchies are similar to balanced hierarchies in that they have a known maximum number of levels and each level has a unique name, but not all levels are present (have values) for every path up or down the hierarchy — making some paths appear shorter than others. Figure 3-8 illustrates a ragged product hierarchy, where a product (POMServer) does not belong to a subcate- gory. This product is effectively a subcategory all of its own. Figure 3-8 Ragged product hierarchy Try to balance slightly ragged hierarchies by removing levels or filling in missing values You can model ragged hierarchies in a dimension by using non-mandatory attrib- utes for the missing levels, but these gaps (missing values) cause problems for BI drilling. If a hierarchy is only slightly ragged you can often redesign it with the stakeholders help as a balanced hierarchy, to improve reporting functionality. This can involve removing levels that are not consistently implemented for all members or creating new level values to fill in the gaps (e.g. a subcategory value of “Server” for the Figure 3-8 example). See Chapter 6 for more details on balancing ragged hierarchies. Variable Depth Hierarchies Variable depth hierarchies often represent recursive relationships Variable depth hierarchies have a variable (unknown) number of unnamed levels. The variable levels do not have unique names because they are typically all of the same type; for example, in human resource hierarchies that document the relation- ships between staff and managers, each level is an employee. Another example is the bill of material for a product comprised of components and subassemblies that are themselves decomposed into other components and subassemblies. Variable depth hierarchies are also know as recursive hierarchies because they are typically represented in source data by recursive relationships: tables that join to themselves. Recursive relationships are used in operational database design for succinctly recording variable depth hierarchies but are very difficult to work with in a data warehouse. They are impractical for measuring business processes because they are impenetrable to stakeholders and BI tools alike. They must be “unfurled” for efficient reporting purposes using the hierarchy map technique described in Chapter 6.

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