Stupid Planning Queries #10 -- Query all descendants of a given member

I love it when a plan comes together

No, I am not John “Hannibal” Smith although I will admit to a preternatural love of the A-Team as a teenager.  Ah, youth.  What I specifically love is when one of my rants/diatribes/random collections of words/occasionally semi-brilliant idea inspires other people.  It really is awesome.  And a little scary.

My Spanish buddy (okay, I am stretching a word here, but c’mon, as he is the only person I know in Spain surely he is simultaneously my only-ever-communicated-by-email acquaintance, colleague, BFF, and buddy all at the same time) Francisco Amores Torres had a comment on my Planning security query post.  He wanted to know how to modify the query to show all of the members in a hierarchical security definition (think inclusive descendants).  He (not me) figured this out using a recursive query.  To SQL geeks, this is like breathing, but for most of us EPM practitioners, it is somewhat of a challenge.  Ooooh, I thought, I want to know how to do that.  I inspired him (if an incomplete solution can be thought of as inspiration) and he inspired me.  It’s kind of awesome.

Limiting a Planning dimension query

There are times (I will illustrate a reason why in a few blog posts – this is too involved to do all at once) when you maybe only want some of the members in a Planning dimension query and that’s what Francisco’s query does. You probably can think of some reasons yourself such as:  extracting subsets of dimensions for transfer to other applications, comparing bits of hierarchies across applications, or even (hint) using the results of a limited set of members to populate other queries and limit their scope.

How do you do it?

I needed a really simple example, so I exercised my Google-fu and came up with this pretty easy to read and understand (although it is for two tables instead of one) MSDN article.

One note – this is only possible in SQL Server 2005 and above as CTE (Common Table Expressions) are not in earlier releases.

One other note – in Oracle PL/SQL you use the CONNECT BY clause.   

The example

I am querying just the inclusive descendants of the member alias “USA” from the Planning sample application.  Put in your own anchor to get other hierarchies.  Don’t be scared off by the Table Variable – I just needed to get the full output of the dimension into a temporary location so I could use the CTE technique.  If I were going to call this code again and again I’d change that to temporary table and not populate it after the first pass.

/*
    Purpose:     Illustrate the querying of all descendants from any member
    Modified:    29 July 2012, Cameron Lackpour
    Notes:       Can only run on SQL Server 2005 and up as it uses Common Table 
                 Expressions (CTE)
                 -  I'm sure this can be done in Oracle but I have to 
                    have some semblance of a life.
*/
/*    Set up a Table Variable called @Dimension    */
DECLARE @Dimension TABLE (
    Parent varchar(80),
    Child varchar(80),
    Alias varchar(80)
    )
/*    Populate the Table Variable with the dimension from Planning.    */
INSERT INTO @Dimension(Parent, Child, Alias)
SELECT
   PO.OBJECT_NAME AS 'Parent',
   O.OBJECT_NAME AS 'Child',
   -- Use a SQL Subquery to get aliases.
   -- NB --  The overall SELECT from HSP_MEMBER ensures that members with
   --        and without an alias are selected.
   --        ISNULL puts in zero length string in place of NULL
   ISNULL((SELECT OA.OBJECT_NAME
       FROM HSP_ALIAS A
       INNER JOIN HSP_OBJECT OA
       ON A.MEMBER_ID = O.OBJECT_ID AND
       OA.OBJECT_ID = A.ALIAS_ID), '') AS 'Alias'
FROM HSP_MEMBER M
INNER JOIN HSP_OBJECT O
   ON M.MEMBER_ID = O.OBJECT_ID
INNER JOIN HSP_OBJECT PO
   ON O.PARENT_ID = PO.OBJECT_ID
INNER JOIN HSP_ENTITY E
   ON M.MEMBER_ID = E.ENTITY_ID
-- Entity dimension is ALWAYS DIM_ID 33, but its name
-- can vary, so use the ID
WHERE M.DIM_ID = 33
;
/* Now do a recursive query to get all of the children of a given member's alias.    */
WITH MyDimension (CTEParent, CTEChild, CTEAlias, Level)
AS
(
    -- Anchor member
    SELECT Parent, Child, Alias, 0 AS Level
    FROM @Dimension AS d
    WHERE Alias = 'USA'
    UNION ALL
    --    Recursive query
    SELECT Parent, Child, Alias, Level + 1
    FROM @Dimension as d
    INNER JOIN MyDimension AS m
        ON d.Parent = m.CTEChild
)
SELECT CTEParent, CTEChild, CTEAlias, Level
FROM MyDimension

And that produces

 

Just as promised, only the members that are inclusive descendants of USA.  Enjoy the hack.

P.S.  An alternate way of doing it

I have an email list (this is pretty common in consulting companies) that I use to send out various and sundry tips and tricks.  Would you believe it’s called the Stupid Tricks list?  You should, ‘cause that’s what it’s called.  Think of the recipients as the brave/unfortunate souls who get this stuff first; their reaction often determines if I enlighten/pollute the wider EPM world with whatever idiocy I come up with.  

One of these happy(?) few is fellow Developing Essbase Applications coauthor Gary Crisci.  When I sent out this query, Gary pointed out that he had written a blog post called SQL queries for Essbase developers over a year and a half ago on the same subject but from an Essbase (really a generic data warehouse) perspective.  I encourage you to check it out as he goes over a slew of different hierarchical queries.

Gary delves into:

• Parent/child Descendants (so similar to the above although not from a Planning perspective)
• Parent/child to Generations
• Parent/child to Levels
• Generations to Parent/child
• Tagging member properties

I really recommend that you check out his blog as there’s good stuff therein.