Using hierarchy with SQL View in SAP Datawherehouse Cloud

[#1]

What is the best way to define a hierarchy within a SQL View using the semantic of Analytical Dataset?

Is it possible to configure the hierarchy within the SQL definition or is it only possible using Associations with other dimensions?

[XaviPolo · Accepted solution]

Hello,

Hierarchies must be defined in Dimensions (Views or Tables with semantic "Dimension") and associate them to Facts (Views or Tables with semantic "Analytical Dataset").

I try to explain it with an example:

Let's imagine a Parent/Child Product hierarchy, it should have two fields similar to PRODUCT_ID and PRODUCT_ID_PARENT.

You may encounter two cases in the Product master data (Dimension):

• PRODUCT_ID and PRODUCT_ID_PARENT fields are in the dimension: You can directly create the hierarchy in the dimension itself.
• You only have the PRODUCT_ID in the dimension and the hierarchy is defined in another table PRODUCT_HIERARCHY (with PRODUCT_ID and PRODUCT_ID_PARENT). In this case, you must define that Table or View with semantic type "Hierarchy" and define the Parent and Child fields. After that in the Product Dimension associate the PRODUCT_HIERARCHY by PRODUCT_ID.

Now you can associate the Product Dimension to an Analytical Dataset and the Hierarchy will be available in Product Dimension in SAC.

Regards,

[fagonzalez5]

Hello Diego,

We create a plain view from BW 0MATERIAL HIERARCHY extractor structure with the following script:

CREATE VIEW [dbo].[SAPP_V_JERARQUIA_MATERIALES] AS

(

select n1.nodename n1, n2.nodename n2, n3.nodename n3, n4.nodename n4, n5.nodename n5,

n1.txt n1_txt, n2.txt n2_txt, n3.txt n3_txt, n4.txt n4_txt, n5.txt n5_txt,

(case

when n5.IOBJNM='0MATERIAL' then n5.nodename

when n4.IOBJNM='0MATERIAL' then n4.nodename

when n3.IOBJNM='0MATERIAL' then n3.nodename

when n2.IOBJNM='0MATERIAL' then n2.nodename

when n1.IOBJNM='0MATERIAL' then n1.nodename

end) MATERIAL_C

from

[dbo].[SAPP_V_0MATERIAL_HIER_L5] n5

right join [dbo].[SAPP_V_0MATERIAL_HIER_L4] n4 on (n5.parentid=n4.nodeid and n5.hienm=n4.hienm )

right join [dbo].[SAPP_V_0MATERIAL_HIER_L3] n3 on (n4.parentid=n3.nodeid and n4.hienm=n3.hienm )

right join [dbo].[SAPP_V_0MATERIAL_HIER_L2] n2 on (n3.parentid=n2.nodeid and n3.hienm=n2.hienm )

right join [dbo].[SAPP_V_0MATERIAL_HIER_L1] n1 on (n2.parentid=n1.nodeid and n2.hienm=n1.hienm )

)

Previously we´ve created one view per hierarchy level (L1, L2, L3, etc..)

create view [dbo].[SAPP_V_0MATERIAL_HIER_L1] AS (

SELECT

hienm, nodeid, childid, parentid, nodename, iobjnm, txt

FROM

[dbo].[SAPP_V_0MATERIAL_HIER]

WHERE tlevel = 1

)

Regards

Fernando