EPMA - SQL Query to get Hierarchy in Parent Child format (+Bonus query)

There was some discussion on the Essbase discussion board about how to build a query that would return the EPMA hierarchy in parent/child format.

Not one to refuse a challenge (I actually can't) I had a go and produced the following using Oracle SQL:


SELECT p.C_MEMBER_NAME,c.C_MEMBER_NAME FROM (
  SELECT  
    I_PARENT_MEMBER_ID, 
    I_CHILD_MEMBER_ID,
    I_LIBRARY_ID,
    I_DIMENSION_ID,
    ROWNUM AS OrderId 
  FROM (
    SELECT       
        I_PARENT_MEMBER_ID, 
        I_CHILD_MEMBER_ID, 
        r.I_LIBRARY_ID,
        d.I_DIMENSION_ID,
        d.I_TOP_MEMBER_ID
    FROM DS_RELATIONSHIP r
    INNER JOIN DS_DIMENSION d ON 
        r.I_DIMENSION_ID = d.I_DIMENSION_ID AND 
        r.I_LIBRARY_ID = d.I_LIBRARY_ID
    INNER JOIN DS_APPLICATION a ON  
        a.I_LIBRARY_ID = d.I_LIBRARY_ID AND    
        COALESCE(d.I_PARENT_APPLICATION_ID,1)=a.I_APPLICATION_ID
    WHERE
      r.I_LIBRARY_ID=1 AND
      a.C_APPLICATION_NAME='Master' AND -- Application name here. master = shared library
      d.C_DIMENSION_NAME='Period' -- Dimension name here
  ) MemebrIDs
  START WITH I_PARENT_MEMBER_ID=I_TOP_MEMBER_ID
  CONNECT BY PRIOR I_CHILD_MEMBER_ID=I_PARENT_MEMBER_ID
) Hierarchy 
INNER JOIN DS_MEMBER c ON 
  Hierarchy.I_CHILD_MEMBER_ID = c.I_MEMBER_ID AND 
  Hierarchy.I_LIBRARY_ID = c.I_LIBRARY_ID AND 
  Hierarchy.I_DIMENSION_ID=c.I_DIMENSION_ID
INNER JOIN DS_MEMBER p ON 
  Hierarchy.I_PARENT_MEMBER_ID = p.I_MEMBER_ID AND 
  Hierarchy.I_LIBRARY_ID = p.I_LIBRARY_ID AND  
  Hierarchy.I_DIMENSION_ID=p.I_DIMENSION_ID 
ORDER BY OrderId

Output:

PARENT	CHILD
Period	D-T-D
Period	W-T-D
Period	M-T-D
Period	Q-T-D
Period	P-T-D
Period	S-T-D
Period	Y-T-D
Period	H-T-D
Period	BegBalance
Period	YearTotal
YearTotal	Q1
Q1	Jul
Q1	Aug
Q1	Sep
YearTotal	Q2
Q2	Oct
Q2	Nov
Q2	Dec
YearTotal	Q3
Q3	Jan
Q3	Feb
Q3	Mar
YearTotal	Q4
Q4	Apr
Q4	May
Q4	Jun
Period	[Year]

Notes:

This query uses the CONNECT BY clause to build a 'parent, child' resultset based on the top level member and some input parameters. Once I've built the parent/child hierarchy based on the member IDs I then go to the member tables to look up the actual member names. There are joins on the dimension and application tables to ensure we get the correct results as a member can exist in multiple dimensions and a dimension can exist in multiple applications.

The input parameters are  the following (i don't think there is a need to change library_id)

• C_APPLICATION_NAME - The name of the application you want to use. Put 'Master' here to use the Shared Library
• C_DIMENSION_NAME - The name of the dimension you want to use

Bonus Query - List all EPMA apps and how many times they have been deployed

 This code works in Oracle. I might update the post with some MSSQL code later (or something that works in both)


SELECT
   SUBSTR(SUBSTR(REPLACE(c_descr,' Called from Batch',''),INSTR(c_descr,':')+2,8),1,8) AS AppName,
   COUNT(*)
FROM JM_Job
WHERE
   e_job_status = 'Completed'
  AND
   SUBSTR(c_descr, 1,8) IN ('deploy :','redeploy')
GROUP BY
   SUBSTR(SUBSTR(REPLACE(c_descr,' Called from Batch',''),INSTR(c_descr,':')+2,8),1,8)
ORDER BY COUNT(*);
  

Post in the comments if you have any feedback or problems with the queries as it is possible there are strange EPMA circumstances that require special logic that I didn't encounter testing the above.

Update: Fixed/updated the bonus query and prettied the SQL slightly.