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
Only Satellites or Links Satellites that refer to the same Hub or Link should be added as Relationships.
-
- Create a Dataflow Set
- Add a Model Object to the Dataflow Set: Add all the concerned Satellites or Links
All the Satellites/Link Satellites should refer to the same Hub/Link.
-
- 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
- Map manually: for each Satellite or Link Satellite, map:
Properties
A PIT Model Object can be configured through the following Properties:
-
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}