Hierarchy validation at the time of data load

[liliana_gil]

Hi Experts, I've a Local Table (Dimension) with a Parent-Child Hierarchy. For that table, we upload data manually, from a CSV. We are using that table in a view and then in an Analytical Dataset that is exposed to a SAC story.

When the hierarchy data is wrong (ie. a node has multiple parents, or no parent), we never get an error when we load the data, but we get the error in SAC story.

The question is: Is it possible to activate a hierarchy validation at the time of data load in DWC?

Thanks in advance,

Liliana

[Sven_Knöpfler · Accepted solution]

Hello Liliana,

as far as I know, there is no hierarchy validation built in to the DWC.

But there is a SQL function that does that:

https://help.sap.com/viewer/09f734c2169c4661b1aa15c00022ab21/2021_01_QRC/en-US/f29c70e984254a6f8df76...

The syntax for a parent-child hierarchy looks like this (you have to make a "table function" sql view to make this work in the DWC):

/********* Begin Procedure Script ************/
return SELECT
HIERARCHY_RANK,
HIERARCHY_TREE_SIZE,
HIERARCHY_PARENT_RANK,
HIERARCHY_LEVEL,
HIERARCHY_IS_CYCLE,
HIERARCHY_IS_ORPHAN,
node_id as "<your_child_column>",
parent_id as "<your_parent_column>"
FROM
hierarchy (
source (
select
"<your_child_column>" AS node_id,
"<your_parent_column>" AS parent_id
FROM
"<your schema>"."<your uploaded table>"
ORDER BY
node_id
) orphan root;
/********* End Procedure Script ************/

You could use this sql view in a dwc data flow and use the the data flow target table as source for your hierarchy dimension. Like that you trigger the validation and receive an error from the system in case the hierarchy is not well defined.

Let me know if that helped 🙂

Kind regards

Sven

[Sven_Knöpfler · Accepted solution]

Hello Liliana,

I saw you accepted the answer. Can I ask you if the approach worked for you or how you did the implementation in the end?

Thanks and kind regards

Sven