The capability to "explode"
the hierarchical levels of our dimensions to member children enhances
business user data analysis by allowing them not only to view the reports to
which they have become accustomed (for example, a trending of monthly
organizational expenses), 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 / she drills down upon. The beauty of multidimensional
analysis becomes clear, with this interactive ability to find root causes for
changes in activity over time. Information consumers can also return to higher
levels within hierarchies to view summary information.
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 will see. We can explore the
drill-down capabilities of the PivotTable report by performing the following
Double-click the USA Store
Country in the leftmost column of our existing report.
report drills to the Store States, the immediate children of the Store
Country, as shown below.
20: Drilling Down to the Children of the Store Country USA
Double click the Washington
(WA) Store State to drill to its children.
children of the Washington Store State appear, as shown below.
21: Children of the Washington Store State
can always reverse a drill-down action ("drill up," as it
were) by double-clicking the original drill-down object. We will
short-circuit the process and zoom up to the original Store Country
the USA Store Country once again. The result set should resemble
that depicted below.
22: Zoom Up to the USA Store Country
again, to expose the three Store States once more. 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 on the PivotTable toolbar (see Illustration 23 below for the Show
Details and Hide Details button pictures). This provides another
means of drilling down to the immediate children.
23: The Show Details and Hide Details Buttons
The children of all
members of the Store State level appear, as illustrated below.
24: Children of the Entire Store State Level
Next, let's assume that
we want to hide the "USA" column of the report (logical enough, 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 in the context menu
that appears, as shown below.
25: The Context Menu - Hide Levels Option
This leaves us with a
view that is more compact. Compare the result to that shown in Illustration
26: The PivotTable Report, sans the USA Store Country Column
There are many other
options in browsing our cubes, as well as with formatting the views we
generate. It pays to invest some time experimenting with the plethora of
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 will delve a bit further into the concepts of adding multidimensionality
to our reports, and demonstrate a straightforward approach to leveraging even
more of the power of our OLAP cube to deliver analysis-focused data.