Hierarchies : The Relational Taboo !

  • Michael J. Kamfonas
  • Published 2014


All data warehouse and data mart designers eventually come across the dimension they wish they could represent as a recursive hierarchy. It may be that it has too many levels or uneven branches, or it may be dynamic with complex segmentation rules not known at design time. The dilemma the designer faces is to force-fit the dimension into some fixed depth hierarchy, or to live with the performance implications of recursive SQL, such as the recursive union of DB2/UDB or Oracle’s “CONNECT BY”. Fortunately, there is another alternative. It exploits the concept of topological ordering and shifts most of the costly work to be done once in a preparatory step very similar to indexing. It is based on node enumeration applied at the time of data maintenance that enables transitive closure to be evaluated in a single SQL query. This technique particularly favors dimensions that are hierarchical, relatively stable in time, and are periodically maintained.

1 Figure or Table

Cite this paper

@inproceedings{Kamfonas2014HierarchiesT, title={Hierarchies : The Relational Taboo !}, author={Michael J. Kamfonas}, year={2014} }