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 ROWSFROM SalesCubeWHERE (Sales)

COLUMNS Axis Expression

The COLUMNS axis expression consists of the CROSSJOIN function applied on two literal sets. Applying the mappings described inLiteral Sets generates two virtual tables. These virtual tables are used as input to the SQL statements described inCROSSJOIN 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 RankFROM      (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 RankFROM      (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 RankFROM   (C1 CROSS JOIN C2) RANK ROWS AS NewRankRANKORDER 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 RankFROM   (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, Rank2ORDER BY Rank

来源：MSDN