Create a PIT

A Model Object of type PIT can be created in the Raw Vault Layer.

What is a Point In Time table

In Data Vault modeling, a Point In Time table (or PIT table) is connected to a Hub or a Link and has a similar structure as a Satellite.

It stores all distinct load dates of all attached satellites.

It helps you load data from your Data Vault into the Data Mart, e.g., into a dimension table or view.

Example:

Creation steps

There are two possibilities to create a PIT Model Object:

• Create a Model Object with the Wizard:
  • Recommended possibility
  • Use the Wizard Create PIT from a Raw Vault Model Object that contains the following steps:
    • Create Model Object
    • Create Relations: number = {2}
    • Create Dataflow
    • Add to View
  • Map manually: for each Satellite or Link Satellite, map:
    • The Target Term XXX_BG_ValidFromTimestamp to the Source Term BG_ValidFromTimestamp
    • The Target Term XXX_HK to the Source Term XXX_HK

• Create a Model Object manually:
  • Create a Model Object of type PIT in the Silver Layer
  • Add a Relationship to all the concerned Satellites or Links

• • Create a Dataflow Set
  • Add a Model Object to the Dataflow Set: Add all the concerned Satellites or Links

• • Map manually: for each Satellite or Link Satellite, map:
    • The Target Term XXX_BG_ValidFromTimestamp to the Source Term BG_ValidFromTimestamp
    • The Target Term XXX_HK to the Source Term XXX_HK

Properties

A PIT Model Object can be configured through the following Properties: 

• Implementation Type

• Optimization Method (Only for Spark Generator)

• Optimization Method Columns (Only for Spark Generator)
• File format (Only for Spark Generator)

Default Terms

A PIT Model Object will include the following Default Terms:  

• BG_LoadTimestamp
• BG_ValidFromTimestamp
• BG_ValidToTimestamp
• PIT_HK
• BG_DataflowName
• BG_ID
• BG_IncrementalFilter

Business Rules

The following Business Rules are checked during the Model Object creation:

• Dataflows amount: min = {1}, max = {1}
• Dataflow Sets amount: min = {1}, max = {null}
• Dataflow Set Model Objects amount: min = {1}, max = {null}
• Relationships amount: min = {1}, max = {null}
• Terms amount: exclude Default Terms = {true}, exclude Business Keys = {true}, exclude Identities = {false}, exclude Foreign Keys = {false}, exclude unmapped Terms = {false}, min = {null}, max = {0}
• Supported Term Data Types: exclude Default Terms = {true}, exclude Business Keys = {false}, exclude Identities = {false}, exclude Foreign Keys = {false}, exclude unmapped Terms = {false}
• Identity Terms amount: min = {1}, max = {1}
• Identity Terms not nullable
• Supported Implementation Types: supported Implementation Types = {Cached, Permanent}