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 4

By William Pearson

We have achieved our objective of nesting the first function within the second, and thereby in cross-joining three sets from three separate dimensions. We note that there are numerous blank cells; it is easy to see that the reason for many of the "empties" is the fact that, as we have mentioned, the Headquarters Store Type is not a standard operating classification, as opposed to the other Store Types, which actually generate revenues, operating costs, and, therefore, profits / losses.

We could use a Filter() function to assist us, or we could simply stipulate the Store Types that we wish to show individually in our query, versus using the .Members function, but both these approaches would constitute more complication - particularly since the first would still leave some "blanks" even after removing Headquarters from the equation, and necessitate additional coding.

NOTE: See Basic Set Functions: The Filter() Function in this series for a detailed look at the Filter() function, and MDX Members: Introducing Members and Member for a hands-on introduction to .Members.

This offers us an opportunity to examine one final facet of the use of the CrossJoin() function, however, as a quick means to our end. We will make use of the NonEmptyCrossJoin() function to suppress empty rows and columns in our result dataset.

Excluding Empty Columns and Rows

While we will merely use it to practice suppressing empty rows and columns in a small practice example, NonEmptyCrossJoin() is a function in its own right in the MDX collection. Where we will simply insert the words Non Empty in front of an existing CrossJoin() function to see the results, the NonEmptyCrossJoin() function will warrant more in-depth examination. We will devote an article to it exclusively in the near future.

For now, let's simply explore a way we can use the operation of the function to clean up our result dataset a bit. We will reuse the query from our last step, as it presents an ideal scenario from which to bring about the suppression of empty columns and rows.

8.  Within the query we have saved as MDX18-2, insert the following:

NON EMPTY

Just after the ON COLUMNS keyword in the query. That is, place it between:


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

9.  Replace the top comment line of the query with the following:

-- MDX18-3:  Nested CrossJoin() Query  w NON EMPTY

The Query pane appears as shown in Illustration 6.


Illustration 6: The Query with Modifications Circled

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

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


Illustration 7: Result Dataset - Empty Rows and Columns Eliminated

The results are compacted - the empty rows and columns have been eliminated.

11.  Save the file as MDX18-3.

We have reached our objectives, both in meeting the consumer requirements and in suppressing the empty rows and columns to make the presentation of our dataset more compact. As we have stated, we will return to the NonEmptyCrossJoin() in a later article, where we can devote more time to its additional characteristics and considerations.

Summary ...

In this lesson, we explored the highly popular CrossJoin() function. We discussed the general purpose of the function, to allow us to produce all possible combinations of two sets, and cited its common use to "collapse" two or more dimensions onto a single axis for purposes of presenting multidimensional data in a two-dimensional matrix.

We introduced CrossJoin(), commenting upon its operation and touching upon performance considerations at a general level. Next, we examined the syntax surrounding the function. We then began practice exercises to illustrate the operation of the function, within hypothetical scenarios where we described a business need, and then set about solving the need with a straightforward MDX query that exploited the CrossJoin() function. Finally, we briefly discussed the results datasets we obtained in the practice examples.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.



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


















Thanks for your registration, follow us on our social networks to keep up-to-date