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.
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 🙂
- SAP Managed Tags:
