### The CrossJoin() Function

According to the *Analysis Services Books Online*, the **CrossJoin()**** **function "returns the cross product of two sets." **CrossJoin()** is particularly handy as it allows us to prepare data for presentation in a matrix format - the need to return "all possible combinations" of the members / tuples of two or more sets is common.

**CrossJoin()** is powerful, as it allows us, in effect, to nest one dimension inside another, but in a direct manner that enables the avoidance of a great deal of extra coding that might be required to obtain the same result through other means. A sample **CrossJoin()** result set, used together with the **NON EMPTY** keyword (discussed later in this article) in our last lesson as a preview, appears in **Illustration 1**.

We will examine the syntax for the** CrossJoin()** function, mention a means for removing empty rows / columns from the results dataset it retrieves, and then undertake practice examples built around hypothetical business needs that illustrate logical uses for the function. We will extend the examples to perform exclusion of *empty combinations *in a query we construct, to afford us a scenario for examining an approach in that regard.

*Discussion*

As we have already discovered, the **CrossJoin() **function returns a cross product of the members of two different sets. The result of the use of such a cross product is the placement of two dimensions on the same axis, be it columns or rows, where **CrossJoin()** creates a new set made up of all possible combinations (after the manner of a "Cartesian product") of the original sets' members.

**CrossJoin() **handles only two dimensions at a time, so if we need to perform a cross product across *more* than two dimensions, we must *nest* the functions to realize our aims (we will see an example of this in our practice exercises). Furthermore, the two sets upon which we are performing a cross join must exist in separate dimensions. We can suppress empty columns / rows for the results dataset with the inclusion of **NON EMPTY** before the **CrossJoin()** function, which effectively transforms it to **NonEmptyCrossJoin()**, a function in its own right.

Because the **CrossJoin()** function returns a set that consists of the matches between every member of the first set and each member of the second set, it is important to realize that large datasets can be returned, and that performance can suffer as a result. If, for instance, we cross join two sets containing 200 members each, our results dataset will contain 40,000 tuples.

For many cubes, the **CrossJoin()** function returns datasets with empty measures associated with a significant number of the resulting tuples. We can often experience a performance boost with a **NonEmptyCrossJoin()**, whose action is to eliminate the tuples with which no data is associated from the results dataset. **NonEmptyCrossJoin()** also provides other features that can facilitate more efficient processing, as we shall see in a subsequent article that is devoted to **NonEmptyCrossJoin()**.

Let's look at some syntax illustrations to further clarify the operation of **CrossJoin()**.

*Syntax*

Syntactically, the sets between which we wish to return a cross product are specified within the parentheses to the right of **CrossJoin**. The syntax is shown in the following string.

CrossJoin(

<<Set1>>, <<Set2>>

)

Contrary to a couple of texts I have come across, the order in which the sets are placed in the **CrossJoin**() function is significant. Placing the sets in different orders often results in a materially different results dataset. The order of tuples in the resulting set depends on the order of **+Set1;** and **+Set2;**, and the order of the members within each set; the order should be considered in the light of its effects upon the presentation of final results.

An alternative syntax simply places an asterisk ("*") between the sets, as shown in the following string:

<<Set1>> * <<Set2>>

The alternate syntax is an extension to MDX in OLE DB 2.0 and greater, housed within MSSQL Server 2000 Analysis Service.

The following example expression illustrates a use of the **Crossjoin()** function. Let's say that a group of FoodMart information consumers, whose data is housed within the **Warehouse** cube, wish to see the total **Units Shipped** by **U.S.** **Warehouse-State **for **each quarter of 1998. **Furthermore, the consumers wish to see the **Units Shipped** in a single column in the report, and the **Quarter / Warehouse-State** information in the row axis of the presentation.

The basic **CrossJoin()** function, which would define the row axis of the result dataset, would be constructed as follows:

CROSSJOIN(
{[Time].[Year].[1998].Children},
{[Warehouse].[Country].[USA].Children}

This is simply expressing that we wish to display the "**Warehouse-State** by **Quarter**" in the row axis, and, assuming that the **Units Shipped** measure was specified for the column axis, our result dataset would be identical to that shown in **Illustration 2**.

Illustration 2: Results Dataset, Syntax Sample, CrossJoin() Function in Row Axis

The results appear to be as expected from a numerical standpoint, as well. Our expectation would be to see a total of *twelve combinations*, every possible combination of **1998 Quarters** and the **U.S. Warehouse-States**, produced by the cross product of **1998's four quarters** (the first of the sets in the syntax sample above), times the number of **Warehouse-States** (three). The twelve combinations are indeed the result, as we see in the illustration above.

**NOTE:** For information surrounding the **.Children** function, see *MDX Member Functions: The "Family" Functions*.

We will practice the use of the **CrossJoin() **function in the section that follows. Moreover, we will explore the use of the function when we need to create multiple cross products (that is, cross joining more than two sets). Finally, we will practice the use of **NON EMPTY** to activate** **the concept of its use in eliminating empty rows / columns from our presentation of **CrossJoin() **results. In each case, we will discuss briefly the results we obtain to reinforce our overall understanding of the **CrossJoin()** function.