Today I will walk you through how you can easily create a custom hierarchy in SAP Data Warehouse Cloud and use it in your reports in 3 steps and 3 minutes.

Custom hierarchies are useful when we need to quickly simulate changes, and help with business decisions for a variety of reasons. The requirements can vary depending on the use cases; for example, they can be built as level-based or based on a parent-child relationship. They can be balanced or unbalanced. In some use cases – such as planning or financial adjustments – you have to have data at the nodes as well as the leaves, but sometimes you should only have data at the leaves and roll up. Sometimes, you want to show the entire hierarchy even if there are no postings for the leaves or nodes, and sometimes you are not supposed to do that. Here is how you build this in 3 simple steps (or less), then use that in your story in Step 4.

Introduction

The custom hierarchy structure can be built in SAP Master Data Governance, another system, or in a spreadsheet, by a business user, externally. These can can be enabled in SAP Data Warehouse Cloud either by using connections or by downloading to a flat file and uploading to SAP Data Warehouse Cloud.  We are demonstrating the file download/upload method in this blog.

Step 1: Create a Dimension with Hierarchy

Create a dimension with a hierarchy. In this demo, I have a business segment hierarchy with unbalanced structure where some segments have no data. For this demo, I have a flat file where I have business segment as children, and segment group as parent. Some branches are two levels deep, some three.

Sample parent – child hierarchy

To build this hierarchy in SAP Data Warehouse Cloud, navigate to the “Data Builder” on the left-hand side.

Data Builder

Upload the flat file that has the parent child relationship, like the one above, using the option “Import CSV File”.

Import Options

Select file, import, type the business name and deploy. Click on the table, select the semantic usage as “Dimension”.

Semantic Usage

Click on hierarchy button on the toolbar, to define a hierarchy of desired type.

In this demo we are selecting a “Parent Child Hierarchy”. If you have a hierarchy where the structure is specified in separate columns, you can use the “Level Based Hierarchy” option.

Hierarchy Options

Provide the name of the hierarchy, the parent column, the child column and deploy.

Settings

Step 2: Identify or Create Your Fact Data

Build or get the fact data you need to report. If you already have a federated or replicated fact data set that you know, you can skip this step. In this example, I have a revenue data for business segments in a flat file that I will upload to SAP Data Warehouse Cloud. There are no rows for revenues for 3 of the segments (Connecticut, Nevada, and Florida) and I would like to make sure that I can report with or without these in my report.

Sample fact data

To create a fact table based on a flat file, like the previous step, upload your file, validate, provide a business name and deploy. Make sure that the data type of the dimension that you built the hierarchy is the same for the fact data.

Step 3: Build an Analytical Dataset with Association

Build a view, where you associate the fact table to the hierarchy dimension. This is where we introduce the dimension (Business Segment) with the hierarchy (My Segments) to the fact table that we have (DWC Revenue) for consumption.

In data builder, select “New Graphical View” option.

Drag and drop your fact table to the canvas.

Click on the “View 1” box, enter a business description, select “Analytical Dataset” for analytical usage, verify that “Expose for Consumption” is “ON”, and specify your measures, as in below.

Scroll down to Associations section, to link the Business Segment dimension with hierarchy with revenues. To do so, Click on the plus sign to create association.

Create associations

Select the dimension table that contains the business segment:

Select the target

The mapping should automatically be done if the data type and field description is the same. Validate the mapping is done for the column.

Association mapping

Deploy and enjoy!

Step 4 (Optional): Build a Story

In this step, we will create a simple report that shows how the custom hierarchy works in SAP Analytics Cloud stories. To build a story, navigate to the story builder.

Select your data, which is the analytical dataset that we created in Step 3.

Insert a chart, and select the dimension with hierarchy. Notice the hierarchy icon enabled for this field.

Select dimension – check out your new cool hierarchy

You can also create a table with this dataset. By default, the story will hide the leaves with no data on them. If you would like to change this behavior in a table for example, navigate to the context menu of the dimension and click on “Unbooked Data”.

Optional – change the behavior for unbooked data

Your story is ready to be saved and shared.

Sample Story

To summarize, by using the hierarchy feature of dimension, you can create custom level-based or parent child hierarchies directly in SAP Data Warehouse Cloud, use it in your own space, or share it with other spaces and business users for better collaboration. By using the association feature in graphical views, you can then use these hierarchies seamlessly in your analytics.

Share your thoughts and feedback in comments section below for your use cases. You can get involved and ask questions in SAP Data Warehouse Cloud community here. You can try it free yourself and check out SAP Data Warehouse Cloud learning track.

Have fun,

Image credits: All images in this blog are created as screenshots while creating this use case by the author.