Skip to Content
Product Information
Author's profile photo Sefan Linders

Build an SAP Datasphere parent-child hierarchy based on S/4HANA hierarchies

Introduction

SAP Datasphere provides modelling capabilities for hierarchies, for consumption in SAP Analytics Cloud. However, at time of writing, the hierarchy functionality in SAP Datasphere does not match the richness of hierarchies in typical SAP source systems such as SAP S/4HANA or SAP ECC. There’s no features yet like hierarchy versions, node validity intervals, orphan node handling, text language support or allowing different data types as leaf nodes. You might be looking for a way to still use those source hierarchies, and the good news is: it’s possible!

Please note this blog needs an update since the last hierarchy update with wave 2023.21, when “hierarchy with directories” were introduced. This blog does not cover that update yet. Please check out the help page on it.

In this blog post I’ll explain how you can transform a typical S/4HANA parent-child hierarchy to fit the current SAP Datasphere capabilities. As an example, a GL Account Hierarchy is used, which is sourced from SAP S/4 HANA Cloud Essentials. In below figure you can see the end result in SAP Analytics Cloud.

Figure 1: End result

Source CDS views

A connection to the source system is made using the Cloud Data Integration (CDI) connection type. Four CDS Views are used, of which one covers the GlAccount line items for the transaction data to be able to display some sample data, and the other three are input for the hierarchy model, namely the parent-child hierarchy itself, hierarchy texts to derive the functional node names, and the GL Account texts to derive the functional GL Account names. Below a list of the definition of these CDS Views.

Raw Data of General Ledger Account Line Item
General Ledger Account Hierarchy Node
General Ledger Account Hierarchy Node – Text
General Ledger Account – Text

Figure 2: Source CDS views, extracted with Cloud Data Integration (CDI)

 

Filtering and snapshotting the GL Account Line Items

Importing the CDS Views is skipped in this blog post as importing is simple and straightforward.

As the hierarchy model will contain quite a bit of join logic, and the transaction data volume is rather large, first the data is persisted in SAP Datasphere. As we don’t need all the line items, they are filtered on one specific company code, as you can see in the SQL view below. After deployment, the view is persisted, as you can see on the right side of the below figure, under the “Persistency” header.

This SQL view is later used for the main Analytical Dataset upon which the SAP Analytics Cloud Story is based. By the way, if you’re wondering about the naming convention: the prefix “tc08_” stands for the 8th test case that’s built in this space, so it’s just for my own organisation and not needed to adhere to. You can also see that the source table here has a different name than the CDS View that is used, and that’s just because I gave it that name when importing the CDS View.

Figure%203%3A%20Line%20item%20filter%20and%20snapshot

Figure 3: Line item filter and snapshot

Creating the hierarchy dimension

In SAP Datasphere, you can model your views with a graphical editor or with a SQL editor. I’ve chosen SQL for the hierarchy dimension, as I found it easier to trial and error with (it took a while before the hierarchy worked…) and it also shows you in one go all the applied logic. See below a screenshot of how this looks like in SAP Datasphere, and below that a copy of the code. Scroll past the code to see an explanation on the logic. Please note that you have to choose “Dimension” as your “Semantic Usage”, otherwise the hierarchy semantics can’t be set. In below screenshot this selection wasn’t made yet and was still set to “Relational Dataset”.

Figure 4: SQL dim

SELECT 
	"HI"."HierarchyUUID",
	"HI"."HierarchyName",
	CASE
		WHEN "HI"."NodeTypeField" = 'GLAccount' THEN "HI"."GLAccount"
		ELSE "HI"."HierarchyNode"
	END AS "HierarchyNode",
	"H2"."HierarchyNode" AS "HierarchyParentNode",
	"HI"."NodeTypeField",
	"HI"."NodeID",
	"HI"."ParentID",
	"HI"."GLAccount",
	CASE
		WHEN "HI"."NodeTypeField" = 'GLAccount' THEN "GT"."GLAccountName"
		ELSE "TX"."HierarchyNodeText"
	END AS "HierarchyNodeText",
	"TX"."Language"
