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_IDINNER 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 OrderIdOutput:
| 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.
