Mapping MDX To SQL Statements

SELECT
   CROSSJOIN ({Access, Word}, {Kansas, Buffalo, Topeka, USA, Canada})
      ON COLUMNS,
   {[1994].Qtr1.MEMBERS, [1994].Qtr2, [1994].Qtr3, [1994].Qtr4.MEMBERS} ON ROWS
FROM SalesCube
WHERE (Sales)

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 CROSSJOIN Function.

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

Name1	Name2	Rank
Products.[All].Office.Access	Geography.[All].USA.Kansas	1
Products.[All].Office.Access	Geography.[All].USA.NewYork.Buffalo	2
Products.[All].Office.Access	Geography.[All].USA.Kansas.Topeka	3
Products.[All].Office.Access	Geography.[All].USA	4
Products.[All].Office.Access	Geography.[All].Canada	5
Products.[All].Office.Word	Geography.[All].USA.Kansas	6
Products.[All].Office.Word	Geography.[All].USA.NewYork.Buffalo	7
Products.[All].Office.Word	Geography.[All].USA.Kansas.Topeka	8
Products.[All].Office.Word	Geography.[All].USA	9
Products.[All].Office.Word	Geography.[All].Canada	10

Product	ProdLine	Product.[All]	City	State	Country	Geography.[All]	Rank
Products.[All].Office.Access	Products.[All].Office	Product.[All]	ALL()	Geography.USA.Kansas	Geography.USA	Geography.[All]	1
Products.[All].Office.Access	Products.[All].Office	Product.[All]	Geography.USA.NewYork.Buffalo	Geography.USA.NewYork	Geography.USA	Geography.[All]	2
Products.[All].Office.Access	Products.[All].Office	Product.[All]	Geography.USA.Kansas.Topeka	Geography.USA.Kansas	Geography.USA	Geography.[All]	3
Products.[All].Office.Access	Products.[All].Office	Product.[All]	ALL()	ALL()	Geography.USA	Geography.[All]	4
Products.[All].Office.Access	Products.[All].Office	Product.[All]	ALL()	ALL()	Geography.Canada	Geography.[All]	5
Products.[All].Office.Word	Products.[All].Office	Product.[All]	ALL()	Geography.USA.Kansas	Geography.USA	Geography.[All]	6
Products.[All].Office.Word	Products.[All].Office	Product.[All]	Geography.USA.NewYork.Buffalo	Geography.USA.NewYork	Geography.USA	Geography.[All]	7
Products.[All].Office.Word	Products.[All].Office	Product.[All]	Geography.USA.Kansas.Topeka	Geography.USA.Kansas	Geography.USA	Geography.[All]	8
Products.[All].Office.Word	Products.[All].Office	Product.[All]	ALL()	ALL()	Geography.USA	Geography.[All]	9
Products.[All].Office.Word	Products.[All].Office	Product.[All]	ALL()	ALL()	Geography.Canada	Geography.[All]	10

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, there are 2 nested dimensions.

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, the deepest member 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. Next, 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
   

ROWS Axis Expression

You can use the method described in the section COLUMNS Axis Expression to obtain the following tables R and Rprime:

Name	Rank
Time.[All].1994.Jan	1
Time.[All].1994.Feb	2
Time.[All].1994.Mar	3
Time.[All].1994.Qtr2	4
Time.[All].1994.Qtr3	5
Time.[All].1994.Oct	6
Time.[All].1994.Nov	7
Time.[All].1994.Dec	8

Month	Quarter	Year	Time.[All]	Rank
Time.[All].1994.Jan	Time.[All].1994.Qtr1	1994	Time.[All]	1
Time.[All].1994.Feb	Time.[All].1994.Qtr1	1994	Time.[All]	2
Time.[All].1994.Mar	Time.[All].1994.Qtr1	1994	Time.[All]	3
ALL()	Time.[All].1994.Qtr2	1994	Time.[All]	4
ALL()	Time.[All].1994.Qtr3	1994	Time.[All]	5
Time.[All].1994.Oct	Time.[All].1994.Qtr4	1994	Time.[All]	6
Time.[All].1994.Nov	Time.[All].1994.Qtr4	1994	Time.[All]	7
Time.[All].1994.Dec	Time.[All].1994.Qtr4	1994	Time.[All]	8

Getting Cell Values

This topic has not yet been rated - Rate this topic

The query for getting the cell values is as follows:

SELECT Value, NewRank AS Rank
FROM
   (SELECT Value
   FROM
      (Fact AS F JOIN Cprime AS C ON
         (   C.[Product.[All]] = F.[Product.[All]]
         AND C.ProdLine = F.ProdLine
         AND C.Product = F.Product
         AND C.[Geography.[All]] = F.[Geography.[All]]
         AND C.Country = F.Country
         AND C.State = F.State
         AND C.City = F.City
         AND F.Zipcode = ALL()
         )
      JOIN Rprime AS R ON
         (   R.[Time.[All]] = F.[Time.[All]]
         AND R.Year)  = F.Year
         AND R.Quarter = F.Quarter
         AND R.Month = F.Month
         AND F.Day = ALL()
         )
      )
   WHERE F.Measures = "Sales")
   RANK ROWS AS newrank RANKORDER BY Rank1, Rank2
ORDER BY Rank

来源：MSDN