FROM 
	"tc08_s4hierarchy_rt_cdi" AS "HI"
	LEFT OUTER JOIN
	"tc08_s4hierarchy_rt_cdi" AS "H2"
	ON ("HI"."HierarchyUUID" = "H2"."HierarchyUUID"
		AND "HI"."ParentID" = "H2"."NodeID"
		AND "HI"."ValidFrom" = "H2"."ValidFrom")
	LEFT OUTER JOIN
	"tc08_s4hier_rt_node_texts" AS "TX"
	ON ("HI"."HierarchyNode" = "TX"."HierarchyNode"
		AND "HI"."HierarchyName" = "TX"."GLAccountHierarchy"
		AND "TX"."Language" = 'EN'
		AND "TX"."ValidityStartDate" <= CURRENT_DATE
		AND "TX"."ValidityEndDate" >= CURRENT_DATE)
	LEFT OUTER JOIN
	"tc08_s4hier_glacc_txt" AS "GT"
	ON ("HI"."ChartOfAccounts" = "GT"."ChartOfAccounts"
		AND "HI"."GLAccount" = "GT"."GLAccount"
		AND "GT"."Language" = 'EN')
WHERE "HI"."GLAccountHierarchy" = '3010'
	AND "HI"."ValidFrom" <= CURRENT_DATE
	AND "HI"."ValidTo" >= CURRENT_DATE;

Several hierarchy features are not supported at time of writing, and that’s basically what the above code is getting you around. In the code, we fit the source hierarchy to SAP Datasphere, by choosing one hierarchy version, removing hierarchy and text time validity, by setting a fixed language, and by “merging” hierarchy nodes and GL Accounts into a single key field.

What is happening in the SQL? Let’s work top down through the code.

  1. Output field HierarchyNode. This is the field that we use as a child in our parent-child relationship. It also acts as the “technical name” in our SAP Analytics Cloud Story, containing either the GlAccount if it’s the lowest (“leaf”) node, or the id of an intermediary node.  In the source table, we already have a field HierarchyNode. The content of that field for intermediary nodes is fine. But for the leaf nodes, I would rather display the actual GlAccount id. Therefore, with the CASE statement we pick the GLAccount if the node is of type GlAccount, and otherwise stick to the HierarchyNode source field. By the way, it would have been easier to pick the “NodeID” source field as the child, but this is a long generated string ID that we don’t want to display to end-users. We do use that field for the join logic though. Also good to note: there is a ChildId in the source table, but it doesn’t provide you with values if it’s a leaf node.
  2. Output field HierarchyParentNode. The parent node is always an intermediary node, so we can just pick this value from the HierarchyNode of the parent record. The parent record can be found using the ParentID which points to the NodeID of the parent record. The first join in the code is therefore a self-join, with which the HierarchyNode of the parent record is found and renamed to HierarchyParentNode.
  3. Output field HierarchyNodeText. In the SAP Analytics Cloud Story, also the functional names should be displayed. Therefore the texts are retrieved for both the (intermediary) hierarchy nodes and the GL Accounts. The second join in the code retrieves the hierarchy node texts, where a filter is needed on language.
  4. Filter on GLAccountHierarchy. Here we choose one hierarchy version, which in this case corresponds to our company code filter on the line items.
  5. Filter on validity of the hierarchy. The currently valid hierarchy should be displayed, and therefore we filter the ValidFrom and ValidTo dates as such.

When the code is done, it’s a simple matter of applying the semantics. First the parent column and child column is selected from the hierarchy menu.

Figure%203b%3A%20hierarchy%20config

Figure 5: hierarchy config

Then the config is made to match the functional names to the technical names by setting the HierarchyNodeText as the label of the HierarchyNode field.

Semantics

Figure 6: Hierarchy column semantics

As a last step, in the view settings, only the HierarchyNode is defined as key. You can have only one field for the key definition of an hierarchy.

Figure%203d%3A%20HierarchyNode%20as%20the%20only%20key

Figure 7: HierarchyNode as the only key

GlAccount to HierarchyNode mapping

The key of the hierarchy is HierarchyNode. However, that field is not part of the line item data, and therefore we have to make it part of it. The line item data does contain the GL Account field, and the relation between GL Account and HierarchyNode is already part of the hierarchy dimension. The hierarchy could be directly joined into the line item data to get the HierarchyNode, but for clarity, a separate view is made for it.

Figure%205%3A%20mapping%20GlAccount%20to%20HierarchyNode

Figure 8: mapping GlAccount to HierarchyNode

The Analytical Dataset and orphan nodes

The Analytical Dataset is the view consumed by SAP Analytics Cloud, and here we associate the transaction data with the hierarchy. For a change, I have used a graphical view. First, the filtered line item view is inserted, after which the data is enriched with the HierarchyNode field by joining with the view that contains the GL Account to HierarchyNode mapping.

The choice for a left join is on purpose: there could be transaction data with GL Accounts that does not exist in the hierarchy dimension. For those records, a formula node is added, where all NULL values of field HierarchyNode are replaced by value ’00NOTASSGND’, which matches an entry in the hierarchy dimension data for nodes that are not assigned. Basically, the left join and the formula represent “orphan node” functionality that is not part of the hierarchy functionality at time of writing. Without the left join and formula, the transaction data would not show up in SAP Analytics Cloud when the hierarchy display would be used.

