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.
Illustration 1: Results of
"Straightforward" Attempt at Same Dim in Both Axes ...
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.
|
USA
|
|
Warehouse Profit
|
% Profit Contribution
|
Total
|
$ xxx,xxx
|
xxx.xx%
|
Bellingham, WA
|
x,xxx
|
xxx.xx%
|
Beverly Hills, CA
|
x,xxx
|
xxx.xx%
|
Bremerton, WA
|
x,xxx
|
xxx.xx%
|
Los Angeles, CA
|
x,xxx
|
xxx.xx%
|
....
|
....
|
...
|
Table 1: Draft of the
Desired Report Layout
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.