settingsLogin | Registersettings
Show Menu

Type 2 Dimension via Data Vault

+3 votes
The DV methodology brings many benefits. However, the last step to create the dimensional models has us confused. The general advice provided is to use the (Hubs & Sats) to create the dimensions and (Links) for facts. However, when the dimension is type 2, the surrogate key isn't unique (Dimension ID) and therefore you have to use the surrogate key and dates to join the fact (Link) to the dimension (hub + Satelliete). Any assistance, would be greatly appreciated.
asked May 18, 2015 in Information Marts by Cookie Monster
recategorized May 19, 2015 by molschimke

2 Answers

+1 vote
Hi Cookie Monster,

you can use hubs + satellites to populate Type 1 dimensions easily (use the PK of the Hub as dimension key).

For Type 2 dimensions, the easiest is to use the PK of a PIT table. Use PIT + Satellites for this purpose.

Hope that helps,

answered May 19, 2015 by molschimke (1,890 points)
+3 votes
Actually, I would not recommend using the PK's stored in the Hubs or the Links as Dimension and fact fillers.  I would actually recommend you create your own Sequence numbers (new ones) for each dimension and each fact.

You do have to use the keys for the joins - that is true, but not for the output.  You should be matching by Business Key for a type 2 dimension, then running a delta against the dimension.

With regard to the actual time-line, you need to decide (by user rules) which temporality / which time line to use.  In other words, there may be more than just the original LOAD and LOAD END date available.  There may be an estimated date, or an actual date, or a projected date, or a booked-date, or some other business date.  In reality, your queries should focus on using BUSINESS dates for both query alignment (sorting and joining), AND for delta processing on the way to the type 2 dimension.

Hope this helps,

Dan Linstedt
answered Sep 14, 2015 by dlinstedt (400 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