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

182 Chapter 6 Who and What Figure 6-15 Hierarchy report, indented to show subsidiary level Consequences To populate the sequence number column correctly you have to build the hierar- chy map in hierarchy sequence order. This precludes the use of SQL techniques that populate the table “a whole level at a time”. It also means that maintenance is more complicated—if nodes are moved their sequence numbers and the sequence numbers of many others around them need to be updated. Because this involves complex coding it is often easier (time permitting) to rebuild (truncate and reload) the hierarchy map than update it. Drill down analysis on a hierarchy map can be implemented using recursive hyperlinks within browser-based reporting interfaces Queries that do not constrain to a single parent have to be careful not to over- count descendent facts Drilling Down on Hierarchy Maps The default drilling features of most BI tools have difficulty working with hierarchy maps, because they expect a fixed number of levels to drill to. However, drilldown can still be achieved by using report hyperlink features available in browser-based BI interfaces. You could create a report similar to Figure 6-15 that only shows the immediate level 2 subsidiaries for a selected owner with the subsidiary names formatted as hyperlinks. When BI users click on a link the same report is called again, passing the selected subsidiary as a parameter to the report. The newly invoked report will then show the next level down in the hierarchy—the subsidi- ary’s level 2 subsidiaries. Even though hierarchy maps remove recursion from the data model to keep queries simple, you can still take advantage of procedural recursion to implement efficient variable-depth drilling by recursively calling the same report. Querying Multiple Parents Care must be taken when queries do not constrain to a single parent company. A revenue query that simply groups by Parent Customer and returns Pomegranate £30M and PicCzar £5M must not sum these to a grand total of £35M. As you can see from the report in Figure 6-16 the Pomegranate total of £30M already includes the £5M PicCzar revenue because it’s a subsidiary. Perhaps what the consulting firm would prefer is a report that showed Pomegranate £30M, EyeBeeM £15M,

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