Tutorial 16: MSAS : Browsing the Cube
The processed cube can now be analyzed. For the purpose of this tutorial we will use ‘slice and dice’ and drill down capabilities of the Cube Browser to browse the cube. The Cube Browser is a tool provided within the Analysis Services to display the results of the cube process without the need to add external software. It is a relatively useful data analysis tool that can display the dimension data as required by the end user. The user can drill up and drill down and check the accuracy, completeness and effectiveness of the model design at the Analysis services level.
Open the Analysis Manager console and expand the Sample data source and then the Cubes folder to see the sample cube. Right click on the cube and select Browse data from the pop up menu. The Cube browser is displayed. A single dimension appears on the browser with three measures. The dimension displayed is Product Category and the three measures are store sales, store cost and unit sales. Two additional dimensions are also displayed Region and Time. The positions of the dimensions can be changed by dragging the region dimension to the top of the columns and dropping it there. A double ended arrow pointer appears while we drag and drop
The resulting grid that appears is displayed in the figure to the right above. The product dimension has assumed the position of the region dimension. We can now see the aggregate store sales by country and region. “All region” is the topmost in the hierarchy. The time dimension is for “All time” and the product dimension is for “All product”. This is because the dimensions default to the highest summary level.
We can add dimensions to the grid by simply dragging the Product dimension to the center of the grid. When the product dimension is dropped the store measures will break down to the individual product components automatically. All Product totals will remain as “totals column” and will be the basis for numerous calculations and analysis.
A filter process can be performed by clicking the arrow to the right of the time dimension and expanding the resulting hierarchical icons. The quarter 3 icon is clicked in the picture below. This will appear in the Time dropdown box and the grid will reflect the values that belong to the filtered time frame.
The drill down process seeks to reveal levels of summary. For this purpose we need to double click on the dimension cell. The ‘+’ sign indicates that detail levels are available for study. On double clicking the category the drill down happens to the dimension and levels and measures are broken down in the grid in to sub components.
To drill up the user has to simply click the newly expanded dimension cell. The presence of the category and sub category cells are dimension site-points that help the user know where he is in the drill down hierarchy.
The combinations of drill downs and slice and dice can provide sophisticated information to the user about the relationships between data. This is the essence of OLAP reporting.