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.
This comment has been removed by a blog administrator.
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDelete