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

176 Chapter 6 Who and What Figure 6-9 Variable-depth hierarchy Hierarchy Map Pattern Problem/Requirement Recursive relationships are difficult to query for BI purposes Profile the data to see if it represents a simple balanced hierarchy Check that variable- depth is necessary and the hierarchy cannot be simplified, before implementing a complex solution A consulting firm, that has billed each one of the customers in Figure 6-9 sepa- rately, wants to use company ownership information to aggregate all billing facts by parent companies. The data is available in the billing system, stored as a recur- sive relationship, referred to as a “pig's ear” or “head scratcher” by its designers— because of its shape (see Figure 6-7). Unfortunately, both terms are also very accurate descriptions of how difficult it is to query recursive relationships. The problem is, recursive relationships cannot be navigated using the standard non- procedural SQL generated by most BI tools. Although some databases have recur- sive extensions to SQL (for example, Oracle’s CONNECT BY), they are not sup- ported by BI tools, and seldom perform adequately against data warehousing volumes of fact data. Because of the challenges involved in making recursive relationships report- friendly, the first thing to do, when you spot one, is to profile the data to see whether it actually represents a variable-depth hierarchy or not. If the data itself represents a balanced hierarchy with a fixed number of levels, or a hierarchy that is only “slightly” ragged, then the design can be kept simple by flattening (denormal- izing) the data into a fixed number of well-named hierarchical attributes within a standard dimension. If data profiling confirms that there is a variable-depth hierarchy, it is worth double-checking that the variable-depth is truly required for analysis purposes. If it is and it cannot be simplified then the following hierarchy map techniques will help, but they should also motivate you to, whenever possible, balance and fix the depth of all hierarchies that are under your control! For customer ownership analysis, there is no opportunity to simplify the hierarchies involved. You cannot tell a customer like Pomegranate that its ownership hierarchy is more complex than your other clients and ask it to sort itself out. This hierarchy is external— beyond your control—and must be represented as is.

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