Join Compatibility

OLE DB Programmer's Reference

As discussed in "Establishing Cube Context," 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   To perform a join, it is not necessary to specify the relationship between cubes, because joining is always by common dimensions (dimensions with the same name). 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 the section "Hypercubes and Multicubes" in Chapter 22: OLE DB for OLAP Concepts.

The semantics of joining by common dimensions, called a natural join, can be expressed as follows:

1. Let the notation X(A, B, C) indicate a cube X that is composed of dimensions A, B, and C.
2. Let X(A, B, C) and Y(C, D, E) be two cubes with the common dimension C.
3. If X.C and Y.C do not have an identical set of members, then X and Y cannot be joined.
4. 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). And 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).

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.

1998-2001 Microsoft Corporation. All rights reserved.