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 (
ROWNUM AS OrderId
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
a.C_APPLICATION_NAME='Master' AND -- Application name here. master = shared library
d.C_DIMENSION_NAME='Period' -- Dimension name here
START WITH I_PARENT_MEMBER_ID=I_TOP_MEMBER_ID
CONNECT BY PRIOR I_CHILD_MEMBER_ID=I_PARENT_MEMBER_ID
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
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
ORDER BY OrderId
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 deployedThis code works in Oracle. I might update the post with some MSSQL code later (or something that works in both)
SUBSTR(SUBSTR(REPLACE(c_descr,' Called from Batch',''),INSTR(c_descr,':')+2,8),1,8) AS AppName,
e_job_status = 'Completed'
SUBSTR(c_descr, 1,8) IN ('deploy :','redeploy')
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.