Normally history is created when descriptive informatie changes for a specific business key. Those changes are store in a Satellite.
Now I have to model a Data Vault where the source system has a separate table for history.
I could use a Multi-active Satellite, but from what I ave read this is used for cases where a business key has multiple values which are still valid.
But when I use Multi-active Satellite for my case then there are is a values that is valid and the other values are not valid.
Does anyone has a good suggest how I should model this with the right construction?
migration scenario, chapter 12 The Data Vault Guru: a pragmatic guide on building a data vault - Cuba, Mr Patrick | 9798691308086 | Amazon.com.au | Books
Hi, the Data Vault standard separates ingestion from consumption of data to make the ingestion more agile.
Thinking on this principle, if your source system separates this 2 tables, the most logical way is to ingest 2 different tables in the vault.
In other words, create a historical Satellite that you can query to create your information marts, if this satellite is updated or not, and how to handle the logic for ingestion to know what is current and what is history is a bit more tricky, but in this case the source system does it for you.
I hope this idea help you to see some possible solutions.
@cx1964 - if I understand U correctly, your source application keeps the current record in one table and puts historical changes in another table.
When there is a change to the current record, the new record goes into the Current-Table and the previous goes into the History-Table with Effectivity (i.e Effective-From & Effective-To) dates
- am I right?
U can model the above using just one Satellite by using the Effectivity Dates in Type2 Slowly-changing Dimension.
This way the Sat will contain the Current followed by all the historical records with Effectivity Dates in sequence. Its Hub will have the common BK.
I understood, that DataVault2 support reference tables. I also understood my problem can be solved by using reference tables. Does anyone have examples how to apply reference tables in DataVault2?