What Are the Time and Place Dependencies of Product Sales?

< Day Day Up >

Identifying Relevant Information

To answer the question, What are the time and place dependencies of product sales?, the data warehousing team decided to design a report that reports sales across a time dimension and a geographic dimension. The following display shows an example of such a report.

click to expand
Display 4.7: Time and Place Dependencies for Sales

The next step is to identify how such a report could be created.

Identifying Sources

Further questioning of the executive team revealed that it would be helpful to track sales across a customer dimension and an internal organization dimension as well as across the dimensions of time and geography. Questions that require multiple dimensions to be analyzed together can often be answered with online analytical processing (OLAP). Accordingly, the data warehousing team concluded that the question, What are the time and place dependencies of product sales?, could be answered most efficiently with OLAP.

The data warehouse team examined existing tables to determine whether they could be used as inputs to an OLAP data store that would produce reports similar to the one shown in Display 4.7 on page 33. They identified a number of tables that could be used. These tables are described in the following sections.

Sources Related to Customers

The following tables can contribute to the customer dimension of the OLAP data store:

CUSTOMER table
CUSTOMER_TYPE table

Sources Related to Geography

The following tables can contribute to the geographic dimension of the OLAP data store:

CONTINENT table
COUNTRY table
STATE table
COUNTY table
CITY table
STREET_CODE table

Sources Related to Organization

The following tables can contribute to the organization dimension of the OLAP data store:

STAFF table
ORGANIZATION table

Sources Related to Time

The following tables can contribute to the time dimension of the OLAP data store:

CONTINENT table
COUNTRY table
STATE table
COUNTY table
CITY table
STREET_CODE table

While the previous tables contain the appropriate information, it is not in the correct format for OLAP. To support OLAP, a number of new data stores most be created, as described in the following section.

Identifying Targets

In order to support the OLAP reports such as the one shown in Display 4.7 on page 33, the data warehousing team specified the following new data stores.

A SAS cube that will support OLAP reporting.
A set of new tables that will form the central fact table and dimension tables for a star schema. Each new table will be created by joining two or more source tables that are related to a particular dimension, such as customers, geography, organization, and time.

The target tables are described in the following sections.

Target to Support OLAP

A SAS cube named Star will be created to support OLAP. This cube will support reports similar to Display 4.7 on page 33.

Target to Provide Input for the Cube

In this example, the ORDER_FACT table that is described in "Target That Combines Order Information" on page 32 is the central fact table in a star schema. Its dimension tables are described in the following sections.

Target That Combines Customer Information

The CUSTOMER_DIM table will be created by joining the tables described in "Sources Related to Customers" on page 33. In this example, CUSTOMER_DIM is one dimension of a star schema.

Target That Combines Geographic Information

The GEOGRAPHY _DIM table will be created by joining the tables described in "Sources Related to Geography" on page 33. In this example, GEOGRAPHY_DIM is one dimension in a star schema.

Target That Combines Organization Information

This dimension table is the same as "Target That Combines Organization Information" on page 32. In this example, ORGANIZATION_DIM is one dimension in a star schema.

Target That Combines Time Information

The TIME _DIM table will be created by joining the tables that are described in "Sources Related to Time" on page 34. In this example, TIME_DIM is one dimension in a star schema.

< Day Day Up >