dcsimg

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

April 12, 2004

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.

MDX Essentials Series
The LEVEL_NUMBER Member Property
The LEVEL_UNIQUE_NAME Intrinsic Member Property
Intrinsic Member Properties: The HIERARCHY_UNIQUE_NAME Property
Intrinsic Member Properties: The DIMENSION_UNIQUE_NAME Property
Further Combination of BottomCount() with Other MDX Functions
Combine BottomCount() with Other MDX Functions to Add Sophistication
Basic Set Functions: The BottomCount() Function, Part I
Intrinsic Member Properties: The MEMBER_VALUE Property
Intrinsic Member Properties: The MEMBER_UNIQUE_NAME Property
Intrinsic Member Properties: The MEMBER_NAME Property
Intrinsic Member Properties: The MEMBER_KEY Property
Intrinsic Member Properties: The MEMBER_CAPTION Property
Set Functions: The StripCalculatedMembers() Function
Set Functions: The AddCalculatedMembers() Function
MDX Numeric Functions: The Min() Function
MDX Numeric Functions: The Max() Function
Set Functions: The .AllMembers Function
MDX Essentials: Set Functions: The MeasureGroupMeasures() Function
String Functions: The .Properties Function, Part II
String Functions: The .Properties Function
Logical Functions: IsGeneration(): Conditional Logic within Filter Expressions
MDX Scripting Statements: Introducing the Simple CASE Statement
Logical Functions: IsGeneration(): Conditional Logic within Calculations
Logical Functions: IsAncestor(): Conditional Logic within Filter Expressions
MDX Clauses and Keywords: Use HAVING to Filter an Axis
Logical Functions: IsAncestor(): Conditional Logic within Calculations
Logical Functions: IsSibling(): Conditional Logic within Filter Expressions
Logical Functions: IsSibling(): Conditional Logic within Calculations
MDX Operators: The IsLeaf() Operator: Conditional Logic within Filter Expressions
MDX Operators: The IsLeaf() Operator: Conditional Logic within Calculations
MDX Numeric Functions: The .Ordinal Function
Other MDX Entities: Perspectives
MDX Operators: The IS Operator
MDX Set Functions: The Distinct() Function
MDX Set Functions: The ToggleDrillState() Function
Set Functions: The DrillUpLevel() Function
Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions
MDX Set Functions: DrillDownLevel()
MDX Set Functions: The DRILLUPMEMBER() Function
MDX Essentials: Set Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions
MDX Essentials : Set Functions: The DRILLDOWNMEMBER() Function
MDX Essentials: Drilling Through with MDX: The DRILLTHROUGH Statement
MDX Essentials: String Functions: The .UniqueName Function
MDX Essentials: String Functions: The .Name Function
MDX Essentials: String / Numeric Functions: The CoalesceEmpty() Function
MDX Essentials: Basic Set Functions: The TopCount() Function, Part II
MDX Essentials: Basic Set Functions: The TopCount() Function, Part I
MDX Essentials: Enhancing CROSSJOIN() with Calculated Members
MDX Essentials: Set and String Functions: The GENERATE() Function
MDX Essentials: The CROSSJOIN() Function: Breaking Bottlenecks
MDX Essentials: String / Numeric Functions: More on the IIF() Function
MDX Essentials: String / Numeric Functions: Introducing the IIF() Function
MDX Essentials: Logical Functions: The IsEmpty() Function
MDX Essentials: Basic Set Functions: The EXTRACT() Function
MDX Essentials: Numeric Functions: Introduction to the AVG() Function
MDX Essentials: Basic Member Functions: The .Item() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Subset() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Tail() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Head() Function
MDX Essentials: Basic Set Functions: The CrossJoin() Function
MDX Essentials: Basic Numeric Functions: The Count() Function
MDX Essentials: Basic Set Functions: The Filter() Function
MDX Essentials: Basic Set Functions: The EXCEPT() Function
MDX Essentials: Basic Set Functions: The Intersect() Function
MDX Essentials: Basic Set Functions: The Union() Function
MDX Essentials: Basic Set Functions: The Order() Function
MDX Essentials - MDX Time Series Functions, Part III: The LastPeriods() and ParallelPeriod() Functions
MDX Time Series Functions, Part II: The OpeningPeriod () and ClosingPeriod() Functions
MDX Essentials - MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions
MDX Essentials: MDX Member Functions: "Relative" Member Functions
MDX Member Functions: The Cousin () Function
MDX Essentials: Member Functions: More "Family" Functions
MDX Member Functions: The "Family" Functions
MDX Essentials: MDX Members: Introducing Members and Member
MDX Essentials : MDX Operators: The Basics
MDX Essentials: Structure of the MDX Data Model
MDX at First Glance: Introduction to SQL Server MDX Essentials








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers