- 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
Drilling to Member Details
Another value that we gain in reporting from an OLAP data source is the capacity to "explode" the hierarchical levels of dimensions to member children, as well as to return to higher levels within hierarchies to view summary information. This capability enhances business user data analysis by allowing users not only to view reports to which they have become accustomed (for example, monthly organizational expense trends), but also to drill down to the details below any summary value. This allows the analyst to see the details that make up the value that he or she drills down on. The beauty of multidimensional analysis becomes clear with this interactive ability to find root causes for changes in activity over time.
The PivotTable report/cube combination provides the ability to view various levels of activity for members as a group, as well as to analyze the details of summary values on an individual member basis, as we'll see. We can explore the drill-down capabilities of the PivotTable report with the following steps:
Double-click the USA Store Country in the leftmost column of the report. The report drills to the Store States, the immediate children of the Store Country, as shown in Figure 22.
Figure 22 Drilling down to the children of the Store Country USA.Double-click the Washington (WA) Store State to drill down to its children (see Figure 23).
Figure 23 Children of the Washington Store State.We can also reverse a drill-down action ("drill up," as it were) by double-clicking the original drill-down object. Let's short-circuit the process and zoom up to the original Store Country level.
Double-click the USA Store Country again (see Figure 24).
Figure 24 Zooming up to the USA Store Country.Double-click USA again, to expose the three Store States again. Click (once, to highlight) the Store State level heading (just above the CA member) on the leftmost side of the report, and then click the Show Detail button (see Figure 25) on the PivotTable toolbar. The Show Detail button changes into the Hide Detail button, as shown in Figure 26.
Figure 26 The Hide Detail button.The children of all members of the Store State level appear, as shown in Figure 27.
Figure 27 Children of the entire Store State level.Next, let's assume that we want to hide the USA column of the report. That's logical if all our stores are located in the US—the USA level is somewhat redundant, and takes up useful real estate. Right-click the Store Country level heading and click Hide Levels on the context menu (see Figure 28).
Figure 28 Select the Hide Levels option.This leaves us with a more compact view (see Figure 29).
Figure 29 The PivotTable report without the USA Store Country
column.
)
Figure 22)
Figure 23)
Figure 24)
Figure 26)
Figure 27)
Figure 28)
Figure 29There are many other options in browsing cubes, as well as with formatting the views you generate. It pays to invest some time experimenting with the available choices and determining the combination of setpoints needed to get information to the targeted consumers in a fashion that will be most useful to them.
Next, we'll delve a bit further into the concepts of adding multidimensionality to our reports, and demonstrate a straightforward approach to leveraging even more the power of the OLAP cube to deliver analysis-focused data.