Introduction to MSSQL Server Analysis Services: Reporting Options for Analysis Services Cubes: MS Excel 2003 and More ... - Page 4
September 12, 2005
Viewing Member Details
The capability to "explode" the hierarchical levels of our dimensions to member children enhances data analysis by allowing business users 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 supporting any summary value. This allows the analyst to see the details that make up the value upon which he / she drills down. The beauty of multidimensional analysis becomes clear, with this interactive ability to find root causes for changes in activity over time (among myriad other values). 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 actions:
11. Within the Warehouse column of the existing report, double-click the cell containing USA.
The report drills down to the Warehouse State Province, the immediate children of the Warehouse Country, as shown in Illustration 21.
Click for larger image
12. Double click the Washington (WA) Warehouse State Province to drill to its children.
The children of the Washington Warehouse State Province appear, as depicted in Illustration 22.
We 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 Warehouse Country level, where we began drilling earlier.
13. Double-click the USA Warehouse Country once again.
The resulting dataset should resemble that depicted in Illustration 23.
14. Double-click USA again, to expose the three Warehouse State Provinces once more.
15. Click (once, to highlight) the Warehouse State Province level heading (above the CA member, and to the right of the Warehouse Country column.
16. Click the Show Detail button on the PivotTable toolbar (another means of drilling down to the immediate children), as shown in Illustration 24.
The children of all members of the State Province level appear, as depicted in Illustration 25.
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 United States - the "USA" level is somewhat redundant, and takes up useful real estate.)
17. Right-click the Warehouse Country level heading.
18. Click Hide Levels in the context menu that appears, as shown in Illustration 26.
This leaves us with a view that is more compact, similar to that depicted in Illustration 27.
There are many other options for 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 settings 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.