Create SAP S/4HANA External GL Account Hierarchy within SAP Datasphere using standard CDS Views

[JoelleS]

1.      Introduction

This blogpost is inspired by this Blogpost (https://community.sap.com/t5/technology-blogs-by-sap/guide-create-sap-s-4hana-external-gl-account-hi...). It is described how you can create an SAP S/4HANA External GL Account Hierarchy within SAP Datasphere.

However, sometimes it might be not possible to use the community content package, therefore we want to hand you a guide which shows you how the hierarchy is being set up. If you don’t have any deviations, we still recommend using the community package, since it will save you a lot of time and effort. Please be aware that this logic can be used for differing use cases! Even with the data changed, the logic will remain the same.

First, you need to set up a connection between your S/4HANA Cloud System and your space in SAP Datasphere. There will soon follow a guide series provided by my colleague Simone and me. I will keep this blog post up to date and add this link as soon as our series started

You should know some basics of SAP Datasphere (build views in general and create an replication flow).

2.      Basic Logic and Setup

We will build the hierarchy and the final view of a balance sheet with the G/L hierarchy applied to it. Please find this logic applied to it. It is the same from the community content. The purple arrows represent the associations.

Therefore, we will need the following CDS Views which will be pulled via Replication Flow. In this step we will create new target tables. These Local Tables remain with the same name as the original CDS views. You can choose existing target tables as well if you have them, but I assume you are starting from scratch.

For the views, we will use different business and technical names. We sticked with the naming convention of the original blogpost with the community content to not create confusion.

Name CDS View and DSP Local Table	Technical Name View	Business Name View	Usage
I_GLACCOUNTHIERARCHY	SAP_CC_FI_HRF_IL_I_GLACCOUNTHIERARCHY	HRF: G/L Account Hierarchy Directory (DIM)	Local Table for GL Account Hierarchy View
I_GLACCOUNTHIERARCHYTEXT	SAP_CC_FI_HRF_IL_I_GLACCOUNTHIERARCHYTEXT	HRF: G/L Account Hierarchy Directory  - Text (IL)	Local Table for GL Account Hierarchy Text
I_GLACCOUNTHIERARCHYNODE	SAP_CC_FI_HRF_IL_I_GLACCOUNTHIERARCHYNODE	HRF: G/L Account Hierarchy Node (IL)	Local Table for GL Account Hierarchy Node
I_GLACCOUNTHIERARCHYNODET	SAP_CC_FI_HRF_IL_I_GLACCOUNTHIERARCHYNODET	HRF: G/L Account Group (Hierarchy Node) - Text (IL)	Local Table for GL Account Hierarchy Node Text
I_GLACCOUNTINCHARTOFACCOUNTS	SAP_CC_FI_HRF_IL_I_GLACCOUNTINCHARTOFACCOUNTS	HRF: G/L Account (DIM)	Local Table for GL Account Master data Attributes
I_GLACCOUNTTEXTRAWDATA	SAP_CC_FI_HRF_IL_I_GLACCOUNTTEXTRAWDATA	HRF: G/L Account – Text (IL)	Local Table for GL Account Master data Text

In addition to these, you should have a view containing your transaction data. In our example we have a custom CDS View with Journal Entries. We are aware that there are Standard CDS Views that contain the same data, but in our case, we had to add some measures.

After deploying and running your replication flow, you should now have several Local Tables. Within the Data Builder you can filter after them using the top row.

Starting from the bottom, we will first create the hierarchy. Afterwards we will build the dimension to which the hierarchy is applied to. This dimension is our master data, which will be associated with our transaction data (Fact View).  Based on the Fact View, we will create our Analytic Model to make our data available for consumption in SAP Analytics Cloud.

3.      Building the Hierarchy with Directory

3.1 View (Text) for GL Account Hierarchy Text

We will first build this Text View:

Name CDS View and DSP Local Table	Technical Name View	Business Name View	Usage
I_GLACCOUNTHIERARCHYTEXT	SAP_CC_FI_HRF_IL_I_GLACCOUNTHIERARCHYTEXT	HRF: G/L Account Hierarchy Directory  - Text (IL)	GL Account Hierarchy Text

Create the view and adjust the following Colums:

• ValidityEndDate with the expression TO_DATE(ValidityEndDate)
• ValidityStartDate with the expression TO_DATE(ValidityStartDate)

In the newly created View (Text) change the semantic type from “relational dataset” to “Text”.

Work on the Semantic Types for the Attributes accordingly.

There is no need to set a representative key in this step.

3.2 View (Dimension) for GL Account Hierarchy View

Next up is the related Dimension:

Name CDS View and DSP Local Table	Technical Name View	Business Name View	Usage
I_GLACCOUNTHIERARCHY	SAP_CC_FI_HRF_IL_I_GLACCOUNTHIERARCHY	HRF: G/L Account Hierarchy Directory (DIM)	GL Account Hierarchy View

Create the view and adjust the following Colums:

• ValidityEndDate with the expression TO_DATE(ValidityEndDate)
• ValidityStartDate with the expression TO_DATE(ValidityStartDate)

In the newly created View (Dimension) change the semantic type from “relational dataset” to “Dimension”.

Create an Association between HRF: G/L Account Hierarchy Directory  - Text (IL) (SAP_CC_FI_HRF_IL_I_GLACCOUNTHIERARCHYTEXT) and the newly created Dimension.

Work on the Semantic Types for the Attributes accordingly. Please ignore the error warning. 

There is no need to set a representative key in this step.

3.3   View (Text) for GL Account Hierarchy Node Text

We will first build this Text View:

Name CDS View and DSP Local Table	Technical Name View	Business Name View	Usage
I_GLACCOUNTHIERARCHYNODET	SAP_CC_FI_HRF_IL_I_GLACCOUNTHIERARCHYNODET	HRF: G/L Account Group (Hierarchy Node) - Text (IL)	GL Account Hierarchy Node Text

 Create the view and adjust the following Colums:

• ValidityEndDate with the expression TO_DATE(ValidityEndDate)
• ValidityStartDate with the expression TO_DATE(ValidityStartDate)

In the newly created View (Text) change the semantic type from “relational dataset” to “Text”.

Work on the Semantic Types for the Attributes accordingly.

Set “Hierarchy Node” (HierarchyNode) representative key.

3.4   View (Relational Dataset) GL Account Hierarchy Node

We will now create the related Dimension:

Name CDS View and DSP Local Table	Technical Name View	Business Name View	Usage
I_GLACCOUNTHIERARCHYNODE	SAP_CC_FI_HRF_IL_I_GLACCOUNTHIERARCHYNODE	HRF: G/L Account Hierarchy Node (IL)	GL Account Hierarchy Node

Create the view and adjust the following Colums:

• ValidityEndDate with the expression TO_DATE(ValidityEndDate)
• ValidityStartDate with the expression TO_DATE(ValidityStartDate)

Create two more calculated Columns:

• Node ID (NODEID) with the expression:

CASE NodeType

WHEN 'L' then

CONCAT(ChartOfAccounts,CONCAT('/',GLAccount))

else HierarchyNode

end

• Hierarchy Name (HIENAME) with the expression

GLAccountHierarchy

In the newly created View (relational Dataset) leave everything else as created by default.

3.5 View (Dimension) for GL Account Hierarchy Node

Next up is the Hierarchy with Directory View:

Relational Dataset	Technical Name View	Business Name View	Usage
HRF: G/L Account Hierarchy Node (IL) (SAP_CC_FI_HRF_IL_I_GLACCOUNTHIERARCHYNODE)	SAP_CC_FI_HRF_GLACCOUNTHIERARCHYNODE	HRF: G/L Account Hierarchy Node (IL)	GL Account Hierarchy Node

 

Create this view and project only the following four columns

Associate the Text View HRF: G/L Account Group (Hierarchy Node) -Text (IL) (SAP_CC_FI_HRF_IL_I_GLACCOUNTHIERARCHYNODET) with the newly created view.

Adjust the semantics for the attributes

Choose "HierarchyNode" (HierarchyNode) as representative key.

 

3.6 View (Hierarchy with Directory) for GL Account Hierarchy View

Next up is the Hierarchy with Directory View:

Relational Dataset	Technical Name View	Business Name View	Usage
HRF: G/L Account Hierarchy Node (IL) (SAP_CC_FI_HRF_IL_I_GLACCOUNTHIERARCHYNODE)	SAP_CC_FI_HRF_GLAccountHierarchyView	HRF: G/L Account Hierarchy (Hierarchy with Directory)	GL Account Hierarchy

Like before, create the view based on the relational dataset HRF: G/L Account Hierarchy Node (IL) (SAP_CC_FI_HRF_IL_I_GLACCOUNTHIERARCHYNODE).

In the newly created View (Hierarchy with Directory) change the semantic type from “relational dataset” to “Hierarchy with Directory”.

Create an Association between HRF: G/L Account Hierarchy Directory (DIM)(SAP_CC_FI_HRF_IL_I_GLACCOUNTHIERARCHY) and the newly created Hierarchy with Directory.

Create a second association between HRF: G/L Account Hierarchy Node (IL) (SAP_CC_FI_HRF_IL_I_GLACCOUNTHIERARCHYNODE ) and the newly created Hierarchy with Directory.

Work on the Semantic Types for the Attributes accordingly.

Change the keys to ValidityEndDate (ValidityEndDate), Node ID (NODEID) and Hierarchy Name (HIENAME). There is no need to set a representative key in this step.

Open the Hierarchy with Directory Settings and fill in the following information.

4.      Apply the Hierarchy

You are almost there 🙂 

4.1 View (Text) for GL Account Master Data Text

Name CDS View and DSP Local Table	Technical Name View	Business Name View	Usage
I_GLACCOUNTTEXTRAWDATA	SAP_CC_FI_HRF_IL_I_GLACCOUNTTEXTRAWDATA	HRF: G/L Account – Text (IL)	GL Account Master data Text

 

In the newly created View (Text) change the semantic type from “relational dataset” to “Text”.

Work on the Semantic Types for the Attributes accordingly.

Set GLAccount (GLAccount) as representative key.

4.2 View (Dimension) for GL Account Master Data Attributes

Next up is the related Dimension:

Name CDS View and DSP Local Table	Technical Name View	Business Name View	Usage
I_GLACCOUNTINCHARTOFACCOUNTS	SAP_CC_FI_HRF_IL_I_GLACCOUNTINCHARTOFACCOUNTS	HRF: G/L Account (DIM)	GL Account Master data Attributes

In the newly created View (Dimension) change the semantic type from “relational dataset” to “Dimension”.

Create an Association between HRF: G/L Account  - Text (IL) (SAP_CC_FI_HRF_IL_I_GLACCOUNTTEXTRAWDATA)  and the newly created Dimension.

Create a second association between HRF: G/L Account Hierarchy (Hierarchy with Directory) (SAP_CC_FI_HRF_GLAccountHierarchyView)

There is no need to work on the semantic types of the attributes.

Set “GLAccount” (GLAccount) as representative key.

5.      Associate the Dimension

In our case we can now associate the Dimension with our Journal Entries, that are deployed as Fact View. We define the measures and create an Analytic Model. In the Data Preview we choose GL Account and can now apply several Hierarchies.

We hope that this guide helps. Feel free to reach out to us 🙂