In this post, I will describe the temporal mechanism that was developed in Postgres as a part of an open-source enterprise system that has inherent data exchange capabilities.
Temporal data refers to master data that changes over time. A practical example is payroll parameters, where an employee's pay rate may change over different periods. Storing this data as temporal, enables processing of prior period adjustments. For instance, if we discover that an employee worked more hours in the previous month but had a pay rate adjustment in the current month, we need to modify the hours for the previous month and reprocess it based on the pay rate from last month.
To facilitate the management of temporal data in a consistent manner, the platform includes a procedure that will automatically determine whether to update an existing segment, insert a new segment, or merge two segments together based on the temporal update it is provided.
When an enterprise system has temporal data setup with this mechanism, we gain significant abilities for reporting and AI as a result of being able to show how the entire database changed over time. Furthermore, users can simply change a query date and the system will return all data to them as if it were that date.
The temporal functionality works hand in hand with the change history functionality. Whenever temporal data changes, the change history function captures the updates and presents them to the user in a meaningful way. Additionally, the temporal functionality seamlessly integrates with the data exchange capability, ensuring that temporal updates are appropriately disseminated to subscribed systems.
Please check out www.3d-ess.com/videos and “020.060 Temporal” where I discuss the rationale for temporal, a procedure that will automatically normalizes temporal data and why it is important to understand temporal resolutions. You can also view the source code at https://github.com/bkjenner/3dEnterpriseSystems/blob/main/500%20Procedures/spsysTemporalDataUpdate.sql.
I encourage you to share your thoughts, questions, and likes.
Thank you for your attention.
Comments