It’s just before the summer break, and I want to share an idea on how to get a dynamic prior year in an analytical model.

There are several questions in the SAP community how to get such a result as it was used in SAP BW. Like this one https://community.sap.com/t5/finance-q-a/offsetting-input-parameters-in-datasphere-s-analytic-models-restricted/qaq-p/13723947

In this post, I want to share an idea on how to get such an offset in SAP Datasphere. Let’s go to our fact view and add some logic that we will use later in our analytic model.

First we will add an input parameter to the bicycle data model, later I will describe another way if you don’t want to use an input parameter. 

The input parameter in this case is called IP_YEAR, has no input help, and has a string data type with a length of 4.

Now let’s use the input parameter to complete the logic. Add a calculated column to the view. If you are building the view in pure SQL, add the logic accordingly.

The new column provides the following information that we will use later. The current year is period 0, the previous year is period 1, and the year before that is period 2. I use the delivery
date as the reference data.

The CASE statement looks like this

CASE WHEN DELIVERYDATE BETWEEN TO_DATE(ADD_YEARS(:IP_YEAR||'0101',-1)) AND 
        TO_DATE(ADD_YEARS(:IP_YEAR||'1231',-1)) THEN 'P1'
        WHEN DELIVERYDATE BETWEEN       TO_DATE(ADD_YEARS(:IP_YEAR||'0101',-2)) AND 
        TO_DATE(ADD_YEARS(:IP_YEAR||'1231',-2)) THEN 'P2'
        WHEN DELIVERYDATE BETWEEN       TO_DATE(ADD_YEARS(:IP_YEAR||'0101',0)) AND 
        TO_DATE(ADD_YEARS(:IP_YEAR||'1231',0)) THEN 'P0'
END 

The 2020 data preview now looks like this:

Now we have all the requirements for the analytical model to create a dynamic selection. So we open the analytic model and see what it looks like

The analytic model shows the year deviation perfectly. Now the next step is to create separate key figures to get a better analysis. Go back to the analytical model and create 3 restricted
measure. One for period 0, one for period 1 and the last one for period 2.

After creating all the restricted measures, we see this in the analytic model

Now we can add the product ID to the rows and have a perfect overview of the sales quantity.

As I wrote earlier, this is based on a variable that you have to set. If you don’t want to use a variable and just want to select the actual year with no flexibility, you could use the SQL
function current_date instead of the input parameter in the view.

This is one idea, and maybe you have a better one you want to share. So I would love to read from you in the comments. I think it is a pretty simple solution to solve the previous year problem in
an analytic model and can be used very easily. 

In this post I want to describe how you can use hierarchies in SAP Datasphere just like in good old SAP BW. 

To be able to use a hierarchy in SAP Datasphere like in SAP Business Warehouse, you need an object with the semantic type „Hierarchy with Directory“. 

There are several blog posts on this topic that explain it in detail:

• https://blogs.sap.com/2024/01/08/creating-a-hierarchy-with-directory-in-sap-datasphere/
• https://blogs.sap.com/2024/01/15/walkthrough-of-different-enterprise-scenarios-via-community-content-package-gl-account-external-hierarchy-with-replication-flow./
• https://blogs.sap.com/2024/01/15/an-introduction-to-hierarchy-with-directory-in-sap-datasphere/
• https://blogs.sap.com/2024/01/15/modeling-a-basic-hierarchy-with-directory-in-sap-datasphere/
• https://blogs.sap.com/2024/01/15/modeling-an-advanced-hierarchy-with-directory-in-sap-datasphere/
• https://blogs.sap.com/2024/01/15/guidecreate-sap-s-4hana-external-gl-account-hierarchy-within-sap-datasphere-through-community-content-packages/

The entity relationship model for hierarchies with directories looks like this:

For the hierarchy with directories, we need different tables directly from SAP BW.

• RSHIEDIRT – for the hierarchy directory texts
• RSHIEDIR – for the hierarchy master data
• RSTHIERNODE – for the texts of the text nodes
• /BIC/HZCDWC001 – for the hierarchy table of the Product Group InfoObject
• /BIC/MZCDWC001 – for the product group master data
• ZDWC007 – for the transaction data of the store

