Data Governance Blog

Key Date-Time Stamps and Why They Matter

Have you ever considered how important the mundane time-stamp is to your BI operations? Think about it, every report includes the element of time in it. The report is a snapshot in time, or covers a range of time. Accurate and timely business intelligence requires good records of the history within your business processes.

The problem we all run into is that the source transaction systems do not apply a great deal of effort recording events. Those systems are typically not very interested in the history of changing elements. For the most part they are designed to manage the user experience in the present. The only history they maintain would be to support that experience. Great for the user, but it falls way short in meeting the needs of the BI teams.

The BI team is not just concerned about what the customer is doing now, but also what the customer has done in the past. It is through the examination of that history that they can make predictions about future behavior. That ability to accurately predict enables businesses to manage risk, drives key decisions, and provide a competitive advantage.

That is where we run into the problem with source applications. While they capture the time-stamps needed for the immediate interaction with users and customers, they lack the time-stamps needed to fully unleash the power of predictive analytics. Too many facts become ambiguous because the precise time and day they occurred is lost since their capture was not considered important to the source system function. Unfortunately, most business requirements for the source only gave reporting and analytics a scant paragraph in the document.

When implementing data governance programs and standards, it is critical to not overlook requirements for time-stamps. With that in mind here are a few requirements to consider:

1. All date-time stamps must reference either UTC or GMT. Time is relative and finding out you cannot track your process across time-zones because the source app did not capture the GMT offset is tough after the roll-out.
2. Any time a relationship to reference data changes, capture the date-time. Being able to track customers, products, and other relationship changes is invaluable to the analytics team.
3. All date-time stamps need to be captured as close to the source as possible. OK we have some old apps out there. So if you cannot capture the time-stamp in the app, the do it as part of the ETL. The sooner you can associate data with an event, the better the odds of being analyze the events.
4. Use slowly changing dimension for key data. Look at what MDM associations are key to your business or process, and convert them to SCD’s in the data warehouse.  While less than ideal, it will provide enough bread crumbs to improve your analytics.

Others may have suggestions that fit your business. Use them to augment the list and include them as requirements in your own standards.