# Business Vault Satellite structure

**URL:** <https://forum.data-community.org/t/business-vault-satellite-structure/1255>\
**Category:** Data Vault 2.0\
**Tags:** business-vault\
**Created:** [April 17, 2024, 7:43pm UTC](https://forum.data-community.org/t/business-vault-satellite-structure/1255 "2024-04-17T19:43:07Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![saqib](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/saqib/32/45_2.png) [@saqib](https://forum.data-community.org/u/saqib)\
**Post date:** [April 17, 2024, 7:43pm UTC](https://forum.data-community.org/t/business-vault-satellite-structure/1255/1 "2024-04-17T19:43:07Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Pfosser](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/pfosser/32/278_2.png) [@Pfosser](https://forum.data-community.org/u/Pfosser)\
**Post date:** [April 18, 2024, 8:23am UTC](https://forum.data-community.org/t/business-vault-satellite-structure/1255/2 "2024-04-18T08:23:00Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![saqib](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/saqib/32/45_2.png) [@saqib](https://forum.data-community.org/u/saqib)\
**Post date:** [April 18, 2024, 1:06pm UTC](https://forum.data-community.org/t/business-vault-satellite-structure/1255/3 "2024-04-18T13:06:11Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Pfosser](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/pfosser/32/278_2.png) [@Pfosser](https://forum.data-community.org/u/Pfosser)\
**Post date:** [April 18, 2024, 2:36pm UTC](https://forum.data-community.org/t/business-vault-satellite-structure/1255/4 "2024-04-18T14:36:33Z")

</div>

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

```auto
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.

---

<div class="post-metadata">

**Author:** ![squash7733](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/squash7733/32/268_2.png) [@squash7733](https://forum.data-community.org/u/squash7733)\
**Post date:** [April 22, 2024, 8:55am UTC](https://forum.data-community.org/t/business-vault-satellite-structure/1255/5 "2024-04-22T08:55:05Z")

</div>

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.