Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition

210 Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition

5.2.4 Application-period temporal tables

When the application-period temporal tables have been defined and deployed to

DB2 using InfoSphere Warehouse 10.1, we can examine how to manipulate and

query these tables. We continue to use our simple example table tracking our

customers’ use of our loyalty program. We begin by looking at the table insert

and then move onto other, more interesting, actions.

Inserting data into an application-period temporal table

Different from the system-period temporal tables discussed previously in this

chapter, row insertion into an application-period temporal table requires that the

start and end time values be provided. Because these only have meaning in the

business context and not the database context, DB2 cannot simply generate

these values. They must be provided by the application or person inserting the

data.

Recall the simple example table of customers and their enrollment status in

various levels of the customer loyalty programs, shown here in Figure 5-31 for

your convenience.

Figure 5-31 Sample Customer Loyalty program table with temporal columns

These records were inserted into the table using the SQL INSERT statements

displayed in Example 5-17.

Example 5-17 INSERT statements to populate an application-period temporal table

insert into cstinsight.cust_loyalty

values('Albert Johnson', 'Customer Loyalty Club', 100, '2011-09-15',

'9999-12-30');

insert into cstinsight.cust_loyalty

values('Joe Smith', 'No Loyalty Scheme', 0, '2012-06-22', '9999-12-30');

insert into cstinsight.cust_loyalty

values('Linda MM', 'Customer Gold Club', 150, '2012-03-12', '9999-12-30');

insert into cstinsight.cust_loyalty

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

values('Melissa Smith', 'Customer Loyalty Club', 100, '2011-11-01',

'9999-12-30');

Inserting and managing multiple time periods

To be really useful, the application-period temporal table can manage multiple

rows for the same customer covering different time periods. This allows us, for

example, to maintain a history for each customer of which plans they participated

in and for what time periods. Having this information available in our data

warehouse allows us to perform important analysis of the impact of these plans

on both the cost and benefit sides of the equation to determine the effectiveness

of the plans at driving increased revenue and retaining good customers.

Simple inserts into application-period temporal tables

The simplest form of INSERT is for the user or the application to provide the

records and dates for each time period to be captured. For example, here we add

a new customer named Jane Doe. Jane became a customer effective May 20,

2012 but did not immediately join a loyalty program. Starting July 1, 2012, she

enrolled in the basic loyalty program. To insert the full history into our

CUST_LOYALTY table using SQL INSERT, we use the statements in

Example 5-18.

Example 5-18 INSERT for multiple periods for the same customer without overlap

insert into cstinsight.cust_loyalty

values('Jane Doe', 'No Loyalty Scheme', 0, '2012-05-20', '2012-07-01');

insert into cstinsight.cust_loyalty

values('Jane Doe', 'Customer Loyalty Club', 100, '2012-07-01',

'9999-12-30');

If we then look at the table contents as shown in Figure 5-32, we can clearly see

the history of Jane Doe’s involvement in the loyalty program.

Figure 5-32 CUST_LOYALTY table after inserting multiple rows for Jane Doe

Note the end date of her first record is July 1, 2012”, and the start date of her

second record is also July 1, 2012. Keep in mind the discussion from the