Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition

Chapter 5. Temporal data management and analytics in an operational warehouse 225

WHERE "Customer" = 'Linda MM'

The results are shown in Figure 5-53. We can see that Linda MM is under the

terms of the 200% bonus point promotion during the first week of August, 2012.

Figure 5-53 Results of the business-time query against the bitemporal table

A second kind of question involves the transaction time of activity in the database

relating to the customer loyalty program. For example, a customer service

representative might use the operational data warehouse to answer the question:

“How many changes have been made to the customer loyalty program for

customer Linda MM in the past year?”

That query is expressed as shown in Example 5-33.

Example 5-33 Querying the bitemporal table using system-time

SELECT * FROM cstinsight.cust_loyalty

FOR SYSTEM_TIME FROM CURRENT TIMESTAMP - 1 YEAR TO CURRENT TIMESTAMP

WHERE "Customer" = 'Linda MM'

ORDER BY SYSTEM_START_TIME;

Figure 5-54 shows the results. We can see that Linda MM’s record was updated

four separate times, including the transaction that created it, over the past year.

Figure 5-54 Results of the system-time query against the bitemporal table

There are many such examples of how bitemporal tables can be utilized. The

power of the bitemporal table is the ability to ask both system-oriented and

business-oriented questions of the same table in a data warehouse solution

using InfoSphere Warehouse 10.1.

5.2.6 Views and temporal tables

InfoSphere Warehouse 10.1 and DB2 10.1 allow views to be created using both

system-period and application-period temporal tables. Generally, they can be