- Introduction to the Series
- What You Will Need to Complete the Series Tutorials
- Building an Excel PivotTable Report on OLAP Data Source
- Connecting Excel to the OLAP Cube
- Layout and Navigation of the PivotTable Report
- Browsing the Cube
- Drilling to Member Details
- Going Multidimensional
- Next in This Series
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Going Multidimensional
One distinct limitation of a typical spreadsheet report is its restriction to two axes, representing columns and rows. The Excel PivotTable report overcomes this handicap by allowing for the presentation of multiple dimensions, a feature that's necessary to provide a full realization of the information-presentation potential of the multidimensional cube. The PivotTable report does this by intersecting dimensions on a single axis, and therefore delivers the full impact of multidimensional data in such a way that we can analyze this information in the two-dimensional world of the typical reporting environment.
The following steps demonstrate how you can unleash the multidimensional power of cubes by making multiple dimensions share the same axis in the PivotTable report. Let's begin by making the store type a part of the row axis to make analysis of store members more powerful, yet more compact.
Drag the Store Type button from the page axis (upper-left corner of the PivotTable report) to the left of the Store State column, dropping it to the left of Store State. The result set should resemble that shown in Figure 30.
Figure 30 Combining the store type and store dimensions in the row
axis.While we now have three columns visible, we can easily tell that only the Store Type label represents the top level of a dimension, as a drop-down arrow only appears at top levels. Thus, multiple dimensions (in this example, the store type and store dimensions), as well as multiple levels of a given dimension (here, the store state and store city levels of the Store dimension), can coexist on a single axis.
Now let's add another measure to the report—Unit Sales—to give us more information about store performance.
Drag the Unit Sales button from the PivotTable toolbar to the data section of the PivotTable report (the column under the Year heading). Once you drop the measure, the PivotTable report should resemble the illustration below.
Figure 31 Dual measures now appear in the PivotTable report.A new data column appears, representing what appears to be a fourth row dimension. This is the default behavior, which we can easily change as needs dictate.
Drag the Data button to the column axis; the cell just above its present position will serve (see Figure 32).
Figure 32 The PivotTable report with a second column dimension.Hiding either measure is as simple as clicking the drop-down arrow next to the Data dimension button and clearing the respective check box.
Let's move the Time dimension to the page axis to make our presentation a bit less confusing for its audience.
Drag the Year dimension button to the page axis in the top row of the PivotTable report.
After this final change, the report appears as shown in Figure 33. Making a PivotTable report truly multidimensional is both straightforward and intuitive, once you get a good understanding of the basics.
Figure 33 The PivotTable report with final changes.
)
Figure 30)
Figure 31)
Figure 32)
Figure 33