MDAC 2.5 SDK - OLE DB Programmer's Reference
Chapter 26: Mapping MDX to SQL Statements
The first method discussed is how HIERARCHIZE works for a set whose dimensionality is 1. This method will be generalized to sets with arbitrary dimensionality.
Consider HIERARCHIZE(S), where S = {Kansas, USA, Canada, Buffalo, Topeka}. As detailed in the section "Literal Sets," create two tables—S1 and S2—that contain the fully qualified member names in the sets S1 and S2, respectively.
Note In a full-fledged MDX expression, there might be no need to do this because the input set to HIERARCHIZE might be derived from another expression.
Gather information as follows:
SELECT COUNT DISTINCT COMPONENT(S.Name, -1)
FROM S
SELECT DISTINCT COMPONENT(S.Name, -1)
FROM S
SELECT DISTINCT LEVEL(S.Name)
FROM S
The objective is to create a table T that looks like this:
Name | Level | Rank1 | Rank2 | Rank3 |
Geography.[All].USA.Kansas | 2 | 1 | 14 | NULL |
Geography.[All].USA | 1 | 1 | NULL | NULL |
Geography.[All].Canada | 1 | 3 | NULL | NULL |
Geography.[All].USA.NewYork.Buffalo | 3 | 1 | 27 | 5 |
Geography.[All].USA.Kansas.Topeka | 3 | 1 | 14 | 1 |
For each member of set S, there should be a RANKX column that contains the rank of each of its ancestors. To get the hierarchized set from T, just use the following:
SELECT Name, Rank
FROM
SELECT * FROM T
RANK ROWS AS Rank RANKORDER BY Rank1, Rank2, Rank3
ORDER BY Rank
Note The number of RANKX columns in T is m + 1, where m = maximum level in the set S. This is the case even if there are unrepresented levels in S, such as when there are members from the COUNTRY and CITY levels but none from the STATE level.
To create the table, perform the following steps:
CREATE LOCAL TEMPORARY VIEW S1(Name)
AS
SELECT S.Name
FROM S JOIN members AS M ON(S.Name = M.Level_Name)
Note The WHERE clause on each UNION iterates from Level = 0 through the level of the lowermost member in S1. Also, the ANCESTOR function in the FROM iterates from 1 through 1 + the level of the lowermost member in S1.
SELECT S1.*, M1.Natural_Sort_Rank AS Rank1,
NULL AS Rank2, NULL AS Rank3
FROM S1 JOIN Members AS M1 ON S1.Name = M1.Member_Name
WHERE S1.Level = 0
UNION
SELECT S1.*, M1.Natural_Sort_Rank AS Rank1,
M2.Natural_Sort_Rank AS Rank2, NULL AS Rank3
FROM (S1 JOIN Members AS M2 ON S1.Name = M2.Member_Name)
JOIN Members AS M1 ON ANCESTOR(S1.Name, 1)= M1.Member_Name
WHERE S1.Level = 1
UNION
SELECT S1.*, M1.Natural_Sort_Rank AS Rank1,
M2.Natural_Sort_Rank AS Rank2, M3.Natural_Sort_Rank AS M3
FROM (((S1 JOIN Members AS M3 ON S1.Name = M3.Name)
JOIN Members AS M2 ON ANCESTOR(S1.Name, 2) = M2.Name)
JOIN Members AS M1 ON ANCESTOR(S1.Name, 3) = M1.Name)
WHERE S1.Level = 2
Consider HIERARCHIZE(S), where:
S = {(Kansas, 1996), (Buffalo, 1995.Q4), (USA, 1995.Mar), (Buffalo, 1995), (USA, 1995), (Kansas, 1996.Q4), (Kansas, 1996.Q1), (USA, 1995.Q1)}
The steps in hierarchizing this set are:
CREATE LOCAL TEMPORARY VIEW S1(Name, Rank)
AS
SELECT DISTINCT Name1, Rank FROM S
CREATE LOCAL TEMPORARY VIEW S2(Name, Rank)
AS
SELECT DISTINCT Name2, Rank FROM S
SELECT S.Name1, S.Name2, NewRank as Rank
FROM
(SELECT S.Name1, S.Name2
FROM (S JOIN D1 ON S.Name1 = D1.Name)
JOIN D2 ON S.Name2 = D2.Name)
RANK ROWS AS NewRank RANKORDER BY D1.Rank, D2.Rank
Generalizing this to sets with dimensionality greater than two should be straightforward.
Note Even when none of the ancestors of a member are present in the input set, the sorting is done as if the appropriate ancestor were present. Thus, in the above example, Buffalo and NYC appear after LA because California (the parent of LA) sorts before New York (the parent of Buffalo and NYC).