settingsLogin | Registersettings
Show Menu

What to do with a sourcesystem with an history itself

+1 vote
Hi folks,

I have to source a system which has a history itself with begin-date and end-date in nearly all tables.
So if I want to take all the history it is difficult to avoid combining the history of two or more tables (into raw vault).
And in my opinion this combining is too much logic or in other words this combining is using soft-rules which should be avoided on the way to the raw vault.
I could of course take always the current values as a snapshot. But what to do with the existing history?
Rerun the ETLs for every day of the last 15 years?
And it is possible that there are changes in the old history...

Any suggestions are appreciated!

Thanx for your time!
Guenther Herzog
asked Aug 22, 2016 by gherzog (380 points)

2 Answers

0 votes
Hi Guenther,

Very interesting question.
Although I never had to deal with this kind of situation, I would like to give it a go and think about it with you.

Assuming you have your "skeleton" of hubs and links in your raw vault and every table is potentially becoming a satellite, there are 2 approaches here I guess.

You could replace the system generated Load_Date of your satellite for the initial load with the load_date from the source system.
However, I would also put the source systems Start_Date and End_Date into the satellite as regular attributes. This because of the fact, that they really are attributes, and you only leveraging them to simplify your loading technique.

Downsides would be here that you need a slightly modified pattern to load your satellites initially, and the real load date would be lost.

Another approach could be to use the regular satellite loading pattern in the raw vault. Attributes will stay attributes, and load date will stay the load date.

On the way out : for example when loading your data mart, you could use the source systems load date to determine your SCD type 2 records instead of the regular system generated load date.

Also you could create a Business Vault View to represent the information like you want, while staying true to the raw vault approach.

I hope this will help a little !


Jacob Siemaszko
answered Oct 22, 2017 by simco_admin (660 points)
0 votes
I will extract all the historical records from the source and overwrite the tables of the data warehouse.
answered Oct 22, 2017 by neiltang (540 points)
Scalefree Scalefree

Upcoming Trainings

  • July 01 to 03 - Amsterdam (English) Data Vault 2.0 Boot Camp and Certification

  • August 05 to 07 - Berlin (German) Data Vault 2.0 Boot Camp and Certification

  • September 9 to 13 - Hanover WWDVC EU

  • September 16 to 18 - Brussels (English) Data Vault 2.0 Boot Camp and Certification

  • October 21 to 23 - Hanover (German) Data Vault 2.0 Boot Camp and Certification

  • November 18 to 20 - Amsterdam (English) Data Vault 2.0 Boot Camp and Certification

  • December 02 to 04 - Dusseldorf (German) Data Vault 2.0 Boot Camp and Certification

  • December 16 to 18 - Zurich (English) Data Vault 2.0 Boot Camp and Certification

  • Contact us for inhouse training engagements.
    Visual Data Vault: Logical Modelling for Data Vault - Free Download!
    Scalefree is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 United States License.
    Permissions beyond the scope of this license are available in our license. | DWH Wiki | Recent questions RSS feed | Imprint | Provided by Michael Olschimke