MDX queries to get Parent/Child and Gen/Lev format (sort of)

I recently stumbled across some interesting MDX functionality.  Unfortunately I have to put the disclaimer right off the bat that sadly this feature may not be of much use given the very poor format of the output, nonetheless this is an interesting way to go about extracting this data and if the user has no other method available to them, it might be helpful.  Perhaps someday Oracle will provide a better facility for formating MDX output and for those of you using MDX behind the scenes to feed a custom app, this should be right up your ally.

The feature I am referring to is the MDX Property Expression.  Basically what this function does is allow you to pull onto your result axis DIMENSION properties for the members you are returning.  The options for what you pull can be quite robust if you structure properly.

So for starters let's say you wanted to query a dimension and return parent child members

You could run the following query:

 SELECT {} ON AXIS(0),
Market.Members DIMENSION PROPERTIES
  PROPERTY_EXPR
   (
    Market,
    MEMBER_NAME,
    Parent(Currentaxismember()),
   "Parent"
   )
ON ROWS
FROM Sample.Basic;

And you would get the following results:

  Axis-1                                            Axis-1.properties                               
+-------------------------------------------------+-------------------------------------------------
 (Market)                                          (Parent = null, type: STRING, )                 
 (East)                                            (Parent = Market, type: STRING, )               
 (New York)                                        (Parent = East, type: STRING, )                 
 (Massachusetts)                                   (Parent = East, type: STRING, )                 
 (Florida)                                         (Parent = East, type: STRING, )                 
 (Connecticut)                                     (Parent = East, type: STRING, )                 
 (New Hampshire)                                   (Parent = East, type: STRING, )                 
 (West)                                            (Parent = Market, type: STRING, )               
 (California)                                      (Parent = West, type: STRING, )                 
 (Oregon)                                          (Parent = West, type: STRING, )                 
 (Washington)                                      (Parent = West, type: STRING, )                 
 (Utah)                                            (Parent = West, type: STRING, )                 
 (Nevada)                                          (Parent = West, type: STRING, )                 
 (South)                                           (Parent = Market, type: STRING, )               
 (Texas)                                           (Parent = South, type: STRING, )                
 (Oklahoma)                                        (Parent = South, type: STRING, )                
 (Louisiana)                                       (Parent = South, type: STRING, )                
 (New Mexico)                                      (Parent = South, type: STRING, )                
 (Central)                                         (Parent = Market, type: STRING, )               
 (Illinois)                                        (Parent = Central, type: STRING, )              
 (Ohio)                                            (Parent = Central, type: STRING, )              
 (Wisconsin)                                       (Parent = Central, type: STRING, )              
 (Missouri)                                        (Parent = Central, type: STRING, )              
 (Iowa)                                            (Parent = Central, type: STRING, )              
 (Colorado)                                        (Parent = Central, type: STRING, )

Some quick find and replace action in Excel (I smell a macro) could easily clean this up.

Let's say you wanted to query Generations

SELECT {} ON AXIS(0),
Market.Levels(0).Members DIMENSION PROPERTIES
  PROPERTY_EXPR
   (
    Market,
    MEMBER_NAME,
    Ancestor
     (
      Currentaxismember(),
      Currentaxismember().Dimension.Generations(1)
     ),
   "Parent_Generation_1"
   ),
  PROPERTY_EXPR
   (
    Market,
    MEMBER_NAME,
    Ancestor
     (
      Currentaxismember(),
      Currentaxismember().Dimension.Generations(2)
     ),
   "Parent_Generation_2"
   )
ON ROWS
FROM Sample.Basic;

Your results would look like this:

  Axis-1                                                                                              Axis-1.properties                                                                                
+---------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------
 (New York)                                                                                          (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = East, type: STRING, )         
 (Massachusetts)                                                                                     (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = East, type: STRING, )         
 (Florida)                                                                                           (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = East, type: STRING, )         
 (Connecticut)                                                                                       (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = East, type: STRING, )         
 (New Hampshire)                                                                                     (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = East, type: STRING, )         
 (California)                                                                                        (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = West, type: STRING, )         
 (Oregon)                                                                                            (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = West, type: STRING, )         
 (Washington)                                                                                        (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = West, type: STRING, )         
 (Utah)                                                                                              (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = West, type: STRING, )         
 (Nevada)                                                                                            (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = West, type: STRING, )         
 (Texas)                                                                                             (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = South, type: STRING, )        
 (Oklahoma)                                                                                          (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = South, type: STRING, )        
 (Louisiana)                                                                                         (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = South, type: STRING, )        
 (New Mexico)                                                                                        (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = South, type: STRING, )        
 (Illinois)                                                                                          (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = Central, type: STRING, )      
 (Ohio)                                                                                              (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = Central, type: STRING, )      
 (Wisconsin)                                                                                         (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = Central, type: STRING, )      
 (Missouri)                                                                                          (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = Central, type: STRING, )      
 (Iowa)                                                                                              (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = Central, type: STRING, )      
 (Colorado)                                                                                          (Parent_Generation_1 = Market, type: STRING, Parent_Generation_2 = Central, type: STRING, ) 

Your challenge here is to manage the column width of your output.  There is a MaxL command to set the output column width

SET column_width 100;

Fortunately there is no maximum to the length of a column, but I would think a deep hierarchy might be a bit ugly!

Another challenge with the generation format is that you would have to know in advance how many generations your dimension has.

To export in level format, just replace Generations for Levels in the query.



I don't think this method replaces a nice tool like the outline extractor.  But if you are in a jam and wanted to extract a dim very quickly and see the hierarchy, this method could be useful.  Of course I have to wonder why outline extraction to a useful format is not native functionality in Essbase.  I think they made a nice step with the outline extraction feature in MaxL, however it dumps to an XML file that is not of much use unless you further process the file.  If anyone from Oracle reads this - please add functionality to extract an outline to a flat file in a format that you could easily push back via a load rule to build another outline.  We have needed this functionality for years.  And while your at it, please provide a client for running MDX queries and returning the results in a useful format.

Thanks, hope you enjoy!