Business Vault Satellite structure

Data Vault 2.0
1

Hello,

We have a LINK that tracks all the Gift Cards used a By a Customer. It is a Many to Many Relationship, a Customer may use multiple Gift Cards, and a single Gift Card may be used by Used by Multiple Customers (partial balance etc.)

We need to create a Business Vault Satellite that can capture this many-to-many association as a single record. The single records should have all the Gift Cards by all the Customers that have used those Gift Cards. What is the best way to design this BV SAT?

LINK_CUSTOMER_GIFTCARD:

Customer ID Gift Cards Acct Number
AAA 1111
BBB 1111
AAA 2222
CCC 2222
DDD 3333

Information we need to surface:

Customer IDs Gift Cards Acct Numbers
AAA, BBB, CCC 1111;2222
DDD 3333;

Thanks,
Saqib

2

Hi.
Do you need to write down the SELECT statement to get the result? It is not problem, but You must specify what DB technology are you using because the string aggregation isn’t a standard SQL function.
But!
I see there one issue. Do you really need a BV Satellite? What is the business key (of which business object) which is the satellite for?

3

Hi @Pfosser . We are on Snowflake.

The reason I was thinking of a BV SAT is because we want to capture the history of how the clusters are evolving over time. But I am not sure what will the BK for this BV SAT. Any advise?

Thanks.

Saqib

4

I have no Snowflake, so I can’t to run the select, but it will looks like:

select listagg(distinct customer_id, ', ') within group (order by customer_id) customer_ids
     , gift_cards
  from (
select customer_id
     , listagg(distinct gift_card, ';') within group (order by gift_card) gift_cards
  from link_customer_giftcard
 group by customer_id
)
 group by gift_cards

But I would like to suppose that you have more tables in your data model.

  1. you have HUB for Customer ID - so you will join it before
  2. you have HUB for Gift Cards Acct Number - so you will join it before
  3. you have a SAT which keeps the effectivity history of the LINK, or the LINK is a Non-historized LINK
    So you will join also this satellite and use it in the grouping clause to have the history in the output.
5

Hello Saqib

If I get your problem, U have

HUB_CUSTOMER (BK: Customer-id)
HUB_GIFTCARD (BK: Acct-Number)
in M:N relationship.

So your LINK_CUSTOMER_GIFTCARD will capture the customers who were allocated a GiftCard - whether now or historically.

The corresponding LINKSAT will capture the relationship between a Customer and a GiftCard - such as

When the Card was allocated to the Customer
Date relationship expired
Current status - Active/Inactive/Suspended/etc…
etc

Do not believe U need a BV-SAT since this relationship & attributes should come down from your source OLTP system into the RV (Raw Vault).

Hope this makes sense.