MDAC 2.5 SDK - OLE DB Programmer's Reference
Chapter 26: Mapping MDX to SQL Statements

COLUMNS Axis Expression

The COLUMNS axis expression consists of the CROSSJOIN function applied on two literal sets. Applying the mappings described in "Literal Sets" generates two virtual tables. These virtual tables are used as input to the SQL statements described in the section "CROSSJOIN Function."

The result of these operations yields the following two tables (denoted as Table C and Table CPrime):

Cprime is obtained from C by using the following steps:

1. Find out the number of nested dimensions in the axis expression:

   SELECT COUNT DISTINCT COMPONENT(Name, -1) FROM C
   

   In the current example, this is 2.

2. Find the names of each dimension:

   SELECT DISTINCT COMPONENT(Name, -1) FROM C
   

   In the current example, this is Products, Geography.

3. For each dimension, find out the deepest member in the axis expression:

   SELECT MAX(LEVEL(Name)) FROM C GROUP BY COMPONENT(Name, -1)
   

   In the current example, this is 3 for Geography and 2 for Products.

4. Create two tables—C1 and C2. C1 has all members in C from Products, and C2 has all members in C from Geography.

   CREATE LOCAL TEMPORARY VIEW C1(Name, Rank) AS
   
       SELECT Name, NewRank AS Rank 
   FROM 
           (SELECT Name1 FROM C WHERE COMPONENT(Name, -1) = "Products"
           RANK ROWS AS NewRank RANKORDER BY Rank)
   
   CREATE LOCAL TEMPORARY VIEW C2(Name, Rank) AS
   
       SELECT Name, NewRank AS Rank 
   FROM 
           (SELECT Name2 FROM C WHERE COMPONENT(Name, -1) = "Geography"
            RANK ROWS AS NewRank RANKORDER BY Rank)
   

5. Now create Cprime:

   SELECT Product AS COMPONENT(C1.Name, 2), 
       ProdLine AS COMPONENT(C1.Name, 1), 
       Product.[All] AS COMPONENT(C1.Name, 0),
       City AS COMPONENT(C2.Name, 3),
       State AS COMPONENT(C2.Name, 2),
       Country AS COMPONENT(C2.Name, 1),
       Geography.[All] AS COMPONENT(C2.Name, 0)
       NewRank AS Rank
   FROM 
       (C1 CROSS JOIN C2) RANK ROWS AS NewRank 
   RANKORDER BY C1.Rank, C2.Rank