## Correction

4/25/2014

Thanks to Chris Webb’s comment (see the comment section). I am making a correction on this blog.

The title of this blog contains a few important words, “from the same dimension”. You might have guessed the opposite side of it is hierarchies “from different dimensions”.

But my correction is not about this. In trying to explain why CROSS JOIN in MDX does not return Cartesian product of two hierarchies from the same dimension, I originally stated:

*With the CROSSJOIN operation in MDX, we get only the combinations that actually have fact data. We now know that out of the 10 colors of Bib-Shorts only the Multi color Bib-Shorts has sales data (fact data). *

*Unlike the SQL CROSS JOIN, where we had to join one fact table and two dimension tables to find out which color of bib-shorts have sales, in MDX, the **CROSSJOIN function automatically took into the consideration of the fact table. I have to say that the twists and turns are for the best. *

I highlighted “**fact table**” above because that is what made my statement incorrect in the context of **two hierarchies from the same dimension**. When we crossjoin two sets of members from **hierarchies from the same dimension**, the fact table(s) does not come into play, rather, it’s the validate combinations from the dimension table(s) that will determine the resulting tuples. In this case, the Multi color is the only color for the Bib-Shorts. This can be verified by joining the two dimension tables, DimProduct and DimProductSubcategory. Here is the SQL query and the result.

Here is the original post.

## Cartesian product can be illustrated by a Cartesian square

Most SQL developers are familiar with the concept of CROSS JOIN and Cartesian product. Cartesian product is the result that is produced from a CROSS JOIN.

In this Wikipedia article, Cartesian product, a Cartesian product is illustrated by a Cartesian square as shown below.

## In SQL Server, CROSS JOIN returns the Cartesian product of rows from tables in the join

Both of the following articles claimed that the SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product.

The above definition of the SQL CROSS JOIN can be illustrated with the following SQL script using the AdventureWorksDW database.

use AdventureWorksDW

go

with color as (

select distinct Color

from dbo.DimProduct

),

sub as (

select distinct EnglishProductSubcategoryName

from dbo.DimProductSubcategory

whereEnglishProductSubcategoryName = ‘Bib-Shorts’

)

select *

from sub CROSS JOIN color

order by

EnglishProductSubcategoryName, Color

–10

To simplify the result set, I am using only one product subcateogry ‘Bib-Shorts’. The above SQL script generates the results shown in the screenshot below. With 1 product category and 10 product colors, the result set is the every possible combination of these two lists, which is 10 (1 * 10) rows.

Keep in mind that the above 10 combinations of the colors and the subcategory do not necessarily have any sales. In order to find witch combinations have sales, we will need to do an INNER JOIN on these 3 tables:

dbo.FactResellerSales or dbo.FactInternetSales

dbo.DimProductSubcategory

dbo.DimProduct

The following 2 SQL queries can be used. It turned out that there is no internet sales for multi color bib-shorts, but there are reseller sales for them.

– There is no Internet Sales for Multi color Bib-Shorts

select *

from dbo.FactInternetSales fact join dbo.DimProduct prod

on fact.ProductKey = prod.ProductKey

join dbo.DimProductSubcategory sub

on prod.ProductSubcategoryKey = sub.ProductSubcategoryKey

wheresub.EnglishProductSubcategoryName = ‘Bib-Shorts’

and prod.Color = ‘Multi’

–0

– There is 756 Reseller Sales for Multi color Bib-Shorts

select *

from dbo.FactResellerSales fact join dbo.DimProduct prod

on fact.ProductKey = prod.ProductKey

join dbo.DimProductSubcategory sub

on prod.ProductSubcategoryKey = sub.ProductSubcategoryKey

wheresub.EnglishProductSubcategoryName = ‘Bib-Shorts’

and prod.Color = ‘Multi’

–756

## In MDX, CROSS JOIN does not return the Cartesian product members from hierarchies

Enter the world of MDX. CROSS JOIN is an important operation in MDX, with some twists and turns.

The MDX query below is the equivalent of the SQL CROSS JOIN in the previous section. However, the result set contains only one row.

select

{ } on 0,

{ CROSSJOIN(

[Product].[Subcategory].[Subcategory].[Bib-Shorts],

[Product].[Color].[Color].members

)

} on 1

from

[Adventure Works]

With the CROSSJOIN operation in MDX, we get only the combinations that actually have fact data. We now know that out of the 10 colors of Bib-Shorts only the Multi color Bib-Shorts has sales data (fact data).

Unlike the SQL CROSS JOIN, where we had to join one fact table and two dimension tables to find out which color of bib-shorts have sales, in MDX, the CROSSJOIN function automatically took into the consideration of the fact table. I have to say that the twists and turns are for the best.

Please refer to the section ‘Finding related members in the same dimension“ in Chapter 5 of our book, MDX with SSAS 2012, to gain more knowledge on this topic and how it is related to the concept of auto-exists and the EXISTS and EXISTING function.

## Three different ways to do crossjoining

In MDX, you can cross join different hierarchies using three different ways. Chris Webb has a blog here, The rather-too-many ways to crossjoin in MDX, which is better than anything I can write about CROSS JOIN.