Skip to Content
Technical Articles
Author's profile photo Lale Yakut Valusek

Create and Consume Hierarchies in SAP Data Warehouse Cloud in 3 Steps and 3 Minutes

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%20parent%20-%20child%20hierarchy

Sample parent – child hierarchy

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

Data%20Builder

Data Builder

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

Import%20Options

Import Options

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

Semantic%20Usage

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%20Options

Hierarchy Options

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

Settings

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%20fact%20data

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%20associations

Create associations

Select the dimension table that contains the business segment:

Select%20the%20target

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%20mapping

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%20dimension%20-%20check%20out%20your%20new%20cool%20hierarchy

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%20-%20change%20the%20behavior%20for%20unbooked%20data

Optional – change the behavior for unbooked data

Your story is ready to be saved and shared.

Sample%20Story

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,

Lale

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

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Chaitanya Reddy
      Chaitanya Reddy

      Thanks for the article, but is there a way to consume BW hierarchies in DWC. Example Profit center hierarchy / Product hierarchy.

      Regards

      Chaitanya

      Author's profile photo Prasad Bandi
      Prasad Bandi

      Chaitanya, One way could be to flatten the hierarchy in BW and use the process explained in this blog.