HIERARCHIZE

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.

Sets with Dimensionality = 1

Consider HIERARCHIZE(S), where S = {Kansas, USA, Canada, Buffalo, Topeka}

1. Gather information:
   1. As detailed in "Literal Sets," make 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 may be no need to do this because the input set to HIERARCHIZE may be derived from another expression.

   2. Use the following query to verify that all members belong to the same dimension. If the returned count is greater than 1, then it is an invalid set.

      SELECT COUNT DISTINCT COMPONENT(S.Name, -1)
      FROM S
      

   3. Get the dimension name by using this query:

      SELECT DISTINCT COMPONENT(S.Name, -1)
      FROM S
      

   4. Get the level number of each level represented in the input set:

      SELECT DISTINCT LEVEL(S.Name)
      FROM S
      

2. The objective is to create a table T that looks like this:

For each member of set S, there should be a RANKX column, which contains the rank of each of its ancestors. To get the hierarchized set from T, just use this:

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 COUNTRY and CITY level, but none from the STATE level.

To do this, you:

1. Define a table that has elements of set S and its levelo:

   CREATE LOCAL TEMPORARY VIEW S1(Name)
   AS
   SELECT S.Name
   FROM S JOIN members AS M ON(S.Name = M.Level_Name)
   

2. Now make a UNION of three SELECT operations (three is the level of the lowermost member in S):

   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
   

   Note   The WHERE clause on each UNION iterates from Level = 0 through the level of the lowermost member in S1. Note also that the ANCESTOR function in the FROM iterates from 1 through 1 + the level of the lowermost member in S1.

Generalizing for Sets with Arbitrary Dimensionality

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:

1. Let S(Name1, Name2, Rank) be the table associated with the set S.
   
   
2. Let S1, S2 be tables:
   • First step:

     CREATE LOCAL TEMPORARY VIEW S1(Name, Rank)
     AS
     SELECT DISTINCT Name1, Rank FROM S
     

   • Second step:

     CREATE LOCAL TEMPORARY VIEW S2(Name, Rank)
     AS
     SELECT DISTINCT Name2, Rank FROM S
     

3. HIERARCHIZE S1 and S2 as explained in "Sets with Dimensionality = 1." Let the resulting hierarchized sets be D1 and D2.
   
   
4. Now use the following query to yield the hierarchized set:

   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.