Modeling a dependent child

Hello,

A) a dep-key in the link denotes that the dep-key is a part of the relationship forever, like a code or degenerate dimension; this relationship (dept + vendor + brand) will never have the same dep-key between them. Likely the offshoot to describe that dep-key is a ref table.
B) a dep-key in the link does not participate in the generation of the link hash key
C) if the attributes associated with the dep-key change then would this be better to include that dep-key in a link-satellite as a dep-key to that parent link-hash-key? Advantage of this approach is that you reduce the number of joins needed to get the data out and the resolution to which applicable dep-key attributes are appropriate to the recorded relationship at that point in time is resolved. Less complex SQL to get that data out. This of course means the data from source is already embedded with that ref-content! If it is seperate then it should be loaded separately and only resolved upon query time.

Am reading the diagram right? You have generated an Effectivity Satellite (SATE)?
Where did BEGIN and END dates under SATE come from?

  • if it is from source then the satellite is a normal Link-Sat, you may choose to split the satellite between the effectivity columns and the other attributes if you like but that just means you’ll have to resolve them at query time should the marts/reports need them (adds complexity that might not be necessary)
  • if you have derived those effectivity columns then yes this should be an effectivity satellite where we only care about tracking that movement, all other attributes describing that relationship goes to its own link-satellite

Try to represent the Business Process / Value Stream Stage in your model, but also keep in mind not to make the interpretation of the model too complex that you have to do mental gymnastics to try and get it out!
Effectivity Satellites constructed by deriving the relationship against a driving key is very complex; use it if you really need it, I’d rate that as one of the most complex builds and queries in a Data Vault model. :slight_smile: Definite examples in the book!

3 Likes