Friday 15 August 2014

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.