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.

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.

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.

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.

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:

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: