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

[Sefan_Linders]

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 that end of 2023, the new functionality Hierarchy with Directory has been released, which provides a far easier approach to model your SAP S/4HANA hierarchies in SAP Datasphere. A series of blog posts has been written on it, starting here: An Introduction to Hierarchy with Directory

This blog post will stay live for the rare use cases where this approach still serves a purpose - although I honestly can't really think of one.

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 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 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.

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 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 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 9: Formula

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

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 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.