Working with an OLAP Pivot Table

You now have a blank pivot table, just as if your data was on another worksheet in Excel. As soon as you start dragging and dropping dimensions and measures, you'll have an eyeful of meaningful data. Let's start slicing the cube.

Arranging the Data

As with regular Excel pivot tables, there is a PivotTable toolbar and a PivotTable Field List. You will notice in Figure 10.10 that each field in the field list has an icon next to it. The fields Store Cost, Store Sales, and Unit Sales have an icon with two rows of ones and zeroes. This icon denotes that these fields are numeric measures. The other fields have an icon with a blue and grey hierarchy. These fields are the dimension fields. If your DBA correctly followed the Analysis Services tutorial, you'll see five dimensions: Customer, Product, Promotion, Store, and Time.

Figure 10.10. Icons in the field list indicate whether a field is a measure or a dimension.

On the worksheet, you can see an outline of the pivot table layout. There are four areas: page, row, column, and data. The data area is where you'll be dragging measures. You remember measures; those are the pieces of data you want to see summarized or otherwise aggregated. The page, row, and column areas are for dimensions, which are the attributes by which you want to do the aggregating.

Click and hold on Store Sales and then drag it over to the data area. When you drop it, it shows you the total dollars of revenue for all your stores. Of course, that's not very interesting. Drag the Store Sales measure out of the upper-left corner of the layout to any area away from the layout, until you see it accompanied by a red X; letting it go now removes that field from the layout. Now you are back to your beginning layout.

Drag and drop the Customer dimension into the Drop Row Fields Here area. Drag and drop the Time dimension into the Drop Column Fields Here area. Drag and drop the Store Sales into the Data area.

Immediately and without any other effort, a summary of 200+ million records shows the 1998 sales figures for the three countries in which Foodmart operates, as shown in Figure 10.11.

Figure 10.11. This OLAP pivot table appears similar to a regular pivot table, except it is summarizing 200+ million records.

Drilling Into the Cube

Examine Figure 10.11. Although you had dropped a field called Customer to the row area of the pivot table, the actual pivot table shows a field called Country. This is because the Customer dimension is a hierarchy of data. Choose the plus sign next to Customer in the PivotTable Field List. You will see that customers are stored by Lname but are grouped by city, state, and country, as shown in Figure 10.12.

Figure 10.12. Dimension fields in the PivotTable Field List can be expanded to show all the aggregations available.

By default, when you drag Customer to the pivot table, you will see the most summarized view of the data. The default pivot table shown in Figure 10.11 included data by country and year.

It is very easy to start drilling down into the data. Double-click on Mexico, and you will see the layout automatically expands to show eight Mexican states, as shown in Figure 10.13.

Figure 10.13. OLAP cubes have groups already built-in. Simply double-click to drill into the data.

Double-click on Zacatecas, and the layout will expand to show the two cities within Zacatecas.

Go up to the cell named 1998 and double-click on it. The layout expands to show the four quarters of 1998. Double-click on each quarter to see each month, as shown in Figure 10.14.

Figure 10.14. Only a few clicks are required to drill down to this data.

To go back to a previous rollup of the data, you have to hide the detail for a measure. In Figure 10.15, select Zacatecas in cell B15 and click the Hide Detail button in the PivotTable toolbar.

Figure 10.15. Use the icon with the red minus sign to hide detail for Zacatecas.

Using Page Fields

Now try dragging and dropping some other dimension, such as Product, up to the Drop Page Fields Here area. The page area acts as a filter. By default, it shows All Product. Select the drop-down to see a list of categories. Click the plus sign next to Baking Goods, as shown in Figure 10.16, to reveal product subcategories. Choose Cooking Oil.

Figure 10.16. The Product page field offers a number of hierarchical choices as well. Click any plus sign to expand it.

Click the Cooking Oil drop-down. Expand the Cooking Oil subcategory to choose the BBB brands. You may notice at this point that not every state carries this brand; those that don't will show an empty cell, as shown in Figure 10.17.

Figure 10.17. If you drill down far enough, you may begin to encounter sparse data. The state of Jalisco did not purchase any of this brand in Q1.

What you're doing is drilling down through levels of detail, and at each level you can see the aggregations of the measures are immediately presented to you. There's no pausing for any calculating, because all those summarized totals are already (either in actuality or virtually) prepared and stored in the OLAP cube. Feel free to experiment, combining multiple dimensions in the rows, in the columns, and in the page filter.