 | Business scenario
This tutorial series will step you through the process of designing
and deploying a simple Informix data warehouse for a fictional Retail
company Sports Stores that uses the well-known Informix sample
OLTP database stores_demo as its source operational system. The
desired warehouse repository is a Data Mart for Sales and Cost
Analysis that will be stored in the newly created OLAP
database sales_demo.
To review the steps to prepare these two databases for the hands-on
exercises in this tutorial, please refer to the
"System requirements" section.
Informix Warehouse can help Sports Stores by providing
graphical tools for modeling the data mart tables in the
sales_demo database and loading its tables with combined
and transformed data extracted from the existing operational data
sources (in this case, the stores_demo database — also
on Informix — and a few plain-text files).
The existing OLTP source
database: The stores_demo database
The stores_demo schema contains the transactional data for the
Sports Stores retail. At the beginning of this tutorial,
the "System requirements" section
describes how to create and populate this database.
Figure 4 shows the logical data model of the
stores_demo schema. For more information about this OLTP
database, you can consult the "IBM Informix Guide to SQL: Reference –
Appendix A. The stores_demo Database" in the IDS V11.50 Information
Center (see Resources).
Figure 4. Logical
data model of the operational database stores_demo
(Click here to see a larger image of
Figure 4.)
Table 2 describes the nine tables that are in the physical model:
Table 2. Description of the tables
in the physical data model of the stores_demo schema
| Physical table name | Description |
|---|
| customer | Contains information about the retail stores' customers that
place orders from the distributor. | | orders | Contains information about orders placed by the customers of
the distributor. | | items | An order can include one or more items. One row exists in the
items table for each item in an order. | | stock | The distributor carries 41 types of sporting goods from
various manufacturers. More than one manufacturer can supply
an item. | | catalog | Describes each item in stock. Retail stores use this table
when placing orders with the distributor. | | cust_calls | All customer calls for information on orders, shipments, or
complaints are logged. The cust_calls table contains
information about these types of customer calls. | | call_type | The call codes associated with customer calls are stored in
the call_type table. | | manufact | Stores information about the nine manufacturers whose sporting
goods are handled by the distributor. | | state | Contains the names and postal abbreviations for the 50 states
of the United States. |
The new OLAP target database:
The sales_demo database
The data mart database sales_demo will contain the star schema
and the aggregated data for the Sports Store retail chain that
is required by BI analysts for sales and cost analysis. Again, the
"System requirements" at the beginning
of this tutorial describes how to create an empty sales_demo
database. Figure 5 shows the intended logical
data model of the sales_demo schema, the OLAP database for your
sample data mart. Notice the star-form created by the dimension tables
around the fact table:
Figure 5. Logical
data model of the sales_demo schema
Table 3 describes the dimension tables and fact table in the physical
data model of the sales_demo schema:
Table 3. Description of the tables
in the physical data model of the sales_demo schema
| Physical table name | Description |
|---|
| time | Dimension table. It contains time information about the
sale. | | customer | Dimension table. It contains information about sales
customers. | | product | Dimension table. It contains information about the products
sold through the data warehouse. | | geography | Dimension table. It contains information about the sales
district and region. | | sales | Fact table. It contains information about product sales and
has a pointer to each dimension table. |
This tutorial uses Informix Warehouse's Design Studio to design the
sales_demo database from scratch. You can use the SQL
script code in Listing 1 as guidance:
Listing 1. Sample SQL script code intended for sales_demo database
-- The following statements create the tables for the sales_demo database:
create table geography (
district_code serial,
district_name char(15),
state_code char(2),
state_name char(18),
region smallint);
create table product (
product_code integer,
product_name char(31),
vendor_code char(3),
vendor_name char(15),
product_line_code smallint,
product_line_name char(15));
create table customer (
customer_code integer,
customer_name char(31),
company_name char(20));
create table time (
time_code int,
order_date date,
month_code smallint,
month_name char(10),
quarter_code smallint,
quarter_name char(10),
year integer );
create table sales (
customer_code integer,
district_code smallint,
time_code integer,
product_code integer,
units_sold smallint,
revenue money (8,2),
cost money (8,2),
net_profit money(8,2));
|
In real-life scenarios you may need to use a combination of templates
and reverse engineering to aid in the process of creating the data
model of a large data warehouse.
For more information about this database, you can consult the "IBM
Informix Guide to SQL: Reference – Appendix B. The sales_demo and
superstores_demo Databases" in the IDS V11.50 Information Center (see
Resources).
Based on this imaginary business scenario, this tutorial series will
use the different tools in the Informix Warehouse Feature to design
and deploy a platform for a data warehousing solution for the retail
company Sports Stores.
In the following sections of this tutorial, see how to start using
Design Studio (the client tool of the Informix Warehouse) to create a
new Eclipse-based workspace. Then, define and test the database
connections to both the existing operational sources you later want to
extract data from (in our case, just stores_demo, on Informix)
and the new datamart you want to design and populate (the currently
empty Informix database sales_demo). After this first setup,
you will use the information provided in this "Business scenario"
section to guide the creation of the tables in the sales_demo
schema and complete the data model of this new OLAP system. In order
to produce the data model for the existing stores_demo OLTP
system, you will use reverse engineering to discover the tables and
relationships, so Design Studio can automatically generate this model
from the Informix catalog information about the stores_demo
database.
| |
