Q:

Is there any BI tool that can handle a recursive hierarchy?

A:

Joyce Bischoff’s Answer: A well-designed data warehouse should avoid the use of recursive hierarchies. The objective of a data warehouse is to provide data structures that can be easily understood by the end users and recursive structures do not meet the objective. Les Barbusinski’s Answer: The short answer is “No.” A ROLAP tool would be limited by the SQL it can generate on the target RDBMS, and I know of no RDBMS that supports n- level recursion in a singleton SELECT. A MOLAP tool would need to define a “cube” for every level in a drill-down hierarchy (i.e., every “cell” in a higher-level “cube” drills down to a “plane” in a lower- level “cube”) and without knowing how many levels there are in the hierarchy, it would be unable to set up the necessary aggregate “cubes.” In every case of recursive hierarchy I’ve ever seen, the hierarchy had to be “flattened out” to the maximum number of levels that were likely to be encountered, with “dummy” values inserted wherever a level was missing.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access