Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition

220 Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition

The results are shown in Figure 5-45.

Figure 5-45 Results of FOR BUSINESS_TIME BETWEEN ... AND .. query

5.2.5 Bitemporal tables

It is possible to define temporal tables for use as both system-period and

application-period. These are known as

bitemporal tables. These are used in

cases where you have to capture both the

system time at which a data record is

entered or valid in the database system, and the

business time for which the

record is valid or in force. As a simple example, you might want to track the time

period that customer loyalty program terms are valid, and also want to keep track

of when changes to a customer’s loyalty program policy are made.

Creating the bitemporal table

As with system-period and application-period temporal tables, the easiest way to

create bitemporal tables is with InfoSphere Warehouse 10.1 Design Studio.

Simply select the schema that you want in the Data Project Explorer, right-click

and choose the menu option Add Data Object  Bitemporal Table as shown in

Figure 5-46.

Figure 5-46 Creating a bitemporal table in Design Studio

This creates a skeleton table structure with predefined column definitions for both

system-period and application-period start and end times, as shown in

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

Figure 5-47. We added a data column and primary key column for completeness

and chose to restrict overlap, as shown.

Figure 5-47 Columns generated for bitemporal tables in Design Studio

The Design Studio also automatically creates the history table for the bitemporal

table with all of the appropriate columns included in the definition as shown in

Figure 5-48.

Figure 5-48 History table generated by Design Studio for bitemporal

As with pure system-period or application-period tables, all of the generated

column names can be renamed in Design Studio per the developer's

requirements or preferences.

Example 5-30 shows the SQL required to create these tables and designate

them to DB2 for bitemporal use.

Example 5-30 SQL to create a bitemporal table manually

CREATE TABLE "CSTINSIGHT"."Table2" (

"PK_COLUMN" INTEGER NOT NULL,

"DATA_COL" INTEGER,

"SYSTEM_START_TIME1" TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW

BEGIN ,

"SYSTEM_END_TIME1" TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END ,

"TRANS_ID1" TIMESTAMP(12) GENERATED ALWAYS AS TRANSACTION START ID ,

222 Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition

"APPLICATION_START_TIME1" TIMESTAMP NOT NULL,

"APPLICATION_END_TIME1" TIMESTAMP NOT NULL,

PERIOD SYSTEM_TIME("SYSTEM_START_TIME1", "SYSTEM_END_TIME1"),

PERIOD BUSINESS_TIME("APPLICATION_START_TIME1", "APPLICATION_END_TIME1")

)

DATA CAPTURE NONE

COMPRESS NO;

CREATE TABLE "CSTINSIGHT"."Table2_HIST1" (

"PK_COLUMN" INTEGER NOT NULL,

"DATA_COL" INTEGER,

"SYSTEM_START_TIME1" TIMESTAMP(12) NOT NULL,

"SYSTEM_END_TIME1" TIMESTAMP(12) NOT NULL,

"TRANS_ID1" TIMESTAMP(12),

"APPLICATION_START_TIME1" TIMESTAMP NOT NULL,

"APPLICATION_END_TIME1" TIMESTAMP NOT NULL

)

DATA CAPTURE NONE

COMPRESS NO;

ALTER TABLE "CSTINSIGHT"."Table2" ADD VERSIONING USE HISTORY TABLE

"CSTINSIGHT"."Table2_HIST1";

ALTER TABLE "CSTINSIGHT"."Table2" ADD CONSTRAINT "Table2_PK" PRIMARY KEY

("PK_COLUMN", BUSINESS_TIME WITHOUT OVERLAPS);

Using bitemporal tables

In this section we provide examples of using bitemporal tables in a DB2 and

InfoSphere Warehouse 10.1 solution. Here we have created a table for

bitemporal analysis in our customer loyalty program scenario. Figure 5-49 shows

a summary of the column information.

Figure 5-49 Bitemporal table columns for CUST_LOYALTY

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

Figure 5-50 shows the history table for this bitemporal table. As expected, the

layout of the HISTORY table is identical to the base bitemporal table.

Figure 5-50 Bitemporal HISTORY table for CUST_LOYALTY

If we run a few simple INSERT and UPDATE statements on our bitemporal

CUST_LOYALTY table as shown in Example 5-31, we can view the impact on the

base and HISTORY tables. We see that we INSERT four new customer records,

then UPDATE one of the records (Linda MM) to give her the promotional bonus

rate for 30 days.

Example 5-31 SQL to populate the bitemporal CUST_LOYALTY table

insert into cstinsight.cust_loyalty ("Customer", "Loyalty_Scheme", "Bonus_Pct",

"Effective_Start_Date", "Effective_End_Date")

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

'9999-12-30');

insert into cstinsight.cust_loyalty ("Customer", "Loyalty_Scheme", "Bonus_Pct",

"Effective_Start_Date", "Effective_End_Date")

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

insert into cstinsight.cust_loyalty ("Customer", "Loyalty_Scheme", "Bonus_Pct",

"Effective_Start_Date", "Effective_End_Date")

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

insert into cstinsight.cust_loyalty ("Customer", "Loyalty_Scheme", "Bonus_Pct",

"Effective_Start_Date", "Effective_End_Date")

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

'9999-12-30');

UPDATE "CSTINSIGHT"."CUST_LOYALTY"

FOR PORTION OF BUSINESS_TIME FROM

'2012-08-01' TO DATE('2012-08-01') + 30 DAYS

SET "Bonus_Pct" = 200