Hierarchy in Dependent Child

Data Vault 2.0
1

Hi.

We have hierarchy in the Line Item level (Dependent Child). How do we model that hierarchy?

Here is what the ORDER and ODER_LINE look like.

ORDER_HEADER:

ORDER_HEADER_KEY ORDER_NO TOTAL_AMOUNT SHIP_TO BILL_TO
aaa Sales Order 1 100 nnn nnn
bbb Purchase Order 2 200 nnn nnn
ccc Sales Order 3 300 nnn nnn
ddd Return Order 4 25 nnn nnn

ORDER_LINE:

ORDER_LINE_KEY ORDER_HEADER_KEY PRIME_LINE_NO DERIVED_FROM_ORDER_LINE_KEY DERIVED_FROM_ORDER_HEADER_KEY CHAINED_FROM_ORDER_LINE_KEY CHAINED_FROM_ORDER_HEADER_KEY AMOUNT
1111 ccc 1 1113 bbb 200
1112 ccc 2 1114 bbb 100
1113 bbb 1 200
1114 bbb 2 100
1115 aaa 1 75
1116 aaa 2 25
1117 ddd 1 1116 aaa 25

Note that the ORDER_LINE has child parent relationship to the ORDER_HEADER and ORDER_LINE.

Here is the DV model for the ORDER with the ORDER_LINE as a DEPENDENT CHILD:

ORDER
ORDER835Ă—738 55 KB

I have reviewed @patrickcuba 's book and Dan’s book but didn’t find any pattern to model this. Please advise.

Thanks,
Saqib

2

You can easily solve that using Recursive CTE’s (Oracle’s Connect By) even deploy that as a Recursive VIEW in Snowflake

3

Hi @patrickcuba . I am not following you. I can use the Connect By in the INFOMART. But the RAW_VAULT level how do we model this hierarchy in the dependent child?

Thanks,
Saqib

4

Remember, DV does not seek to replace IM; it never did.

5

Hi @patrickcuba ,

I wasn’t looking for replacing IM, but more for a way to define the hierarchy of the LINE ITEMs in the Raw DV.

Thanks,
Saqib

6

I’m sending you something as a former student that I believe will give you exactly what you need.

7

This requirement is explained in:

  • “Building a Scalable Data Warehouse with Data Vault 2.0” book, section 4.4.5.2 “Dependent Child Key” (page 111)

Basically suggesting adding a “line-item” column on the LINK table.

1 Like