Join Compatibility

As previously discussed in chapter 2, the FROM clause is used to specify the name of the source cube. Some providers may support joining cubes that share common dimensions. These providers can specify a comma-delimited list of cubes to be joined in the FROM clause.

Note   It is not necessary to specify the relationship between cubes in order to perform a join because joining is always by common dimensions (dimensions with the same name).

The semantics of joining by common dimensions, called a natural join, are as follows:

1. Two cubes are said to have common dimensions if and only if the dimensions have the same DIMENSION_UNIQUE_NAME. For more information, see “Multiple Hypercubes vs. True Multicubes” in Chapter 2.
   
   
2. Let the notation X(A, B, C) indicate a cube X which is composed of dimensions A, B, and C.
   
   
3. Let X(A, B, C) and Y(C, D, E) be two cubes with the common dimension C.
   
   
4. If X.C and Y.C do not have an identical set of members, then X and Y cannot be joined.
   
   
5. Assuming X.C and Y.C have an identical set of members: The natural join of X and Y results in a cube XY(A, B, C, D, E) such that the dataset

   SELECT XY.A ON COLUMNS, XY.B ON ROWS, XY.C ON PAGES 
   WHERE (D.ALL, E.ALL)
   

   is the same as the cube X(A, B, C).

   The dataset

   SELECT XY.C ON COLUMNS, XY.D ON ROWS, XY.E ON PAGES 
   WHERE (A.ALL, B.ALL)
   

   is the same as the cube Y(C, D, E).

6. The natural join operation associates and commutes (except for the dimension order).

Even though the above rules defined semantics for cubes with three dimensions each and one dimension common, the generalization to other cases is straightforward.

The method of joining cubes described here is similar to a NATURAL JOIN in a relational database system. Vendors can extend the language to support other types of joins.