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.

• 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.

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.

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

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.

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.

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