To understand OLAP reporting, suppose that Terry is a high-level marketing manager in a company called Contoso. Contoso manufactures and sells nylon backpacks and shoulder bags around the world. Terry and his team are preparing a marketing campaign to launch their new line of shoulder bags. In defining their marketing objectives, Terry and his team start with the following questions:

  • How many shoulder bags did Contoso sell last year?

  • In which quarter did Contoso sell the most shoulder bags? The fewest shoulder bags?

  • Which model shoulder bag was most popular? Least popular?

While each of these questions is important for their marketing campaign, the questions are too broad to help Terry and his team take specific action in specific markets. Accordingly, Terry and his team develop questions that are more complex. For example:

  • How many shoulder bags did Contoso sell last year in Europe? In Asia? In the United States?

  • How many shoulder bags did Contoso sell last year in a specific region or city of a specific country/region?

  • In which quarter did Contoso sell the most shoulder bags of a specific model and in which region? Which colors of that model? To which customer account?

  • In which quarter did Contoso sell the fewest shoulder bags of a specific model and in which region? Which colors of that model? To which customer account?

With answers to these questions, Terry and his team can more accurately predict the results of a targeted marketing campaign. These are the kinds of questions to which they need answers.

Getting answers from a database with OLAP reporting

Now that Terry and his team have the appropriate questions, they must locate the answers to these questions. The answers exist in the historical business data on their Oracle or SQL Server database. The prospect of sorting through historical data could be daunting, but Terry and his team plan to use OLAP reporting to get answers from their data.

Terry sets up a meeting with their software developer, Karen, to create the OLAP query. Karen has the necessary permissions to create the OLAP query. For their first query, Terry and Karen decide to focus on this question:

How many shoulder bags did Contoso sell last year in North America?

Dimensions

In looking at the question in more detail, you can see that the OLAP query must retrieve data from each of the following tables in the database:

  • Product (shoulder bag)

  • Region (North America)

  • Time Period (fiscal year 2007)

In the context of OLAP reporting, each of these tables is referred to as a dimension. Most OLAP queries contain several dimensions, and for this reason, OLAP reporting is referred to as multidimensional reporting. The multidimensional nature of the query can be represented as a cube.

In their current cube, Terry has identified a Location dimension. If Terry wanted to view more specific data, he could narrow the query by selecting a level. A level is a subset of a dimension. For example, the Location dimension could contain the following subsets or levels: continent, country, region, and city.

Measures

If you look again at Terry's business question (How many shoulder bags did Contoso sell last year in North America?), you see that the OLAP cube is missing one part of the question: the total (how many). OLAP cubes must contain at least one aggregate function, such as a sum (how many) or a maximum value. In OLAP terminology, this aggregate function is called a measure. The following table shows aggregate functions available in Microsoft Dynamics AX.

Measure

Description

Sum

The sum of the input values.

Min

The lowest of the input values.

Max

The highest of the input values.

Count

The number of input values.

Putting it all together

Having mapped out the original business question in terms of dimensions, levels, and measures, Terry and Karen are ready to create and process the OLAP cube. (For instructions on how to create and process a cube, see the Microsoft Dynamics AX developer documentation.)

After Terry and Karen have processed the cube, they can view the OLAP data in a PivotTable report in Excel and thereby get the answers to the original business question.

For a more detailed introduction to OLAP concepts, see SQL Server Books Online.

See Also