Verify
Results - from a Reporting Perspective
Let's use the MDX
Sample Application to practice setting up a report to display the data as
requested by consumers. This is a great way to get a feel for the adequacy of
the components we have designed for this purpose, as the same MDX we construct
to return the data requested by the information consumers within the Sample
Application will likely be used as the source query within the reporting
application. (We will use the above structural preparation for a report that we
will construct using MSSQL Server Reporting Services as the "front end"
in an article we publish later, in the MSSQL Server
Reporting Services series here at Database Journal.)
We recall that the
requirement was that we display U.S.A. on the "x-" axis and Warehouse City, represented by a "City,
State" combination, to appear across the "y-" axis. The
consumers also only wish to present the Warehouse Profit and % Profit
Contribution along the "x-" axis, which, as we will see, is
easily accommodated with the other requirements. Let's initialize the MDX Sample Application, and take the following steps:
1.
Start the MDX
Sample Application.
We are
initially greeted by the Connect dialog, depicted in Illustration 35.
Illustration 35:
The Connect Dialog for the MDX Sample Application
The
illustration above depicts the name of my server, MOTHER1, and properly
indicates that we will be connecting via the MSOLAP provider (the
default).
The MDX
Sample Application window appears.
3.
Ensure that FoodMart
2000 is selected as the database name in the DB box of the toolbar.
4.
Select the Crosstab
Reporting cube in the Cube drop-down list box.
5.
Click File
-> New to open a blank Query pane.
The MDX
Sample Application window should resemble that shown in Illustration 36,
complete with the information from the Warehouse cube displaying in the Metadata
tree (left section of the Metadata pane).
Illustration 36: The MDX Sample Application Window
(Compressed View)
We
will begin creating our query with a focus on returning results in the same
general formation as the Data View we left in the Cube Editor. We
will then move from the core query to a more precise presentation of the data
as it was requested by the information consumers (and for which they provided a
"draft" that was reproduced in Table 1 above).
We
will initially retrieve our two axes, containing members of the same dimension
(Store) with the primary Warehouse Profit measure, by taking the
following steps.
1.
Create the
following new query:
-- ANSYS34-1 Core Query - Same Dim in Both Axes of Crosstab
SELECT
{ [Store].[All Stores].[USA]} ON COLUMNS,
{[Store Location].Members} ON ROWS
FROM
[CROSSTAB REPORTING]
WHERE
([Time].[1998], [Measures].[Warehouse Profit])
2.
Execute the
query using the Run Query button.
The
results dataset appears as depicted in Illustration 37.
Illustration 37:
The Results Dataset - Core Query Results - Same Dim in Both Axes
3.
Save the query
as ANSYS34-1, in a convenient location, leaving it open for the next
steps.
In the
results dataset we note that all store locations appear, and that there are
several "blank" cells. This is largely a result of our having asked
for "USA only" in our column
specification. Because the column is retrieving USA only, values at the intersections of USA and non-domestic locations are
null.
We will
further polish the results set by ridding it of the nulls, with the NON
EMPTY() keyword, in the next steps, both
for cosmetic effects (making the dataset more compact), and for eliminating
potential confusion among the information consumers. We will do so within a
modification to our last query.
4.
Change the
comment line in the query to read as follows:
-- ANSYS34-2 Core Query - Eliminating "Empties"
5.
Save the query
as MDX34-2, to protect MDX34-1.
6.
On line 4 of
the query, enclose {[Store Location].Members} in parentheses ("(
)").
7.
Place the
cursor before ({[Store Location].Members}) ON ROWS.
8.
Type the
following keyword phrase in front of the new left parenthesis:
NON EMPTY
So
that the entire line appears as follows:
NON EMPTY({[Store Location].Members}) ON ROWS
The
query, with our modifications to the ON ROWS specification, resembles
that shown in Illustration 38.
Illustration 38:
Query with Modifications in the Query Pane (Compressed View)
9.
Execute the
query, using the Run Query button, once again.
The
query executes, once again, and the results dataset appears as depicted in Illustration 39.
Illustration 39: Results
Dataset - Empties (and Non-Domestic Location Members) Removed
10.
Resave the
query as MDX34-2.
Because
we will now rearrange a couple of things to bring the query output into
alignment with the presentation requested, we will create a new query at this
stage. The core axes portion remains the same, but we will be using a CROSSJOIN()
function to combine our measures with the existing dimension in the ON COLUMNS
specification. Moreover, we will be adding the % Profit Contribution
calculated member / measure within those measures.
11.
Click File
-> New to open a blank Query pane.
We
will initially retrieve our two axes, containing members of the same dimension
(Store), with the primary Warehouse Profit measure and the %
Profit Contribution calculated measure, by taking the following steps.
12.
Create the
following new query:
-- ANSYS34-3 Final Query Supporting Presentation Requirements
SELECT
CROSSJOIN({[Store].[Store Country].[USA]},{[Measures].[Warehouse Profit],
[Measures].[% Profit Contribution]}) ON COLUMNS,
NON EMPTY({[Store Location].Members}) ON ROWS
FROM
[CROSSTAB REPORTING]
WHERE
([Time].[1998])
13.
Execute the
query using the Run Query button.
The
results dataset appears as depicted in Illustration 40.
Illustration 40:
The Results Dataset - Supporting Presentation Requirements
14.
Save the query
as ANSYS34-3.
We
have now provided for all aspects of the requested data presentation. Our
structural changes, coupled with the appropriate query within the respective
reporting application, will allow for the generation of a report that resembles
the draft we received from the information consumers. We can also support
complementary charts and other report objects that might rely upon the values
we have retrieved.
» 