Comparing CROSSJOIN and ORDER: A Provider Implementation Note

OLE DB Programmer's Reference

Assume set S is defined as CROSSJOIN(S1, S2), where S1 and S2 are sets consisting of members from a single dimension. (For example, S1 has members from Products and S2 has members from SalesRep.)

Now consider F(S), where F is any set function other than CROSSJOIN.

The following is never true: F(S) = CROSSJOIN(F(S1), F(S2))

The example below illustrates why this is not true in the case of the ORDER function. It is straightforward to come up with similar examples for the other set functions as well.

Suppose that for the Customers Army and Industry:

(Army, Sales) = 20000 (for [all] SalesRep)

(Industry, Sales) = 30000 (for [all] SalesRep)

Also, suppose that for the sales reps Venkatrao and Netz:

(Venkatrao, Sales) = 10000 (to [all] customers)

(Netz, Sales) = 20000 (to [all] customers)

With this data and assuming that ALL is the default member for both Customers and SalesRep dimensions:

ORDER({Army, Industry}, Sales, DESC) = {Industry, Army}

ORDER({Netz, Venkatrao}, Sales, DESC) = {Netz, Venkatrao}

CROSSJOIN(ORDER({Army ), ORDER({Netz, )) = {(Industry, Netz), (Industry, Venkatrao), (Army, Netz), (Army, Venkatrao)}

Now suppose that Venkatrao sold 6000 to Army and 3000 to Industry (and the other 1000 to some other customer) and that Netz sold 1000 to Army and 500 to Industry (and the other 18500 to some other customer). Then:

ORDER(CROSSJOIN({Army, Industry}, {Netz, Venkatrao}), Sales, DESC) = {(Army, Venkatrao), (Industry, Venkatrao), (Army, Netz), (Industry, Netz)}

Therefore, CROSSJOIN(ORDER, ORDER) is not equal to ORDER(CROSSJOIN).

Note   If F(S) = CROSSJOIN(F(S1), F(S2)) were true, implementation would be simplified—because there is no need to implement any set function (except CROSSJOIN) for sets formed out of multiple dimensions. For example, if the set S is defined as CROSSJOIN(S1, S2), the evaluation of HIERARCHIZE(S) could be done as follows:

   CROSSJOIN(HIERARCHIZE(S1), HIERARCHIZE(S2))

1998-2001 Microsoft Corporation. All rights reserved.