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.
204 Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition
???? Analyze the effectiveness of allowing a customer to participate in a premium
loyalty program for a fixed period of time.
???? Allow for loyalty program terms that can vary over time, and correctly
calculate metrics related to the program such as accrued points and member
revenue contribution.
In InfoSphere Warehouse 10.1 (and DB2 10.1), the table is augmented with a
business start column and business end column that tracks the effective start
and end dates during which the information in the table record is valid or in force.
This is similar to the system start and end columns in the system-time temporal
tables described in previous sections. However, in the case of business time, we
are more interested in the dates in a business context as opposed to the moment
the data was entered or changed in the database. Also, with business time
temporal tables, we do not use a history table as in the case of system time
temporal data. All history is maintained in the single table unless the record is
fully deleted.
The augmented table with business time temporal handling is shown in
Figure 5-25 with newly added effective start and end dates. For our small
sample, we show four customers, most of whom are enrolled in one of the
programs.
The effective start date reflects the date on which their enrollment became active
(or simply the date they became a customer if not enrolled in a program). The
effective end date for all shown is December 30, 9999 or in effect, indefinite. This
means their current enrollment status is not set to expire at any given time in the
future.
Figure 5-25 CUST_LOYALTY table enhanced with business time columns
We now consider the details of how to implement and manipulate the business
time temporal tables in InfoSphere Warehouse 10.1.
Application-period terminology: Consistent with the ISO SQL 2011
terminology, the term
application-period is used to indicate temporal tables
based on business time as distinct from system-period temporal tables.
Chapter 5. Temporal data management and analytics in an operational warehouse 205
Creating an application-period temporal table
Similar to the system-period temporal tables, there are multiple techniques that
can be employed to prepare a table in DB2 for use as an application-period
temporal table. We consider the process in InfoSphere Warehouse Design
Studio first, then we look at doing it with SQL that has been coded manually.
Using Design Studio tooling
To create an application-period temporal table in InfoSphere Warehouse using
Design Studio, simply define the table as usual and then select Business time
period under the “Temporal attributes” heading in the table Properties tab as
shown in Figure 5-26.
Figure 5-26 Design Studio properties for application-period time temporal table definition
Notice several items in this figure:
???? The Effective_Start_Date and Effective_End_Date columns are automatically
generated by Design Studio. These are given tool-defined generic names,
and the designer or DBA can simply change them to names with more
meaning for the business context. These columns may either be DATE types
or TIMESTAMP types, depending on the application-specific requirements for
these fields and how they will be used.
???? The “Generated” flag is
not checked for application-period temporal tables.
Different from the system-period temporal feature discussed previously, DB2
has no way of auto-generating these values, because they are business
times and dates as opposed to database transaction times. The DBA or
application have to provide these values when data is inserted or updated.
This is what we expect for business time.
???? The “period” attribute for the application-period date columns defines the
Effective_Start_Date and Effective_End_Date as BUSINESS_TIME “begin”
and “end” columns respectively. This information is coded into the SQL DDL
to create the table (or alter an existing table).
206 Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition
???? The business period start and end columns are defined as “Not Null”, but no
default values are provided. The application that populates these tables (or
updates) must always provide an explicit value.
As with the system-period temporal tables, all of these attributes that specify the
details of the application-period temporal table are automatically generated by
Design Studio. They are used to generate the appropriate SQL DDL statements
to create or alter the table to make it support business time data management
and query logic.
Figure 5-27 Create an application-period temporal table in Design Studio with shortcut
Figure 5-28 displays the results.
Figure 5-28 Results of creating an application-period temporal table in Design Studio
Notice that Design Studio adds new business start time and business end time
columns and gives them the default names APPLICATION_START_TIME1 and
Tip: You can also simplify the creation of application-period temporal tables in
Design Studio by right-clicking the schema in the project explorer and
selecting Add Data Object Application-period Temporal Table. This
creates a skeleton table definition with the two application-period start and end
columns predefined.
This technique and results are shown in Figure 5-27 and Figure 5-28. Notice
the default column names for the start and end times. These can be changed.
Also, the data type for the columns can be changed from TIMESTAMP to
DATE if time-of-day granularity is not required for the business application.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.