The transaction data is contained in the Facts table for the store. The transaction data has an association to the Product Category dimension. The Product Group dimension has a hierarchy with
directory assignment. 

However, the main logic takes place in the view with the semantic type Hierarchy with Directory. In the Entity-Relationship Model, it is recognizable as „Product Group with Hierarchy Directory“.

The directory has a text association with the hierarchy texts (table RSTHIERNODE), a dimension association with the hierarchy master data (table RSHIEDIR), which
in turn has a text association with the hierarchy directory texts table (table RSHIEDIRT). So much for the structure of the Entity-Relationship model. 

We start with the table Hierarchy Directory Tables (RSHIEDIRT). This is a remote table in the SAP BW system with the semantic usage Text.

In the attributes of the table, the language key, the text, and the column name are defined.

Next, we look at the Hierarchy Master Data table (RSHIEDIR). This table lists all the hierarchies that exist in the SAP BW system.

To get only the data we need, we filter the hierarchy IDs we need. Using projection, we remove the unneeded columns and then calculate the date from to obtain a correct validity. The semantic use
of views is dimensioning.

The RSTHIERNODE table is used to obtain the texts associated with the hierarchy nodes. Filter the hierarchy IDs you require and hide the columns you do not need. 

As semantic usage text and the columns with the necessary semantic types (language, text, description) are defined.

Now the hierarchy is created with directories. The basis is the hierarchy table of the Product Group dimension (/BIC/HZCDWC001).

First, various unneeded columns are hidden (e.g., object version, level of a hierarchy node). Then, the Hierarchy Node Name (NODENAME) column must be split into two columns to
get a correct assignment for the hierarchy. One for the folder (0HIER_NODE) and one for the original InfoObject (ZCDWC001). So we have two new columns in this
view: Leaf Node (ZCDWC001) and Node ID (0HIER_NODE).

This view now forms the basis for the hierarchy with directories.

The calculation of the new columns had to be moved to a separate view, otherwise it would lead to an error. As a semantic usage for the following view, we use Hierarchy with Directory and then
define our settings. If necessary, the dimension assignment to the hierarchy’s master data must be done beforehand.

In the details panel, we now make the settings for the hierarchy with directory.

We define the Parent Column and the Child Column. We also need to select the column containing the name or ID of the hierarchy. The association with the Hierarchy Directory Entity (Hierarchy
Master Data) should be done automatically by the previous association.

Next, we define the Nodetype column. The last step is the definition of the node value. Once for the folder (0HIER_NODE) and the corresponding column (NODE_ID)
and then also for the individual leaves of the hierarchy. Here is the InfoObject ZCDWC001 with the Leaf Node column. 

By clicking OK, the hierarchy can be implemented and published. Afterward, the association with the dimension Product Group can be performed. In the analytical model, the hierarchy can now be
selected for the dimension

This is one way to model hierarchies in SAP Datasphere. In this post, I get all the information directly from the old BW and reuse it to build a data model in Datasphere. If you have any thoughts
or ideas, please post them in the comments.

You may also be interested in these posts:

• Consume hierarchies with nodes in SAP Datasphere
• Create a parent-child hierarchy in SAP Datasphere
• Datasphere: Restrict Data Access in Hierarchies
• Create a time hierarchy in SAP Datasphere

Name change from SAP Data Warehouse Cloud to SAP Datasphere. Some links may break.

Wow! It’s now a long time since I wrote the last blog post. I think many of my posts starts with this now. But anyway, let’s get back to the subject. I have the opportunity to have a look at the
new Datasphere (DSP) Analytic Model. It was presented in the keynote of Hagen Jander and Eric Schemer on the DSAG Jahreskongress.

Since last week I have a look at the Analytic Model and must say it is really cool. At the moment, I build a test with some demo data. So Stay tuned and we see us next year.

Happy x-mas and a happy new year.