How can you handle complex hierarchies in an OLAP data model?

2 How to store complex hierarchies?

A bridge table is a common way to store complex hierarchies in an OLAP data model. This table connects two or more tables that have a many-to-many relationship, such as a dimension table and a fact table, or a dimension table and another dimension table. The bridge table stores information about the complex hierarchy, including the level, the parent, the child, and the weight of each member. For example, the columns in a bridge table for a parent-child hierarchy may include MemberID, ParentID, Level, and Weight. Utilizing this type of table can help store complex hierarchies in a flexible and scalable way without having to create multiple columns or rows for each level or branch. However, it can also introduce drawbacks such as increased complexity and size of the data model, reduced performance and speed of queries, and potential data quality and consistency issues.

3 How to query complex hierarchies?

Querying complex hierarchies using OLAP tools and languages, such as MDX or DAX, can be a challenge since these tools are designed to work with multidimensional data models that have simple and uniform hierarchies. To access and manipulate the data in these more complex hierarchies, you need to use special functions and syntax. For instance, you can use the Descendants function to return a set of members that are descendants of a specified member at a specified level or distance in the hierarchy, the Ancestor function to return the ancestor of a specified member at a certain level or distance in the hierarchy, and the Path function to return the string representing the path of a member in the hierarchy. Additionally, you can use the Hierarchy syntax to specify which hierarchy you want to use for a dimension, using dot notation (e.g. [Product].[Brand-Model-Product]). Although these functions and syntax can help you query complex hierarchies in an OLAP data model and perform operations such as filtering, grouping, aggregating, and calculating the data, they require advanced skills and knowledge as you need to understand the structure and logic of the hierarchies and write precise expressions.

4 How to display complex hierarchies?

A third challenge of complex hierarchies is how to display them in a user-friendly and intuitive way with OLAP tools and applications, such as Excel, Power BI, or Tableau. It's important to employ special techniques and options to display them meaningfully and clearly, including hiding or showing levels, formatting or customizing labels, and using or creating hierarchies. These techniques and options can help you to display complex hierarchies in an OLAP data model while enhancing readability and usability. However, they may require creativity and experimentation to find the best way to present the data while avoiding confusion and ambiguity.

5 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?