Introduction to the DB2 Data Warehouse Edition Tutorial

You will design and deploy a business intelligence solution that extends the power of the DB2^® data warehouse for a fictional company named JK Superstore.

Business scenario

JK Superstore is a retail enterprise with a department store chain that carries product lines of clothing, shoes, beauty, home furnishings, and electronics. The company has grown steadily for the last several years and wants to make sure that it can maintain its profitability as it continues to grow and expand into new markets.

The JK Superstore data warehousing team wants to implement a new data warehouse to consolidate the company's data in a DB2 database so that the company can have a consistent data source for all of its analysis and reporting needs. A data architect in the JK Superstore technical team designed the data warehouse with a data mart for analysis. The data warehouse is defined in the DWH schema, and the data mart is in the MARTS schema.

The DWH schema contains the transactional data for the JK Superstore retail chain. Figure 1 shows the physical data model of the DWH schema.

Figure 1. Physical data model of the DWH schema

The following table describes the nine tables that are in the physical model:

Table 1. Description of the tables in the physical data model of the DWH schema
Physical table name	Expanded table name	Description
ITM_TXN	Item transaction	The item transaction table contains individual transactions that record the transfer of a single product item (or multiple identical product items) from JK Superstore to a customer, or vice versa. For example, one transaction can represent a single barcode scan at a checkout or a refund for a returned product.
MKT_BSKT_TXN	Market basket transaction	The market basket transaction table groups the individual transactions that occur together as one event. For example, one market basket transaction can represent the collection of items purchased by a single customer at one time.
OU	Organization unit	The organization unit table contains the individual stores that are part of the JK Superstore retail chain.
PD	Product	The product table identifies goods and services that can be offered or sold by the JK Superstore.
PD_X_GRP	Products by group	The products by group table defines the relationship between a product and a product grouping. Products might be listed multiple times if they belong to more than one product group or if they are listed in historical product groupings that are still maintained.
GRP	Group	The group table identifies a specific grouping of products that is of interest to JK Superstore. A group can be composed of other groups. A group can be created for marketing, management, control, or reporting purposes.
IP	Involved party	The involved party table contains people that are related to the business activities of JK Superstore, such as store managers and distribution contacts.
MSR_PRD	Measurement period	The measurement period table records the intervals of time at which measurements are captured in the warehouse.
CL	Customer	The customer table contains anonymous customer IDs. For the purposes of this tutorial, these IDs are necessary for mining.

The MARTS schema contains aggregated data for the JK Superstore retail chain that is needed to analyze product sales and pricing. Figure 2 shows the physical data model of the MARTS schema.

Figure 2. Physical data model of the MARTS schema

The following table describes the four tables that are in the physical model of the MARTS schema:

Table 2. Description of the tables in the physical data model of the DWH schema
Physical table name	Expanded table name	Description
PRCHS_PRFL_ANLYSIS	Purchase profile analysis	The purchase profile analysis table is the fact table. It contains sales metrics for products and market baskets that are purchased by customers.
STORE	Store	The store table is a dimension table. It corresponds to the organization unit table in the data warehouse.
TIME	Time	The time table is a dimension table. It corresponds to the measurement period table in the data warehouse.
PRODUCT	Product	The product table is a dimension table. It corresponds to the product table in the data warehouse.

This tutorial shows you how to use the main features of DWE to implement an end-to-end business intelligence solution for JK Superstore.

Learning objectives

The tutorial has the following learning objectives:

Use the Design Studio to design and test your solution
Use the Administration Console to deploy and administer your solution
Update your physical schema design
Populate the fact table of a new data mart by using SQL-based data flows and control flows
Design a complete cube model and deploy performance-enhancing materialized query tables (MQTs)
Design and score a mining model
Build Alphablox reports based on OLAP metadata and mining models

Time required

The complete tutorial should take approximately eight hours to finish.

However, you can choose to do specific modules individually rather than the entire tutorial. Most of the modules take approximately 60 - 90 minutes each to complete. The following table shows the time that is required to complete each module.

Table 3. Time required to complete each module
Module	Time required
Optional: Introduction to the Design Studio	20 minutes
Module 1: Designing the physical data model for your data warehouse	60 minutes
Module 2: Designing warehouse building applications	90 minutes
Module 3: Deploying and running warehouse building applications	60 minutes
Module 4: Designing OLAP metadata	60 minutes
Module 5: Building DB2 Alphablox reports based on OLAP cubes	60 minutes
Module 6: Creating a mining model	60 minutes
Module 7: Building DB2 Alphablox reports based on a mining model	45 minutes

You can start this tutorial from the optional introduction module, Module 1, Module 2, or Module 4. To start from Module 2 or Module 4, complete the optional Start the tutorial here lesson at the beginning of those modules. You can also skip all of the lessons in Module 6 by completing a shortcut lesson at the beginning of the module.

