3 The Sample Schema

3.1.1 Reporting Requirements

When asked to be more specific about what she needs, the Vice President of Sales and Marketing identifies the following requirements:

• Trended sales data for specific customers, regions, and segments.

• The ability to provide information and some analysis capabilities to the field sales force. A Web interface would be preferred, since the sales force is distributed throughout the world.

• Detail regarding mail-order, phone, and e-mail sales on a weekly and monthly basis, as well as a comparison to past time periods. Information must identify when, how, and what is being sold by each channel.

• Margin information on products in order to understand the dollar contribution for each sale.

• Knowledge of percent change versus the prior and year-ago period for sales, units, and margin.

• The ability to perform analysis of the data by ad hoc groupings.

The CIO has discussed these requirements with his team and has come to the conclusion that a standard reporting solution against the production order entry system would not be flexible enough to provide the required analysis capabilities. The reporting requirements for business analysis are so diverse that the projected cost of development, along with the expected turnaround time for requests, would make this solution unacceptable.

The CIO's team recommends using an analytic workspace to support analysis. The team suggests that the Sales and Marketing department's IT group work with Corporate IT to build an analytic workspace that meets their needs for information analysis.

3.1.2 Business Goals

The development team identifies the following high-level business goals that the project must meet:

• Global Computing's strategic goal is to increase company profits by increasing sales of higher margin products and by increasing sales volume overall.

• The Sales and Marketing department objectives are to:

  • Analyze industry trends and target specific market segments

  • Analyze sales channels and increase profits

  • Identify product trends and create a strategy for developing the appropriate channels

3.1.3 Information Requirements

Once you have established business goals, you can determine the type of information that will help achieve these goals. To understand how end users will examine the data in the analytic workspace, it is important to conduct extensive interviews. From interviews with key end users, you can determine how they look at the business, and what types of business analysis questions they want to answer

3.1.4 Identifying Required Business Facts

The key analysis requirements reveal the business facts that are required to support analysis requirements at Global Computing.

These facts are ordered by time, product, customer shipment or market segment, and distribution channel:

Sales
Units
Change in sales from prior period
Percent change in sales from prior period
Change in sales from prior year
Percent change in sales from prior year
Product share
Channel share
Market share
Extended cost
Extended margin
Extended margin change from prior period
Extended margin percent change from prior period
Margin, percent of total product sales
Units sold, change from prior period
Units sold, percent change from prior period
Units sold, change from prior year
Units sold, percent change from prior year

These facts are ordered by item and month:

Unit price
Unit cost
Margin per unit

3.1.5 Designing a Logical Data Model for Global Computing

"Business Goals" identifies the business facts that will support analysis requirements at Global Computing. Next, we will identify the dimensions, levels, and attributes in a logical data model. We will also identify the relationships within each dimension. The resulting data model will be used to design the Global star schema, the OLAP Catalog metadata, and the analytic workspace.

3.2.1 Dimension Table: TIME_DIM

The TIME_DIM table defines a time dimension with three levels. Each level is supported by four columns: a numeric surrogate key, a textual description, an end date (last day in time period), and a time span (number of days in time period). This is the most basic information required to define a time dimension.

The surrogate keys are artificial values with no meaning outside the context of the table. They assure that the same values are not repeated at different levels, and they provide the fastest processing speeds both in the relational tables and in the analytic workspace. The descriptive columns provide meaning to these numeric identifiers.

The end date and time span columns support time-series analysis, such as:

• Change from a prior period

• Change from a year ago

• Year-to-date

• Range of time

There are seven years defined, from 1998 to 2004, with data provided for 1998 to early 2003. The last year is available for forecasting.

In the standard hierarchy, the rollup sequence from the base to the top level is:

MONTH -> QUARTER -> YEAR

Table 3-1 describes the columns of the TIME_DIM table.

3.2.2 Dimension Table: CUSTOMER_DIM

The CUSTOMER_DIM table defines seven levels that will be used to define two hierarchies. Each level has a numeric surrogate key and a textual description, which is the most basic information to define a "normal" dimension, that is, a dimension that is not time. SHIP_TO is the primary key, and its values will become the base-level members for both Customer hierarchies.

In the Market hierarchy, the rollup sequence from the base to the top level is:

SHIP_TO -> ACCOUNT -> MARKET_SEGMENT -> TOTAL_MARKET

In the Customer hierarchy, the rollup sequence is:

SHIP_TO -> WAREHOUSE -> REGION-> ALL_CUSTOMERS

Table 3-2 describes the columns of the CUSTOMER_DIM table.

3.2.3 Dimension Table: PRODUCT_DIM

The PRODUCT_DIM table defines a product dimension with four levels. Each level has a numeric surrogate key and descriptive text. ITEM_ID is the primary key, so its values will become the base-level members of the Product dimension.

In the Product hierarchy, the rollup sequence from the base level to the top level is:

ITEM -> FAMILY -> CLASS -> TOTAL_PRODUCT

Table 3-3 describes the columns of the PRODUCT_DIM table.

3.2.4 Dimension Table: CHANNEL_DIM

The CHANNEL_DIM table contains four columns. CHANNEL_ID is the primary key, and its values will become the base-level members of the Channel dimension. ALL_CHANNELS_ID defines a single value that represents all of the channels. In the OLAP Catalog, these two columns will define the two levels of a single Channel hierarchy. The rollup sequence from the base level to the top level is simply:

CHANNEL -> ALL_CHANNELS

The remaining columns, CHANNEL_DSC and ALL_CHANNELS_DSC, provide textual descriptions that give the surrogate keys meaning.

Table 3-4 describes the columns of the CHANNEL_DIM table.

3.2.5 Fact Tables: UNITS_HISTORY_FACT and _UPDATE_FACT

The UNITS_HISTORY_FACT and UNITS_UPDATE_FACT tables contain four foreign key columns, which together comprise a multi-column primary key. The foreign keys are related to the primary keys of the four dimension tables.

In UNITS_HISTORY_FACT, every foreign key value for Product, Customer, and Channel is used at least once, and 65 time periods are used. The table contains 169,487 rows of a possible 513,864 unique key combinations.

UNITS_UPDATE_FACT adds data for month 91 (Jun-03). Every foreign key value for Product, Customer, and Channel is used at least once. The table contains 3,459 rows of a possible unique 6,588 key combinations.

Table 3-5 describes the columns in both tables.

3.2.6 Fact Tables: PRICE_AND_COST_HISTORY_FACT and _UPDATE_FACT

The PRICE_AND_COST_HISTORY_FACT and PRICE_AND_COST_UPDATE_FACT tables contain two foreign key columns, which together comprise a multi-column primary key, and two fact columns.

In PRICE_AND_COST_HISTORY_FACT, data is provided for all products for 65 months.

PRICE_AND_COST_UPDATE_FACT adds data for month 91 (Jun-03) for all products.

Table 3-6 describes the columns of both tables.

3.3.1 Global Product Dimension Mapping

Table 3-7 shows how the columns of the PRODUCT_DIM dimension table are mapped to workspace objects to provide an embedded total PRODUCT dimension.

PRODUCT_DIM supports a single dimension hierarchy, PRODUCT_ROLLUP, with four levels: ITEM, FAMILY, CLASS, and TOTAL_PRODUCT. The descriptive columns are mapped to both the long and short descriptions, but this redundancy in the analytic workspace is not required.

3.3.2 Global Time Dimension Mapping

Table 3-8 shows how the columns of the TIME_DIM dimension table are mapped to workspace objects to provide an embedded total TIME dimension.

TIME_DIM supports a single dimension hierarchy, Calendar, with three levels: Month, Quarter, and Year. For time-based analysis in the analytic workspace, a Time dimension must have End_Date and Time_Span attributes, as it does here. The descriptive columns are mapped to both the long and short descriptions, but this redundancy in the analytic workspace is not required.

3.3.3 Global Price Cube Mapping

Table 3-9 shows how the columns of the PRICE_AND_COST_HISTORY_FACT fact table are mapped to workspace objects to provide a PRICE_CUBE cube with two measures, UNIT_COST and UNIT_PRICE.

An aggregation operator is defined in the OLAP Catalog and is the basis for an initial aggmap for the cube. The aggmap provides the rules of aggregation. The measure formulas use the aggmap to aggregate the base-level data loaded into the measure variables.

Most variables are sparse and require a composite dimension, which is associated with the cube.