# Creating information mart table from a Link-Sat with history

**URL:** <https://forum.data-community.org/t/creating-information-mart-table-from-a-link-sat-with-history/1036>\
**Category:** Data Vault 2.0\
**Created:** [November 3, 2023, 9:19am UTC](https://forum.data-community.org/t/creating-information-mart-table-from-a-link-sat-with-history/1036 "2023-11-03T09:19:32Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![blitz](https://avatars.discourse-cdn.com/v4/letter/b/bbe5ce/32.png) [@blitz](https://forum.data-community.org/u/blitz)\
**Post date:** [November 3, 2023, 9:19am UTC](https://forum.data-community.org/t/creating-information-mart-table-from-a-link-sat-with-history/1036/1 "2023-11-03T09:19:32Z")

</div>

Hi All,  
Hope you are well!

I have a question on how to create a model in the information mart corresponding to a specific Link-Sat combination. Creating a fact table might be an obvious answer, but the satellite hanging off the link stores history, hence want to make sure that this the correct approach.  
Explaining through an example

If you look at the first snippet  
Link\_Sat\_1.JPG

 ![Link_Sat_1 (1)](https://europe1.discourse-cdn.com/flex013/uploads/datavaultusergroup/original/1X/2f12094d8a87edd80406802f43d0933ef765c7af.jpeg)

There is a Hub and Sat Product which consists of the base attributes of the product  
There is a Hub and Sat Banner with the details of the retail banners under which these products are sold  
Then there are banner specific attributes of a product like the website specific description, website URL etc, that I have put in a satellite hanging off a link between product and banner.  
Now these attributes are non-additive and can change over time as shown in the example for max purchase quantity.

Now if you look at the second screenshot for the information mart layer

Link\_Sat\_2.JPG

 ![Link_Sat_2 (1)](https://europe1.discourse-cdn.com/flex013/uploads/datavaultusergroup/original/1X/a47f586559bbce96417df2d4d23e2bfba36107f2.jpeg)

It shows an illustration of Hub & Sat Product being converted into dim\_product  
Hub & Sat Banner being converted into dim\_banner.  
The link between product and banner and the corresponding satellite converted into a factless fact table with product\_key and banner\_key

My concerns are the following:-  
This fact table will need to store records as a SCD type 2 to reflect the changes captured in the satellite.  
This fact table will contain all attributes from the satellite making it quite wide

Do you think this is the right approach?  
I don’t want to manufacture a dimension which is a combination of product and banner.

Please let me know what you think.

---

<div class="post-metadata">

**Author:** ![patrickcuba](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/patrickcuba/32/19_2.png) [@patrickcuba](https://forum.data-community.org/u/patrickcuba)\
**Post date:** [November 4, 2023, 12:07am UTC](https://forum.data-community.org/t/creating-information-mart-table-from-a-link-sat-with-history/1036/2 "2023-11-04T00:07:31Z")

</div>

Link-Sat for your facts ← oh yes!  
Aggregate the metrics and bring in the identity columns to create a star schema, even better

> **[Data Vault on Snowflake: Expanding to Dimensional Models](https://www.linkedin.com/pulse/data-vault-snowflake-expanding-dimensional-models-patrick-cuba)**
>
> Snowflake continues to set the standard for Data in the Cloud by taking away the need to perform maintenance tasks on your data platform and giving you the freedom to choose your data model methodology for the cloud. You will expect the same...

---

<div class="post-metadata">

**Author:** ![blitz](https://avatars.discourse-cdn.com/v4/letter/b/bbe5ce/32.png) [@blitz](https://forum.data-community.org/u/blitz)\
**Post date:** [November 7, 2023, 3:29am UTC](https://forum.data-community.org/t/creating-information-mart-table-from-a-link-sat-with-history/1036/3 "2023-11-07T03:29:38Z")

</div>

Thanks @patrickcuba . Appreciate your prompt response.

Just to clarify, in the given example, are we saying it’s ok to have a wide SCD type 2 fact table?

---

<div class="post-metadata">

**Author:** ![patrickcuba](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/patrickcuba/32/19_2.png) [@patrickcuba](https://forum.data-community.org/u/patrickcuba)\
**Post date:** [November 7, 2023, 4:01am UTC](https://forum.data-community.org/t/creating-information-mart-table-from-a-link-sat-with-history/1036/4 "2023-11-07T04:01:29Z")

</div>

nope — no where in the article does it state the fact is a an scd type 2 table; please review

---

<div class="post-metadata">

**Author:** ![blitz](https://avatars.discourse-cdn.com/v4/letter/b/bbe5ce/32.png) [@blitz](https://forum.data-community.org/u/blitz)\
**Post date:** [November 7, 2023, 7:42am UTC](https://forum.data-community.org/t/creating-information-mart-table-from-a-link-sat-with-history/1036/5 "2023-11-07T07:42:24Z")

</div>

That was my whole question. Can you please look at the example that I had shared originally and kindly suggest what should be best way to do it.  
It’s not a regular scenario.

---

<div class="post-metadata">

**Author:** ![patrickcuba](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/patrickcuba/32/19_2.png) [@patrickcuba](https://forum.data-community.org/u/patrickcuba)\
**Post date:** [November 7, 2023, 7:57am UTC](https://forum.data-community.org/t/creating-information-mart-table-from-a-link-sat-with-history/1036/6 "2023-11-07T07:57:12Z")

</div>

i did … the satellite in a DV can be used to support an SCD AND fact table, remeber sats don’t have end-dates

---

<div class="post-metadata">

**Author:** ![blitz](https://avatars.discourse-cdn.com/v4/letter/b/bbe5ce/32.png) [@blitz](https://forum.data-community.org/u/blitz)\
**Post date:** [November 7, 2023, 8:29am UTC](https://forum.data-community.org/t/creating-information-mart-table-from-a-link-sat-with-history/1036/7 "2023-11-07T08:29:13Z")

</div>

Apologies, maybe I am unable to explain my question.

The satellite hanging off my link needs to be converted into a SCD type 2.  
The grain of this satellite is a combination of keys coming from 3 different hubs which forms the link.  
These attributes can’t be aggregated, like I have shown in the example.

How do I convert this Link + Sat into a fact and dimension?  
There is no natural key for this dimension as it is a combination of 3 concepts coming together.

Note: These 3 concepts will have their own separate dimension tables containing their base attributes.  
These attributes are over and above that.

Maybe the problem is how I have done the DV layer itself?

---

<div class="post-metadata">

**Author:** ![Carl](https://avatars.discourse-cdn.com/v4/letter/c/ee7513/32.png) [@Carl](https://forum.data-community.org/u/Carl)\
**Post date:** [November 9, 2023, 11:38am UTC](https://forum.data-community.org/t/creating-information-mart-table-from-a-link-sat-with-history/1036/8 "2023-11-09T11:38:08Z")

</div>

Hi Blitz,

From what i can understand, I think you are trying to create a Factless Fact table? Or, is your Fact table tracking the Sales of Products?

I’ll propose an option based on my understanding but perfectly happy for those more in the know to correct me if I’m barking up the wrong tree.

If you are trying to create a Factless Fact table then I think you need to determine a grain at which you want to be able to track the ‘Facts’. For example, do you want to know what the Product, Banner and Product\_Banner attribute combinations are for every day, or, at the end of a month?

I think you need to determine your grain and then utilise the approach of a creating a PIT table to assist in the creation of your Fact table. Keep the attribute information in the Satellites / Dimensions (all 3 of them). Your Fact table should then include a Date column (to denote your grain) and then Dimension Key columns for each date (row), which link back to your Dimensions to retrieve the correct attribute information for that particular date.

Your Dimensions are created from your Satellites. You will need to create a DimensionKey to denote row uniqueness. You cannot use the HUB Key as this will not denote row uniqueness.  
You cannot use your DiffHashKey as this could also not be unique if attributes revert back to a historical combination of attributes. You may have a sequence number column you can use. Or, in my case I created another HashKey value by combining the Hub\_Key and the LoadDate and then utilised this as my DimKey.

Your Fact table is now no longer an SCD2 as the attributes exist in the Dimensions (which are created from your Satellites). But, you need to determine a grain that meets your need for the Factless Fact table.

Alternatively, if your Fact table is tracking the sale of Products then your Grain is the date of sale but you still need to include DimensionKeys for each of the 3 Dimensions to return the correct attribute information for the date of sale.

I hope I have helped in someway, even if to generate a clearer discussion 🙂

---

<div class="post-metadata">

**Author:** ![blitz](https://avatars.discourse-cdn.com/v4/letter/b/bbe5ce/32.png) [@blitz](https://forum.data-community.org/u/blitz)\
**Post date:** [November 13, 2023, 3:01am UTC](https://forum.data-community.org/t/creating-information-mart-table-from-a-link-sat-with-history/1036/9 "2023-11-13T03:01:04Z")

</div>

Thanks Carl, yes I am trying to create a factless fact table, however the I can’t move the attributes into individual dimensions, since these attributes exist against a combination of Product and Banner.

Like “maximum quantity” you can purchase is not just a banner or product attribute.  
Ex.  
You can purchase **6 bottles** of _Cat amongst pigeons_ from _Liquorland_  
You can purchase **8 bottles** of _Cat amongst pigeons_ from _Vintage Cellars_  
You can purchase **3 bottles** of _Jacob’s Creek_ from _Vintage Cellars_

As you can see this value differs for the combination of Product and Banner  
Also this maximum quantity can change over time and hence the need of storing SCD2 data,

So to answer your question I want the product-banner attributes to be tracked whenever they get changed, similar to tracking changes to only product attributes or only banner attributes.

Product-Banner in itself maybe qualified to become a ‘manufactured’ dimension, if not a factless fact, but I am not sure if that’s the right thing to do - hence the original question.

Hope this clarifies my predicament.

---

<div class="post-metadata">

**Author:** ![Carl](https://avatars.discourse-cdn.com/v4/letter/c/ee7513/32.png) [@Carl](https://forum.data-community.org/u/Carl)\
**Post date:** [November 13, 2023, 11:04am UTC](https://forum.data-community.org/t/creating-information-mart-table-from-a-link-sat-with-history/1036/10 "2023-11-13T11:04:59Z")

</div>

Hi Blitz,

It looks like your sat\_product\_banner is a Link Sat off of link\_product\_banner. So, can you not treat sat\_product\_banner as any other Sat and track your attribute changes over time? Your ‘Product Banner HK’, from the link, acts as a sort of HUB\_Key but then you create a Hash Diff Key to track changes in your SAT. If needed as a Dimension later you can just use your sat\_product\_banner SAT as a source for your dimension and create a DimKey by either creating a hashkey of ‘Product Banner HK’ and ‘LoadDate’ or by using a sequence number if you have one.

Does that sound about right?

Carl

---

<div class="post-metadata">

**Author:** ![blitz](https://avatars.discourse-cdn.com/v4/letter/b/bbe5ce/32.png) [@blitz](https://forum.data-community.org/u/blitz)\
**Post date:** [November 14, 2023, 4:35am UTC](https://forum.data-community.org/t/creating-information-mart-table-from-a-link-sat-with-history/1036/11 "2023-11-14T04:35:10Z")

</div>

Hey Carl,  
_“It looks like your sat\_product\_banner is a Link Sat off of link\_product\_banner”_

Correct, if you look at my original post, that’s exactly what I have shown in the diagram 🙂

My problem isn’t tracking the changes in DV layer, that’s very straightforward.  
My question always was regarding converting this structure into either a factless fact or a dimension table, and both of them don’t seem to be an ideal approach

- With a dimension approach - I am creating a new _business_ concept of Banner-Product which doesn’t really exist in real life, even though there are attributes against it.
- With the factless fact approach - I need to create a really wide fact table with SCD2 type data,

My question is which is the better approach of the two or is there a better way of handling such a scenario.  
Unfortunately this type of scenario is not covered in Dan’s book or the links that Patrick shared.

---

<div class="post-metadata">

**Author:** ![Carl](https://avatars.discourse-cdn.com/v4/letter/c/ee7513/32.png) [@Carl](https://forum.data-community.org/u/Carl)\
**Post date:** [November 17, 2023, 9:31am UTC](https://forum.data-community.org/t/creating-information-mart-table-from-a-link-sat-with-history/1036/12 "2023-11-17T09:31:43Z")

</div>

Hi Blitz,

Ok, I get what you’re asking. My gut is leaning me towards thinking that the Dimension approach would be the better option, however, like you, i’d be interested to hear what other people think.

As a side, could i ask which tool you use to show the ERD like image showing the relationships and DV Objects?

Thanks,

Carl

---

<div class="post-metadata">

**Author:** ![blitz](https://avatars.discourse-cdn.com/v4/letter/b/bbe5ce/32.png) [@blitz](https://forum.data-community.org/u/blitz)\
**Post date:** [November 19, 2023, 11:00pm UTC](https://forum.data-community.org/t/creating-information-mart-table-from-a-link-sat-with-history/1036/13 "2023-11-19T23:00:12Z")

</div>

Thanks Carl, yes I am leaning towards creating a dimension as well.

The tool is called Lucid charts. It’s not that great tbh, a lot of manual effort required 😅

---

<div class="post-metadata">

**Author:** ![AHenning](https://avatars.discourse-cdn.com/v4/letter/a/96bed5/32.png) [@AHenning](https://forum.data-community.org/u/AHenning)\
**Post date:** [November 20, 2023, 8:54am UTC](https://forum.data-community.org/t/creating-information-mart-table-from-a-link-sat-with-history/1036/14 "2023-11-20T08:54:23Z")

</div>

Dimension seems to be the best choice. When I generate marts from DV I create dimensions from satellites and facts from non historized links. That makes is easy and straight forward.

---

<div class="post-metadata">

**Author:** ![azimed](https://avatars.discourse-cdn.com/v4/letter/a/b4bc9f/32.png) [@azimed](https://forum.data-community.org/u/azimed)\
**Post date:** [July 1, 2024, 7:30am UTC](https://forum.data-community.org/t/creating-information-mart-table-from-a-link-sat-with-history/1036/15 "2024-07-01T07:30:39Z")

</div>

Unfortunatelly late to the discussion, but wonder why not to have a product\_banner (more specifically product banner max sales change) fact (instead of factless). Conceptually it’s still a fact as it has a metric, a date and attributes.
