202 Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition
5.2.3 Temporal data management with business time
Business time refers to the time, either a simple date or down to the granularity of
a time stamp, that has particular meaning in a business context. The date on
which a legal contract goes into effect or the date that a sale coupon expires are
both examples of business time.
Business time might or might not be the same as the corresponding system time
for an event, and the two can be completely unrelated. For example, a
change-of-address card received by the post office might be entered into the
system on June 1 (system time); however, the actual change of address might
not go into effect until July 1 (business time).
Many business applications, queries, and activities are most interested in
business time handling. Generally speaking, business processes and
applications require the ability to identify the time period during which some
condition is valid or effective. They need the business start and business end
dates (or dates and times).
To support the business logic that goes with managing the effective business
start and end times, InfoSphere Warehouse 10.1 (and DB2 10.1) have been
enhanced with new features to do just that. We examine these new features in
To help illustrate the new features, we again use part of the database schema for
the Customer Insight Warehouse Pack. In this case, we use the tables
associated with tracking the involvement of a customer in a loyalty program.
Many retailers and other businesses use loyalty programs to retain and incent
their customers by awarding them with points for each purchase. These points
can be exchanged directly for goods or may go towards a discount or some other
advantage. Airline frequent flyer programs are typical examples of how these
In our sample database, as in many actual programs, there are different grades
or levels that offer increasing amounts of reward for customers who have the
most purchases. Thus, “better” customers are rewarded with better loyalty
Note: There is a script ~/sqllib/samples/clp/temporal_revert.db2 that
creates a stored procedure called REVERT_TABLE_SYSTEM_TIME. This
stored procedure provides a general technique to revert any row or rows in a
base table to any rows in the history based on a TIMESTAMP value. Details of
the script usage are in the comment section of the script.
Chapter 5. Temporal data management and analytics in an operational warehouse 203
In our sample database, we have three levels of loyalty program with the
following benefits for each:
No Plan: At this lowest level are customers who are not enrolled in any loyalty
plan and therefore, have no plan benefits.
Loyalty Club: The lowest plan level. Members earn a point for every dollar
spent and can exchange points for vouchers that can be used to purchase
goods or services offered by our company.
Gold Club: The highest plan level. Members earn points for every dollar
spent with an additional 50% bonus points. They redeem those points in the
same way as the Loyalty Club level.
A great deal of interesting activity and analysis can take place in the context of
the operational data warehouse when we use the business time data
management and query features in InfoSphere Warehouse 10.1.
For the examples that follow, consider the simple table of information shown in
Figure 5-24 Customer Loyalty Program information
Even with this simple table of information, we see how we can perform significant
and useful analysis in a data warehousing environment. For example, we can
perform the following analysis:
Track customer loyalty points and determine how many they have redeemed
in a given time period.
Compare purchasing behaviors of customers in the loyalty programs with
those not enrolled.
Compare the costs incurred by the loyalty program for both administration
and point redemption with the increased revenue attributed to the program.
If we add business time and associated logic to Figure 5-24, we can perform
many other useful activities related to the loyalty program such as:
Compare a single customer’s behavior during periods when the customer is
enrolled or not enrolled in a loyalty program.