End to End Designing the Multilevel Hierarchy in Datasphere

[kartheekkk]

In this blog we would like to cover up how to design multilevel Hierarchy in Datasphere on CDS views.

I have considered Profit center in my scenario and Below are the list of standard CDS views required to design Hierarchy.

CDS View Name	SQL VIEW NAME	CDC	Support DataFlow	Support Replication flow
I_PROFITCENTERTEXT	IFIPROFITCENTERT	NO	Yes
I_PROFITCENTER	IFIPROFITCENTER	YES		Yes
I_PROFITCENTERHIERARCHY	IFIPROFITCENTERH		Yes
I_PROFITCENTERHIERARCHYTEXT	IFIPRFTCTRHT		Yes
I_PROFITCENTERHIERARCHYNODE	IPRFTCTRHNODE		Yes	No
I_PROFITCENTERHIERARCHYNODET	IFIPRFTCTRHNODET	No	Yes	No

To Achieve a hierarchy Representative key plays a key role. If we miss deciding the representative key then the Hierarchy will not be visible.

We don't have option like in BW - Tcode RSH1 in datasphere to check whether Hierarchy is enabled or not at Master data dimension level or Fact association level

What is representative key ?

In an analytical scenario where data is exposed as dimension, the master data can have multiple key fields, but a single key is required to access the data. That's why this field must be identified as a representative key field 

Here i try to explain on the standard CDS view name for better understanding while designing replace the standard view name with your custom view name in Datasphere

I_Profitcentertext : 

Semantics Usage as Text  

Preparing the Hierarchy Directory : 

I_PROFITCENTERHIERARCHYTEXT ;

I_PROFITCENTERHIERARCHY : This CDS view will help us to choose multiple hierarchy versions

Semantics Usage Type : Dimension

Add I_PROFITCENTERHIERARCHYTEXT  as Text Association

Now moving to Preparing Hierarchy node 

I_PROFITCENTERHIERARCHYNODETEXT :

I_PROFITCENTERHIERARCHYNODE :

Add the below association Before choosing the semantics to  Hierarchy with Directory

I_PROFITCENTERHIERARCHY

I_PROFITCENTERHIERARCHYTEXT

Choose the semantics usage as Hierarchy with Directory 

Once click on the Hierarchy with Directly Setting we have to pass the fields 

Parent : once click on the field you will be able to see the dropdown and choose Parent node 

Child : Hierarchy Node

Hierarchy Directory : I_PROFITCENTERHIERARCHY( for your ease of understanding i have given standard CDS view name. Here you will see the Business name of the view as per your design

Node Type : we will see 3 unique values under Node type R- ROOT , N-Node , L - Leaves

Node type values : initially it will be blank click on the + icon Next to Node type values to Add values as shown in my screenshot 

Now click on this icon to assign the representative key 

Add Hierarchy Node as Representative Key to this view 

Now the final step to add this hierarchy view to Profit center Dimension 

I_PROFITCENTER

Add I_PROFITCENTERHIERARCHYNODE as association to I_PROFITCENTER

Add I_PROFITCENTERTEXT as association to I_PROFITCENTER( We can ignore this step as you can fine the Description field is available in I_PROFITCENTER itself you can decide the semantics as Text and assign the text to Profit center

Now add  I_PROFITCENTER(Dimension) as association to your fact view & Deploy .

Go to your Analytical view Click on Fact source 

In Details tab find for profit center and click on the right side arrow 

Enable the check box Use Associated Dimension to see the hierarchy 

Now click on preview once deployed.

You will see the Hiearchy option if all went well 

Select your Hierarchy from Drop down. 

At this moment we don't have option to apply Hierarchy Variable at analytical model level tried applying input parameter at Hierarchy dimension level which ended up with error message

Review the following errors: " - CSN Compilation failed with following messages: Errors(ORIGINAL): (in entity:“IT.2VD_RTR_PROFITCENTERHIERARCHY_01”) Table-like entities with parameters are not supported for conversion to SQL [Ln 3459] Errors(FORMATTED): (in entity:“IT.2VD_RTR_PROFITCENTERHIERARCHY_01”) Table-like entities with parameters are not supported for conversion to SQL [Ln 3459], Correlation ID: 4c684267-8ea9-42d6-44fd-3adf8d7587b3 

and SAP is releasing it in their upcoming release till the time we have to filter the required Hierarchy at I_PROFITCENTERHIERARCHY view.

Here is the final Output at Analytical Model level

Are you still not able to see the Hierarchy ?? Execute the below steps 

Step 1 : Execute tcode KCH2 to display Profit center hierarchy find your hierarchy 

Step 2 : Execute Tcode HRY_REPRELEV  

SETCLASSS = 0106 (in my example)

Execute 

Step 3:  Enable the check boxes as   

• Report Relevant  
• Auto Replicate 

Step 4 : Click on Save 

Step 5 : No Entries before executing the Hierarchy Replication Tcode  

Step 6: Execute Tcode : HRRP_REP and select your Hiearchy name at Hierarchy ID

Step 6 : Refresh the CDS View I_PROFITCENTERHIERARCHY