Optimizing Access across Multiple Hierarchies in Data Warehouses

In enterprise data warehouses, different users in different business units often define their own application specific dimension hierarchies tailor made to their reporting and business performance monitoring needs. Due to resource constraints, only on a small number of these hierarchies are precomputed for performance optimization. Consequently aggregations over hierarchies without precomputations are often less responsive. We report on a performance problem in a very large banking enterprise where the large number of application specific hierarchies became a performance bottleneck. This paper proposes a novel solution for optimizing the performance of data warehouses with a large number of application specific hierarchies. We exploit the observation that dimension hierarchies in real data warehouses often contain significant overlaps. Our method detects common sub-structures among hierarchies and provides a rewriting algorithm to exploit any precomputations on these shared sub-structures. Our solution is applicable to data warehouses of large enterprises with a large number of business units and hence a large number of application specific hierarchies.