Optimizing Access across Multiple Hierarchies in DataWarehouses

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.

By: Lipyeow Lim; Bishwaranjan Bhattacharjee

Published in: RC25148 in 2011


This Research Report is available. This report has been submitted for publication outside of IBM and will probably be copyrighted if accepted for publication. It has been issued as a Research Report for early dissemination of its contents. In view of the transfer of copyright to the outside publisher, its distribution outside of IBM prior to publication should be limited to peer communications and specific requests. After outside publication, requests should be filled only by reprints or legally obtained copies of the article (e.g., payment of royalties). I have read and understand this notice and am a member of the scientific community outside or inside of IBM seeking a single copy only.


Questions about this service can be mailed to reports@us.ibm.com .