Share via


SalesData Example

The SalesData example model described in this section will be referenced throughout the OLE DB for OLAP documentation. In its entirety, the SalesData example represents a complex multidimensional data store containing several dimensions. Because OLE DB relies on the dataset object to present various views of the data in this multidimensional model, on any combination of its dimensions, the following illustration is intended only to provide a conceptual representation of a simple three-dimensional dataset, or cube, that might be derived from the SalesData model.

SalesData cube with multiple dimensions

As defined in OLE DB for OLAP, each dimension in a data cube is either an axis dimension or a slicer dimension. For example, in the preceding illustration, Sales (a member of the Measures dimension) and Products are on the axis dimensions x and y (also called the ROW and COLUMN axes), respectively, and Years represents the slicer dimension, with 1991 representing a possible two-dimensional "slice" as a table showing all product sales for 1991.

SalesData Dataset

The SalesData dataset includes the following dimensions:

  • SalesRep Names of all sales representatives.

  • Geography A hierarchy that includes the levels Continents, Countries, Regions, and Cities.

  • Months

  • Quarters

  • Year

  • Measures, of which the three are Sales, PercentChange, and BudgetedSales

  • Products

The inherent beauty of an OLAP application is its ability to calculate and view data in a number of ways through different dimensions. For example, it is possible to use PercentChange of the Measures dimension to calculate the percentage variance in sales of a particular product over a selection of years. Or, from another perspective, you could calculate the percent of change in sales of a particular product as compared against all other products over a selection of years.

Using five of the SalesData dimensions listed above, the following dataset illustration shows how PercentChange would be reported across SalesRep of a Geography region by Years, for all Products.

percent change in sales aggregated by year and rep

Another user might want to see how products fare in certain geographical areas, and not be concerned about time or growth, but rather be interested in simple sales totals for each product through the quarters of the past year. Accessing data through the same dimensions as in the preceding example, the application can report the data for the selected products across the SalesRep and Geography dimensions for each quarter in a given year, as shown in the following dataset illustration.

sales data aggregated by salesrep and region

Note

The axis on which a dimension is located can vary, depending on how the user or programmer wants to view or configure the information.

  • Two axes dimensions encompassing Measures, SalesRep, and Geography in one, and Year in the other, as follows?

    • The measures (represented by PercentChange or Sales) dimension, the SalesRep dimension, and the Geography dimension compose the ROW (or x) axis.

    • The Year or Quarters dimension composes the COLUMN (or y) axis.

  • One slicer dimension: Products.

The following sections provide sample tables and illustrations of the SalesData dimensions, corresponding members, and hierarchies.

SalesData Dimensions/Members

The following table displays seven dimensions of the SalesData dataset and a sampling of members in each dimension. This table is used as the basis of all hierarchical structures listed below the table.

Note

Due to space considerations, an ellipsis (...) is sometimes used to indicate those portions of information not included for a specific example.

SalesRep

Geography

Quarters

Months

Year

Measures

Products

Director NA

All

QtrYear

January

1989

Sales

Computers

Netz

North America

Qtr1

February

1990

PercentChange

Expansion Cards

Venkatrao

Canada

Qtr2

March

1991

BudgetedSales

Floppy Drives

Director Europe

USA

Qtr3

April

1992

Ceiling Fan

Ng

Canada_East

Qtr4

May

1993

75W Light Bulb

King

Canada_West

June

1994

AC Adapter, 12V

USA_North

July

1995

AC Adapter, 6V

USA_South

August

1996

Printers

USA_West

September

1997

Scanners

USA_East

October

1998

Boise

November

1999

Boston

December

2000

Calgary

2001

Cookstown

Houston

Los Angeles

Miami

New York

Ottawa

Pembroke

Seattle

Shreveport

Toronto

Vancouver

Japan

SalesData Geography Hierarchies

The SalesData Geography hierarchy, detailed in the following table, is constructed from members of the Geography dimension as listed in the preceding SalesData Dimensions/Members table above.

Geography.All

Geography.NorthAmerica

Geography.Europe

USA

Geography.NorthAmerica

USA_East

Boston

New York

Cookstown

Pembroke

USA_South

Houston

Miami

USA_North

Seattle

Boise

USA_West

Los Angeles

Canada

Canada_East

Ottawa

Toronto

Canada_West

Vancouver

Calgary

Geography.Japan

(N/A)

The following illustration provides a conceptual view of the preceding table.

Note

The root level member (All) of this hierarchy has no parents, and the leaf level members (Cities) have no children.

example of a geographical dimension

SalesData Time Hierarchies

The SalesData Time hierarchy shown in the following table is constructed from members of the Quarters and Months dimensions, which are listed in the SalesData Dimensions/Members table.

QtrYear

Qtr1

January

February

March

Qtr2

April

May

June

...

The following illustration provides a conceptual view of the preceding table:

time dimension with quarters and months

For comparative purposes, the following table shows how the Time hierarchies are built for both calendar and fiscal year reporting.

Year-Calendar

Year-Fiscal

Qtr1

Qtr1

January

July

February

August

March

September

Qtr2

Qtr2

...

...

The following illustration provides a conceptual view of the preceding table:

Comparison of calendar and fiscal year dimensions