Organizing your data with proper hierarchical structures is important for any business. When you acquire data in SAP Analytics Cloud, you can enrich your data by creating hierarchies. In this blog, let us see how to create different hierarchies while building an acquired data model.
As the name implies Level-Based Hierarchies are used when your data has different levels of data. Some examples are Division > Region > State > City > Store and Product Category > Sub-Category > Product. In Level-Based Hierarchy, the upper levels will be properties of the dimension. For example, Category and Sub-category will be properties/attributes of the product. Each level is stored as a separate column within the model.
Consider the following example where you have order details data. It would be great to have the Product dimension in a Level-Based Hierarchy.
Creating a Level-Based Hierarchy while building a data model is easy. In our example, the hierarchy should be added to the dimension Product ID. So, Product Name is added as the Description. Then a Hierarchy is created using the Hierarchy button under Tools. The product ID is selected as the base dimension. Then additional levels are added by choosing the appropriate column names. Once the Hierarchy is created, all the higher level columns are automatically converted to attributes for the base dimension.
If needed, you can also choose the option ‘Generate a 100% unique bottom level for my hierarchy’ so that the bottom level is created with a unique id combining the higher-level information. This unique ID can avoid data discrepancies.
Once the model is created, you can see the Hierarchy dimension with levels representing different attributes.
Parent-Child hierarchy can have only one attribute i.e. parent. And usually, the members within a parent-child hierarchy will be of the same type. For example, in an employee hierarchy, immediate managers will be the parent for each employee. Though you can have multiple levels, each member will have a parent or be a root member. While building a data model, if you have parent members in a separate column, you need to make it an attribute to the child dimension where you are going to create the parent-child hierarchy. Here in the example below, the manager column is added as a parent attribute to the Employee dimension.
Upon selecting the Hierarchy Management option within a model, you can use drag and drop feature to structure your parent-child relationships and even create multiple levels. Please note that all the members in the parent column are added as members to the main or child dimension with ‘<root>’ as its parent attribute member.
If you don’t have parent information in a separate column or if you have created a blank model, you will be able to add parent attribute after the creation of the model under Dimension Settings. Once the attribute is created, you can import or enter data and use Hierarchy Management to structure parent-child relations.
If you are interested in learning more about SAP Analytics Cloud, check out our series of blogs here.