Kinds of Time (Modern Data Warehousing, Part 2)

This article is the second part in an ongoing series on modern data warehousing using data vault. The first part on data warehouse layers can be found here.

What Time Is It?

The original data warehouse definition, going back to Bill Inmon, states that a data warehouse is (among other things) a “time-variant, nonvolatile collection of data”. But what do these requirements mean in practice?

Time-variant means that the data changes over time. But on which timeline?

• Are we trying to use the actual business change time, the time attribute values changed in real life? Is that time stored somewhere so that we can load it into the data warehouse? Do we even notice (or care about) all actual changes?
• Are we trying to use the source system change entry time? Does the source system store that time? How often do we extract data from that system? Can we miss relevant changes? Do we even notice (or care about) all actual changes?
• Are we trying to use the source system data extraction time? How often do we extract data from that system? Can we miss relevant changes or extract multiple changes to the same instance at the same time? How much time can pass between an actual business change and the time of the source system data extraction?
• Are we trying to use the data warehouse load time? How often do we actually load data? Can we miss relevant changes or load multiple changes to the same instance at the same time? How much time can pass between an actual business change and the data warehouse load time? How meaningful is this technical timeline for the users of the data warehouse?

In many data warehousing projects, no explicit decision concerning the timeline(s) used for historization is made. This can lead to confusion and problems later on.

In addition, the inherent conflict between time-variance and non-volatility is often overlooked. What about data that arrives later than expected for some reason? What if corrections to attribute values or to the timeline itself have to be made later?

If we are historizing on a business change timeline, we might have to destructively update our data to incorporate these corrections, violating the non-volatility requirement and making it impossible to recreate past reports from the data warehouse.

Relevant Kinds of Time

Before we can pick the right historization approach for our data warehouse, we must be aware of the different kinds of time we can encounter.

We have to cut through the terminological confusion in the respective books and standards and understand which kinds of time are relevant to us. Only then can we decide along which timeline(s) to historize our data in both the core layer (for satellites) and the presentation layer of the data warehouse.

In general, there are three major time dimensions:

1. appearance time (when something happens in real life),
2. assertion time (when someone makes a statement about something) and
3. recording time (when something is stored in a database or other storage system).

While it is definitely worth trying to present data according to appearance time (the actual business change time) or at least an approximation thereof, it is not a good idea to historize the core layer by appearance time. In many cases, the appearance time is either not known at all or only becomes known a significant time after the fact. In the core layer, you should store the appearance time as an attribute if you can get it from somewhere but not use it to build your primary timeline.

Like appearance time, assertion times are often either not known at all or only become known a significant time after the fact. In many cases, they just aren’t that important for your data warehouse anyway. Store assertion times as attributes in the core layer if you can get hold of them but don’t use it to build your primary timeline. Unlike appearance time, assertion times usually aren’t used for presenting data either.

In contrast, recording times are of supreme importance in a data warehousing context:

• In many cases, users want to know what the source systems said at a certain point in time and therefore are very interested in the change entry time in the source system. However, source systems don’t always provide the change entry time and might manipulate it in undesirable ways, making it unsuitable as primary timeline for the core layer. Again, store it as an attribute in the core layer to be able to use it in the presentation layer if required.
• The source system data extraction time is the first kind of time that you can get with some reliability (because the extracts are delivered as files with creation timestamps or because the data warehouse takes care of the extraction itself). Again, store it as an attribute in the core layer. If you can’t get hold of any of the previous kinds of time, it is the best proxy of actual business change time you have. Still, because files might arrive or be loaded out of order, it is not the best choice as a primary timeline for the core layer.
• The only kind of time that is completely under your control is the data warehouse load time. Therefore, it should be used as the primary timeline for the core layer. It might be used in the presentation layer as well but usually, either other kinds of time are more interesting to the consumers of your data or the presentation layer isn’t historized anyway.

You can find more details about different kinds of time in this presentation. In the third part of the series, we’ll discuss different historization options according to the number of timelines used.