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 12, 2004

MDX Essentials: Basic Set Functions: The CrossJoin() Function - Page 3

By William Pearson

Practice

The Basics

To reinforce our understanding of the basics we have covered so far, we will use the CrossJoin() function in a manner that illustrates its operation in the simplest of scenarios: We will construct a simple select query that creates an axis using CrossJoin().

As we have in many of the preceding articles of the series, will use the MDX Sample Application as a platform from which to construct and execute the MDX we examine, and to view the results datasets we obtain.

1.  Start the MDX Sample Application.

2.  Clear the top area (the Query pane) of any queries or remnants that might appear.

3.  Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

4.  Select the Warehouse cube in the Cube drop-down list box.

Let's assume for our practice example that we have received a call from corporate Finance needing some statistics for an upcoming presentation to the stockholders. The Finance information consumers wish to know Warehouse Sales, Cost, and Profit balances for operating years 1997 and 1998, by U. S. States of Store operation.

To rephrase, the objective will be to present three annual measures, Warehouse Sales, Cost and Profit for each of the years 1997 and 1998, broken out by U. S. Store-State. While we can accomplish this by cross joining the Year and State dimensions on either the column or the row axis, the row axis will provide us a more compact presentation. We can easily see that three measures across the columns will result in a narrower matrix than to use the six combinations (two Years times three U.S. States of Store operation) that we would expect to be retrieved by an alternative arrangement of the proposed CrossJoin() function.

Let's construct a simple query, therefore, to return the measures as columns and the cross-joined sets as rows.

5.  Type the following query into the Query pane:


-- MXAS18-1, Basic CrossJoin() Query
SELECT
  { [Measures].[Warehouse Sales], [Measures].[Warehouse Cost],    
      [Measures].[Warehouse Profit]} ON COLUMNS,

   CROSSJOIN (
       {[Time].[Year].Members},
       {[Store].[Store Country].[USA].Children} ) ON ROWS
FROM
   [Warehouse]

6.  Execute the query by clicking the Run Query button in the toolbar.

The Results pane is populated by Analysis Services, and the dataset shown in Illustration 3 appears.


Illustration 3: Result Dataset - Basic CrossJoin() Example

We see the Warehouse Sales, Cost and Profit measures populating the columns across, and the cross-product combinations of Year (from the Time dimension, naturally) and Store-States (from the Store dimension) appearing on the row axis.

7.  Select File -> Save As, name the file MDX18-1, and place it in a meaningful location.

8.  Leave the query open for the next section.

Multiple Cross Products

Next, let's explore the use of a CrossJoin() function in a scenario where a business need exists to cross join more than two sets. We will say, as an illustrative example, that we have been requested to present the measures we retrieved earlier, Warehouse Warehouse Sales, Cost and Profit, once again for operating years 1997 and 1998, by U. S. States of Store operation. The difference this time, however, is that the information consumers in Finance have requested that we present the existing row information by Store Type.

To rephrase, the objective will be to present the three annual measures, Warehouse Sales, Cost and Profit for each of the Store Types (there are six, including the Headquarters classification, which appears to amount to a "non-store" group), by years 1997 and 1998, broken out by U. S. Store-State. Again, a quick analysis of the multiples lead us to conclude that performing our cross joins on the row axis will provide us a more compact presentation. (Six Store Types times two Years times three U.S. States of Store operation is 36 possible combinations, as opposed to the three columns required by the measures, as we placed them in the columns before).

Let's construct the query to return the measures as columns and the cross-joined sets as rows. We can begin with our initial query.

1.  Within the query we have saved as MDX18-1, replace the top comment line of the query with the following:

-- MXAS18-2, Nested CrossJoin() Query

2.  Save the query as MDX18-2, to prevent damaging MDX18-1.

3.  After the following line:


{[Measures].[Warehouse Sales], [Measures].[Warehouse Cost], 
    [Measures].[Warehouse Profit]} ON COLUMNS,

And before the CrossJoin() expression already in place (shown next):


CROSSJOIN(
   {[Time].[Year].Members},
   {[Store].[Store Country].[USA].Children})) ON ROWS

Insert the following expression:


CROSSJOIN(
   {[Store Type].[Store Type].Members},

4.  Insert a close parenthesis ")" just before the ON ROWS keyword.

(This serves to close the "outer" CrossJoin(), within which our original CrossJoin() function is to be "nested.")

The Query pane appears as shown in Illustration 4, with the inserted coding circled in red.


Illustration 4: The Query with Modifications Circled

NOTE: Don't forget the extra closing parenthesis symbol ")".

5.  Execute the query by clicking the Run Query button in the toolbar.

The Results pane is populated, and the dataset shown in Illustration 5 appears.


Illustration 5: Result Dataset - Nested CrossJoin() Query

6.  Re-save the file as MDX18-2.

7.  Leave the query open for the next section.



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