Trees in SQL: Nested Sets and Materialized Path. Ragged Hierarchy Navigation. Ragged Hierarchies are a common BI reporting structure in which the hierarchy contains a variable number of levels.
Examples are an organization hierarchy, or a financial chart of accounts. Ragged hierarchies are cumbersome to navigate using standard SQL. But there are ways to simplify the navigation without compromising performance. It is useful to employ a “tree” metaphor to visualize the modeling challenges of dimensional hierarchies. A “tree” is a collection of reporting hierarchies such as may exist in a product dimension or an organization dimension. OBIEE Data Modeling Tip.
In this third “design tip”, I’m going to look at creating ragged hierarchies in Oracle BI Suite Enterprise Edition.
Ragged Hierarchies are hierarchies where some routes down the hierarchy are longer than others; for example, you may have an organizational hierarchy for salespeople where the overall boss has two assistant managers, one of them works on their own and another has three sales managers under them; some of those sales manager may have sales staff under them, and some sales staff may have trainees under them, like this diagram from this Joe Celko article. In OLAP tools such as Discoverer for OLAP these sorts of hierarchies are simple to display, but in relational query tools such as Oracle BI Answers they’re more tricky, as the underlying logical model expects dimension hierarchies to have a fixed depth and leaf levels to be common for each branch.
So how do you handle ragged hierarchies with Oracle BI Suite Enterprise Edition? With the following table DDL: