The sample
data model used in these lessons is derived from the Sales History (SH) schema
that is part of the Oracle Database Common Schema.
For this
data model, Oracle Warehouse Builder (OWB) was used to transform the SH schema
into an Oracle analytic workspace. The workspace is a multidimensional data
source that is optimized for online analytical processing (OLAP).
One of the
key performance indicators (KPIs) for the sample company is profitability.
Executive management must be able to report on this key indicator in order
to make strategic business decisions.
At present,
senior executives are using reports that query the data entry systems. This
has not provided the type of summarized OLAP reporting and analysis that executives
can easily use.
Overview
of the Data Model
The Common Schema analytic workspace data model describes the
organization of data by each of the dimensions.
Data
Model: Dimensions
The dimensions
used in the solutions are described below.
Time
The Time
dimension is based on a normal ISO calendar. It therefore has the following
hierarchy (highest to lowest level):
YEAR
> QUARTER > MONTH
Channel
The Sales
Channel dimension is small. However it is very important in this data model.
To keep track of the changes within different channels, the Channel dimension
also has a Channel Class. It has the following hierarchy:
TOTAL
> CHANNEL CLASS > CHANNEL
Geography
The Geography
dimension is key to analyzing the business. This dimension was derived from
the Customer dimension, with its values aggregated up to Country as the lowest
level. In a real world situation, this dimension would probably have multiple
hierarchies. However, for simplicity, in this exercise there is a single,
simple Geography dimension with one hierarchy:
TOTAL
> REGION > SUB REGION > COUNTRY
Product
Product
is also a key dimension in the data model, for obvious reasons. In a real
world situation, this dimension would probably have multiple hierarchies.
However, for simplicity, this example uses a single Product dimension with
one hierarchy:
TOTAL
> CATEGORY > SUB CATEGORY > PRODUCT
Promotion
The Promotion
dimension is a key tool for the marketing department. Although it is a small
dimension, it is of great value to the organization. The Promotion dimension
has the following hierarchy:
TOTAL
> CATEGORY > SUB CATEGORY > PROMOTION
Data
Model: Measures
The model
supports the following data measures:
· Sales
Revenue
· Sales
Costs
· Quantity
These
measures are dimensioned by Product, Time, Channel, Promotion, and Geography.
Bookmark
