Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition

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

this section.

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

plans work.

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

program benefits.

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.

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.