Once the cube is processed within Analysis Services, the data that we have accumulated in the cube is ready for analysis. We can approach this in many ways and, for the time being, will focus on the "slice and dice" and drill-down capabilities available to us within the Cube Browser. While many tools can be used to access the data for analytical operations, the Cube Browser is provided within Analysis Services to afford us a means of browsing our data without the necessity of adding additional software to our machines.
What's more, the Cube Browser is, in itself, a relatively useful data analysis tool: It makes it possible not only for us to browse the data in our cubes, but to tailor the amount of dimension data that is visible (via filtering), to drill up (to see less detail / more summary) or to drill down (to see more detail, particularly that underlying a chosen summary number). It is particularly useful in checking the accuracy, completeness and effectiveness of our model design at the Analysis Services level, regardless of our ultimate plans for our cubes.
We will begin by going once more to the Analysis Manager console, where we will expand the MyFirstCube data source, then expand the Cubes folder, to see MyFirstCube appear. We right-click the cube, then select Browse Data from the popup menu. The Cube Browser appears at this point, as shown below in Illustration 46. We see a single dimension, Product Category, in the initial display, and two measures, Store Sales and Store Cost. Scrolling to the right reveals a third measure, Unit Sales; at the top of Cube Browser, we can see two additional dimensions, Region and Time.
Illustration 46: The Cube Browser, Initial Display
We choose to change our dimensions' positions by dragging the Region dimension at the top of the Browser to the top of the Product column, watching for the appearance of a double-ended arrow pointer, then by dropping the Region dimension onto the top of the column. The resulting grid appears below in Illustration 47. Notice, too, that the Product dimension has assumed the original position of the Region dimension, atop the Browser. We can now see the aggregate Store Sales of each country, side by side with the associated country's Store Costs and Unit Sales. We note that the Time dimension at the top indicates "All Time," meaning that the measures we see apply to all time measured in our cube. The dimensions default to highest summary level from the outset of the browse, and we can always tell their relative setpoints, and thus the perspective of the cube, simply by glancing at the dimensions' setpoints atop the Browser. Keep this in mind anytime you attempt a Browse, to prevent becoming "lost" in the cube at any point.
Illustration 47: The Cube Browser, After Changing Positions of Dimensions -- also Scrolled to Right to Display Other Measures
Next, say we wish to see the makeup of the Sales and Unit measures from a Product perspective, and thus want to add, rather than replace, a dimension to the grid. We accomplish this by simply dragging the Product dimension to the center of the grid. When we drop the Product dimension here, we see our Store measures break out the individual product components automatically in the grid, as illustrated below (Illustration 48). We also note that the All Products column remains, acting as a "totals" column, as well as a basis for numerous calculations and analyses.
Illustration 48: The Cube Browser, after Adding the Product Dimension
We perform a filter process by simply clicking the arrow to the right of the Time dimension, and expanding the resulting hierarchical icons down to the third quarter of 1998, as shown in Illustration 49. We click the Quarter 3 icon, and see it appear in the Time dropdown box at the top of the browser. Additionally, we see the measures in the grid adjust to reflect the values that belong to the filtered time frame. (Illustration 50).
Illustration 49: Selection of the Third Quarter of 1998 in the Time Dimension Hierarchy
Illustration 50: Results of Filtering through the Time Hierarchy based upon the Above Selections
Next we'll perform a drill down, seeking lower levels of summary than we currently observe. While we'll explore many more features of the drill down process in future articles, for now we'll keep things simple. We double-click on the Product Category cell (the fact that it displays a "+" sign indicates its capacity as a drill down point), and note that drill down occurs to the Product Subcategory level (itself a drill down point, as it displays a "+" at the left of the cell), breaking down the measures in the grid to their respective subcomponents, as one might anticipate (see Illustration 51 below).
Illustration 51: Results of Drilling Down from the Product Category to the Product Subcategory
We can always drill up simply by double-clicking the newly expanded Product Category cell. Note, too, that the presence of the Category and Subcategory cells, much like the dimension setpoints we noted earlier, serves to alert us at any given time "where we are" in the drill down hierarchy.
We could certainly go much further, drilling down, for instance, on the various countries through Regions, Provinces, Districts and Cities, to reach the level of analysis that might be most meaningful. The combination of various drill downs with combinations of dimensions ("slice and dice") can provide sophisticated information about the relationships we arrange for analysis, and is the essence of OLAP reporting. And we can always drill up again, as we noted earlier, to return to higher levels of summary as our needs dictate.
Add databasejournal.com to your favorites Add databasejournal.com to your browser search box IE 7 | Firefox 2.0 | Firefox 1.5.xReceive news via our XML/RSS feed
IE 7
Firefox 2.0