Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Apr 11, 2005

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

By William Pearson

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).

2.  Click OK.

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM