Data Vault Point in Time (PIT) Tables in DV 2.0 Standard: Importance, Definitions, and Functionality
Point in Time (PIT) tables in the business vault will greatly reduce the amount of compute time needed in the raw and business data vaults when it’s necessary to find the correct set of satellite records for a particular point in time. By implementing PIT tables, points in time are set in advance, eliminating the need for reporting users to find the most appropriately matching satellite record per business key per requested point in time. The PIT tables anticipate and figure out this information ahead of time, saving valuable manpower, time, and budget. In this whitepaper, you will learn about the journey of PIT tables, how to create them, and their importance as part of your data vault implementation.
The Performance Reporting Problem
When satellite record load dates don’t match a requested point-in-time, the closest
record value of each satellite table on the requested date is used. But this can involve significant compute time. That’s exacerbated when requests add more satellites, records, users, or changes over time, resulting in a significant drain on the data vault’s compute resources.
Using PIT Tables to Solve the Performance Problem
Performance can degrade quickly depending on the quantity of satellite data and number of concurrent users analyzing data in the data vault. Architecting and implementing PIT tables—preset points-in-time—in advance anticipates user query needs to improve compute performance.
Optimizing Data PIT Storage Without Sacrificing Performance
Significantly reduce the data footprint a PIT requires by making some modifications to
the PIT’s DDL, slight code changes while creating the data in the PIT, and a small modification when querying the PIT. Smaller PIT record sizes will equate to faster performance while not losing any PIT functionality.
We’re proud to help organizations thrive, and we’d love to tell you more.