Using PowerPlay for Windows for Reporting and Analysis with an Analysis Services Cube

We can perform the same general PowerPlay functionality in working with the MSSQL_Warehouse cube that we can perform with a proprietary PowerCube. We can also access local cubes (.cub files) that are saved directly from the MS PivotTable Service from PowerPlay. Further, we can also perform many functions that are not available within the standard PowerCube scenario. Among these are Analysis Services’ extended capabilities in the areas of multiple hierarchies, member properties, and virtual cubes and dimensions. We can exploit differences that exist in Analysis Services’ calculated members capabilities, as well.

7. Click the View menu item in the top toolbar.

8. Uncheck the Dimension Viewer option (to free up a bit more room on the screen), if appropriate.

9. Click the Store Folder in the Dimension Line (the line of labeled folder icons just atop the crosstab area.)

We are presented with a flyout selection of first level Store dimension categories (countries), from which we can select to drill down.

10. Click USA to drill to the USA Store data.

The data in the crosstab now reflects the drilled down presentation. Notice that “USA” now appears as the label on the Store dimension folder – where we saw Store before. This also demonstrates handily something about how PowerPlay’s Explorer mode works: When we drill down in PowerPlay Explorer, we replace a parent category with its child categories in the report – all simultaneously. In this example, we can see that, by selecting USA (country level) for drilldown, the child categories (state level) are displayed. (Drilling down on a nested level, the creation of which we will introduce later, preserves the number of levels, until we reach the bottom of the hierarchy.)

The display appears.

Let’s drill a level lower to reinforce our understanding.

11. Click the Store folder (now labeled USA) again.

Notice that the flyout selection now represents second level Store dimension categories (states); Store appears at the top (and we can easily “drill up” or “zoom” back to the top level by clicking its name here), with the categories that are beneath the country level appearing as the next lower-level drill candidates.

12. Click OR to drill to the Oregon Store data.

“OR” now appears as the label on the Store folder – and the measures that appear in the crosstab adjust accordingly. We have drilled to the Oregon category, state level of the Store dimension.

13. Click USA on the Store categories list to zoom / “drill up” to the USA Store data.

14. Drag the Product folder and drop it to the right of the Stores (now states) list on the report. (A “ghost” image will appear when we touch the dragged object to the drop point, shown circled in our illustration.)