If you want to see the results of most of the tutorial lessons, you can open the completed sample projects in the Design Studio. From the File menu, select New > Example... > Data Warehousing Examples and complete the wizard. You can also access this wizard directly from the Design Studio Welcome page.

Skill level: Moderate

This tutorial assumes some conceptual knowledge of data warehousing and business intelligence, but does not assume any knowledge of the DWE-specific implementation of those concepts.

Audience

This tutorial describes how to complete the tasks that are related to designing, deploying, and maintaining a physical database schema, SQL warehousing flows, OLAP metadata and summary tables, mining models, and Alphablox reports. Many enterprises divide the designing and administration tasks and the domain areas (SQL warehousing, OLAP, mining, reporting) among multiple people. Not all lessons in this tutorial might apply to you. However, each lesson should be applicable to someone in your team.

System requirements

To complete this tutorial from end to end, you must have the following components of DB2 Data Warehouse Enterprise Edition installed on one or more systems. If you have the DB2 Data Warehouse Base Edition installed instead of the Enterprise Edition, you can only complete the following modules of the tutorial:

Module 1: Designing the physical data model for your data warehouse
Module 4: Designing OLAP metadata, not including Lesson 6

The tutorial setup scripts are designed to be run on a Windows^® computer. Make sure that you use a Windows server when you are working through the tutorial, even if you intend to use a UNIX^® or Linux^® platform for your test and production systems.

Server components

DB2 UDB Enterprise Server Edition, Version 8.2, FixPak 3 (also known as Version 8.1, FixPak 10)
DWE Intelligent Miner™
WebSphere^® Application Server
DB2 Cube Views™
DB2 Alphablox
DWE Administration Console

Client components

DB2 Administration Client
DWE Design Studio
- Intelligent Miner plugins
- SQL Warehousing plugins
- OLAP plugins
DWE Intelligent Miner Visualization

Documentation

DWE Samples and Tutorial

Prerequisites

Before you begin the tutorial, you must complete the following steps:

Make sure that you have SYSADM authority on the DB2 database server that you will be using for this tutorial. You need this authority to create and modify the sample database that you will work with. Throughout this tutorial, the db2admin user ID is assumed to have SYSADM authority.
If you are working with a database server on a remote client, catalog the database server using the DB2 Configuration Assistant.
Create the starter version of the DWESAMP database by opening a DB2 Command Window and running the \DWE\samples\data\setupdwesamp.bat script that is inside your DWE installation directory. For detailed information about what the script does, see the \DWE\samples\data\Readme.txt file.

Expected results

If you finish the entire tutorial, you will have a complete working DB2 data warehouse that is optimized for analysis. You will also have two web-based reports.

Each module lists the expected results so that you can track your progress after each module.

Modules in this tutorial

Optional: Introduction to the Design Studio
You will use the Design Studio for many of the lessons in this tutorial. By taking a few minutes to become more familiar with the Design Studio, you will be more productive and able to complete the tasks in this tutorial more quickly.
Module 1: Designing the physical data model for your data warehouse
In this module, you use the Design Studio to import the existing physical data model for the new JK Superstore data warehouse and marts and complete the design of the MARTS schema. You also update the DWESAMP database with the MARTS schema changes.
Module 2: Designing applications to build a data warehouse
In this module, you use the Design Studio to design and run SQL-based data flows that help populate and maintain data warehouse tables with the data that is necessary for meaningful JK Superstore analytics.
Module 3: Deploying and running applications that build warehouses
In this module, you embed data flows inside control flows and deploy data warehouse applications to schedule and run in the WebSphere Application Server environment. You also schedule and manage these applications by using the DWE Administration Console.
Module 4: Designing OLAP metadata
In this module, you create OLAP metadata that describes your data in a multidimensional model. The OLAP metadata can also be used to create recommendations for materialized query tables (MQTs), which contain preaggregated data. In DWE these MQTs provide improved performance of queries that are based on the same cube model.
Module 5: Building DB2 Alphablox reports based on OLAP cubes
In this module, you use DB2 Alphablox to create an Alphablox cube based on the Cube Views metadata that you created in the previous module. You also deploy a preconfigured application on a WebSphere server and build and work with reports.
Module 6: Creating a mining model
In this module, you create a mining model to analyze customer purchase trends for JK Superstore.
Module 7: Building DB2 Alphablox reports invoking the data mining model
In this module, you use DB2 Alphablox FastForward templates to display data in interactive reporting views that invokes the data mining model from the last module. You will also learn some basic concepts about the DB2 Alphablox relational reporting model and work with DB2 Alphablox FastForward templates that display data in interactive reporting views that invoke the data mining model.