Figure%207%3A%20Formula

Figure 9: Formula

In the view properties, an association is defined to the hierarchy dimension, based on the HierarchyNode field.

Figure%208%3A%20assocation

Figure 10: Association to hierarchy dimension

Yes, a graphical view was used, but if you’re interested to see the code, from a graphical view you can simply choose “Preview SQL” to see the SQL representation of what was modeled. The below figure shows this feature, and the code is pasted below that.

Figure%209%3A%20preview%20SQL

Figure 11: Preview SQL

 

SELECT
  "tc08_s4h_items_filtered"."SourceLedger" AS "SourceLedger",
  "tc08_s4h_items_filtered"."CompanyCode" AS "CompanyCode",
  "tc08_s4h_items_filtered"."FiscalYear" AS "FiscalYear",
  "tc08_s4h_items_filtered"."AccountingDocument" AS "AccountingDocument",
  "tc08_s4h_items_filtered"."LedgerGLLineItem" AS "LedgerGLLineItem",
  "tc08_s4h_items_filtered"."LedgerFiscalYear" AS "LedgerFiscalYear",
  "tc08_s4h_items_filtered"."ChartOfAccounts" AS "ChartOfAccounts",
  "tc08_s4h_items_filtered"."ControllingArea" AS "ControllingArea",
  "tc08_s4h_items_filtered"."GLAccount" AS "GLAccount",
  "tc08_s4h_items_filtered"."ProfitCenter" AS "ProfitCenter",
  "tc08_s4h_items_filtered"."Segment" AS "Segment",
  "tc08_s4h_items_filtered"."BalanceTransactionCurrency" AS "BalanceTransactionCurrency",
  "tc08_s4h_items_filtered"."AmountInGlobalCurrency" AS "AmountInGlobalCurrency",
  "tc08_s4h_items_filtered"."FixedAmountInGlobalCrcy" AS "FixedAmountInGlobalCrcy",
  "tc08_s4h_items_filtered"."AmountInBalanceTransacCrcy" AS "AmountInBalanceTransacCrcy",
  "tc08_s4h_items_filtered"."AmountInTransactionCurrency" AS "AmountInTransactionCurrency",
  "tc08_s4h_items_filtered"."AmountInCompanyCodeCurrency" AS "AmountInCompanyCodeCurrency",
  "tc08_s4h_items_filtered"."AmountInFunctionalCurrency" AS "AmountInFunctionalCurrency",
  "tc08_s4h_items_filtered"."TransactionCurrency" AS "TransactionCurrency",
  "tc08_s4h_items_filtered"."CompanyCodeCurrency" AS "CompanyCodeCurrency",
  "tc08_s4h_items_filtered"."GlobalCurrency" AS "GlobalCurrency",
  "tc08_s4h_items_filtered"."FunctionalCurrency" AS "FunctionalCurrency",
  "tc08_s4h_items_filtered"."FiscalPeriod" AS "FiscalPeriod",
  "tc08_s4h_items_filtered"."FiscalYearVariant" AS "FiscalYearVariant",
  "tc08_s4h_items_filtered"."FiscalYearPeriod" AS "FiscalYearPeriod",
  "tc08_s4h_items_filtered"."PostingDate" AS "PostingDate",
  "tc08_s4h_items_filtered"."DocumentDate" AS "DocumentDate",
  IFNULL("tc08_s4hier_node_glacc_mapping"."HierarchyNode", '00NOTASSGND') AS "HierarchyNode"
FROM ("tc08_s4h_items_filtered" LEFT MANY TO ONE JOIN "tc08_s4hier_node_glacc_mapping" ON "tc08_s4h_items_filtered"."GLAccount" = "tc08_s4hier_node_glacc_mapping"."GLAccount")

 

The Story

Now it’s simply a matter of using the Analytic Dataset in an SAP Analytics Cloud Story. When you choose HierarchyNode in the Rows section, the hierarchy already shows up, by default with the functional name (Description). This is how the data was visualised in the first figure of this blog post. As you can see in the below figure, also the ID can be displayed instead.

Figure 12: Story

Conclusion

This blog post showed how you can use a typical SAP source system hierarchy in SAP Datasphere, and displaying it in SAP Analytics Cloud, by transforming the data to the supported hierarchy features at time of writing. This is just one way of getting it done, so feel free to take a different approach, and feel free to share your approach in the comments.

