Introduction to MSSQL Server Analysis Services: Presentation Nuances: CrossTab View - Same Dimension - Page 2

A CrossTab View with the Same Dimension in Both Axes

Overview and Discussion

In many cube design scenarios in which we find ourselves, especially working with clients and customers with specific presentation effects in mind, we have numerous options for "where" to implement the mechanisms behind the final display. As I explain to clients in my daily work with MSSQL Server, Analysis Services, and Reporting Services, one of the most powerful things about an integrated Microsoft BI solution is the fact that it often presents three discrete "layers" at which we can build the structure that underlies the end results.

This is why organizations that undertake such a solution need to seek, as architects and implementers, an individual or team that is versed in all three layers of the solution. Although a "Reporting Services guru" can accomplish many of the enterprises needs from within the Reporting Services application, there are typically structural opportunities at the cube, or even RDBMS, level that might mean far more optimal reports, both from a processing standpoint and from a user-friendliness perspective.

An example might be the structure that underlies the parameterization of a Reporting Services report based upon an Analysis Services OLAP cube. While we might build the picklists and other components of parameterization solely within the reports, we might just as easily (and in some cases much more optimally) create the picklists within the underlying cubes, and simply reference them in any report based upon the same cube.

For that matter, and along the same lines, depending upon an "expert" in any of the three components who is not fluent in all three might be a recipe for mediocrity at best, and failure to meet objectives, at worst, in the pursuit of an integrated Microsoft BI solution. When assembling a team to plan and execute larger implementations of this sort, a project lead or other manager who is versed in all three layers can certainly go a long way toward ensuring the optimal allocation and coordination of design effort among a team of more specialized practitioners. One of the clear benefits is avoidance of an architecture where the "heavy lifting" demanded by the enterprise information consumers is supported in such a way that bottlenecks are inevitable - and, perhaps worse, irreversible.

In this article, we will present a reporting requirement that I come across fairly frequently, the delivery of a crosstab capability where the same dimension needs to appear in both the "x-" and "y-" axes of the ultimate presentation. As many of us learn in our early exposure to MDX, attempts to simply cast the same dimension in the ON ROWS and ON COLUMNS specifications of our MDX query, as is done in the following query:

SELECT 
    { [Store].[Store Country].Members} ON COLUMNS,
    {[Store].[Store City].Members} ON ROWS
FROM  
   [WAREHOUSE]
WHERE
   ([Time].[1998], [Measures].[Warehouse Profit])

results in our receiving an error, upon attempts at execution, similar to that shown in Illustration 1.

We will begin with a scenario that illustrates a requirement for such a dual-axis presentation, using a hypothetical business need to add practical value. Let's say that a member of the Finance department at a client, the FoodMart organization, has approached us with a request it wishes to meet, from its Warehouse cube users. We are told that the information consumers have requested a report that presents Warehouse Profit by U. S. Warehouse City, as well as the percent contribution to total Warehouse Profit, made by each Warehouse City to the whole for the FoodMart organization, for operating year 1998. While this appears to be a simple query - indeed, it has been attempted by an internal "guru" that focuses a great deal of personal effort on learning Analysis Services - the presentation that the consumers have requested make the task a bit more considerable.

The consumers state that they want the presentation to appear with Warehouse Country (U.S.A. only, in the current request) to appear in the "x-" axis and Warehouse City, represented by a "City, State" combination, to appear across the "y-" axis. At our request, to confirm our understanding, they present a draft of the desired display that appears as depicted in Table 1.

After confirming our understanding of the business requirements, we agree to build in support for the requirement within the Warehouse cube. Working with a copy of the sample cube, we will make the additions and adjustments to make the report possible. We will then test the results within the Analysis Services cube browser, and then by running a query from an external application, the MDX Sample Application, to simulate the effect we would obtain from within an external reporting application.

I will pursue the use of the structure that we create from a Reporting Services perspective from an article within my Reporting Services series at Database Journal. I invite you to join us in that article to obtain an appreciation for the ease with which we can produce a report similar to the above, thanks to the structure that we will have housed in our cube to support the desired features.