settingsLogin | Registersettings
Show Menu

Modelling InventoryTable where an active period is already defined

0 votes
I'm trying to model an Inventory table using DV-modelling 2.0. Our source system has an Inventory table like: idInventoryKey, dtInventory, dtInventoryEnd, Position
For a certain inventory key, a position exists during a certain period. When a new position arrives, for the same dtInventory, an update takes place and the position is adjusted. When the transactiondate does not already exists, a new inventory record is created with the new date and position, and the previous record for this inventory key is end-dated.
When I want to model this table using the DV techniques, I create a Hub (for the inventory key), and a satellite. But the system generated load-date (and load end-date) is somehow superfluous, because the "real" dates are the inventorydates dtInventory and dtInventoryEnd. Filtering on this period gives me the most active position (where dtInventoryEnd is null).
Is it appropriate in this case to use the inventory dates from the source system as the "real" load dates? When not, how do I retrieve the position for a certain period?
asked Jan 3, 2016 in Modelling by peter-frissen (120 points)

2 Answers

0 votes
I don't see why the system generated load-end-dates would not also be NULL and in this case, you would be able to query in exactly the same manner with the load-end-dates as with the source system end dates.

As for the question around the use of source system times within the Data vault model in general. It can be fine to use them within the model but your business needs to have very consistent times across the whole of the business systems, low transactional frequency and acceptance of this as a risk moving forwards. The risk is that if a system is introduced to the vault that does not comply then the information presented to the business will not be representative of what is actually happening. If this is an acceptable risk then you can go ahead but it will constrain you going forwards and is not the preferred approach. If you are going ahead I'd make sure you get some written confirmation of acceptance of this risk so it can be shown if this does become a problem in the future.
answered Jan 7, 2016 by liamwhales (220 points)
0 votes
The load dates have a different meaning than the business dates provided. Load dates tell you when the data entered the data warehouse (staging or datavault). These are dates under control of the data warehouse. The business dates may not be the same. What if the dtInventory is today, but you only load that record tomorrow (or even later)? By making use of both types of dates, you introduce bitemporality, which opens up new possibilities. If you get a correction for the same dtInventory (update as you describe it), how would you know when this update entered your data warehouse? You don't unless you use the load dates.

To retrieve the position for a certain period in that case (using both types of dates), you need to compare against both of them, depending on what you actually want to see. You can make sure that the "reference" date falls between the load dates and the business dates (which would mean: give me the current position as we know it now), or use the reference date between the business dates only and have a varying load date range, giving you the opportunity to see when exactly the corrections were made.
answered Feb 25, 2016 by srobijns (140 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