Assigned Tags

      10 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Paul Vatter
      Paul Vatter

      hi Sefan

      thanks for your blog!

      We have worked on the same topic with our customer and had a quite similar solution - maybe you already have some input on our open questions: 🙂

      As far as I can see you have now a dedicated "dimension" available in SAC containing just the G/L Account Hierarchy, correct? Or did you also consider to have just one "dimension" called G/L Account which also contains attributes like if it is an balance sheet account or not?

      Do you know if the sorting of the view is reflected in the sorting of the hierarchy?

      Do you have any good idea how to eliminate leading zeros without disturbing the sorting behaviour (more related to the cost center hierarchy)?

      Thanks in advance and best regards

      Paul

      Author's profile photo Deodutt Dwivedi
      Deodutt Dwivedi

      Hi Sefan,

      Thanks for the detailed blog, I tried the graphical way by importing the standard G/L Account & Hierarchy related CDS Views from my S/4HANA 2020 OP system. Unfortunately the data type for Parent, Child fields is automatically being assigned to Binary and cannot be overwritten from the configuration(probably will try import/export), This is causing issue in join.  Is it the reason why you have gone for scripting instead of graphical view creation?

      Regards,

      Deo

       

      Author's profile photo Sefan Linders
      Sefan Linders
      Blog Post Author

      Hi Deodutt,

      I went partially for SQL as it was easier to trial and error. I did not run into a datatype cast issue. Sounds like that needs a ticket to be logged. As a workaround, you could also try a cast operation (TO_NVARCHAR) in a graphical view formula node.

      Regards,

      Sefan

      Author's profile photo Deodutt Dwivedi
      Deodutt Dwivedi

      Thanks Sefan for checking and providing feedback.

      Regards,

      Deo

      Author's profile photo Adlin Sundararaj
      Adlin Sundararaj

      "tc08_s4hierarchy_rt_cdi" is the local name you used for CDS view I_GLAccountHierarchyNode?

      I do not see any field as NodeTypeField having value as 'GL Account'. Is it a custom field created?

       

      thanks

      adlin

      Author's profile photo Marianne Loenen
      Marianne Loenen

      Hi Adlin,
      Answering for Sefan since I happen to see this question...
      No, "tc08_s4hierarchy_rt_cdi" is the technical name (chosen on import) of the lineitems view https://api.sap.com/cdsviews/I_GLACCOUNTLINEITEMRAWDATA

      Hope this helps,
      Marianne

      Author's profile photo Oddmar Lid
      Oddmar Lid

      Hi,

      I have the same questions.
      Just wondering what the source data for the remote table "tc08_s4hierarchy_rt_cdi" can be?

      It is mentioned that the source CDS View for this remote table are:
      https://api.sap.com/cdsviews/I_GLACCOUNTLINEITEMRAWDATA

      But this can't be correct since this view does not contain the fields in the SELECT clause of the example SQL, like the fields:
      - HierarchyUUID
      - HierarchyName

      And I really can't see why you would mix in the transactional data into the Dimension definition of the Hierarchy.

      Any suggestion on what kind of source CDS View the Remote Table "tc08_s4hierarchy_rt_cdi" gets it data from?

      In my head, the source should have been the CDS View "I_GLAccountHierarchyNode" which contains the necessary on the Parten/Child relationship?
      But this do not contain the fields in the SELECT clause of the example SQL above.

      Regards

      Oddmar

      Author's profile photo Marianne Loenen
      Marianne Loenen

      hi Oddmar, you are right this is just one of the views that is being used and this is the one that holds the fact data. You can see at the top of Sefans post that there are more CDS views used;
      Raw Data of General Ledger Account Line Item
      General Ledger Account Hierarchy Node
      General Ledger Account Hierarchy Node – Text
      General Ledger Account – Text
      together the make up the GL account hierarchy information and how they link to the fact (the line item)
      sorry for confusing you...
      Marianne

      Author's profile photo Jayendra Parmar
      Jayendra Parmar

      Hi Sefan,

       

      Thank you for the well explained blog.
      We tried to follow the step by step approach mentioned in your blog, however when we consume the final analytical view in SAC story we get an error Please contact your administrator.
      Model: GL_ACCOUNT_HIER (this is the name of our analytical view).

      As soon as we remove the association in the analytical view, we are able to consume the view in SAC.

      Could you provide some assistance here?

       

      Regards.

       

      Author's profile photo Sefan Linders
      Sefan Linders
      Blog Post Author

      That error can mean anything, but in this case usually means that your hierarchy is not properly filtered or joined. As a result, your parent-child relations might not make sense as there are too many or too little records. Walk through the steps and code in this blog once more and really make sure that you have filtered on things like validity date and hierarchy name or number.