This content has been marked as final. Show 5 replies
You didn't mention the version, If you are on 220.127.116.11+ then you can use the outline load utility to extract the account metadata, have a read of http://john-goodwin.blogspot.co.uk/2011/04/planning-11121-exporting-metadata.html
If you are on an earlier version check out - http://camerons-blog-for-essbase-hackers.blogspot.co.uk/2011/07/stupid-planning-queries-3-accounts.html
I have tried the follwoing query which ran very good in SQl devloper but in any case I am not able t extract
Valid for plan type as there are three rows for single line.
UDA same reason as above.
KIndly suggest how I can do this.
I used the follwoing query :
PO.OBJECT_NAME AS "Parent",
O.OBJECT_NAME AS "Child",
WHEN 0 THEN '+'
WHEN 1 THEN '-'
WHEN 2 THEN '*'
WHEN 3 THEN '/'
WHEN 4 THEN '%'
WHEN 5 THEN '~'
WHEN 6 THEN '^'
END AS "Operator",
WHEN 0 THEN 'Store Data'
WHEN 1 THEN 'Never Share'
WHEN 2 THEN 'Label Only'
WHEN 3 THEN 'Shared Member'
WHEN 4 THEN 'Dynamic Calc and Store'
WHEN 5 THEN 'Dynamic'
END AS "Storage",
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
END AS "Two Pass",
WHEN M.DATA_TYPE IS NULL THEN 'Unspecified'
WHEN M.DATA_TYPE = 1 THEN 'Currency'
WHEN M.DATA_TYPE = 2 THEN 'Non Currency'
WHEN M.DATA_TYPE = 3 THEN 'Percentage'
WHEN M.DATA_TYPE = 4 THEN 'SmartList'
WHEN M.DATA_TYPE = 5 THEN 'Date'
WHEN M.DATA_TYPE = 6 THEN 'Text'
END AS "Data Type",
F.FORMULA as "Formula",
WHEN E.SRC_PLAN_TYPE = 1 THEN 'Main'
WHEN E.SRC_PLAN_TYPE = 2 Then 'Models'
WHEN E.SRC_PLAN_TYPE = 4 THEN 'Balsheet'
END AS "Source Plan Type",
WHEN E.Time_Balance = 0 THEN 'Flow'
WHEN E.Time_Balance = 1 THEN 'First'
WHEN E.Time_Balance = 2 THEN 'Balance'
WHEN E.Time_Balance = 3 THEN 'Average'
END As "Time Balance"
FROM rbshp1.HSP_MEMBER M
INNER JOIN rbshp1.HSP_OBJECT O
ON M.MEMBER_ID = O.OBJECT_ID
INNER JOIN rbshp1.HSP_OBJECT PO
ON O.PARENT_ID = PO.OBJECT_ID
INNER JOIN rbshp1.HSP_ACCOUNT E
ON M.MEMBER_ID = E.ACCOUNT_ID
LEFT OUTER JOIN rbshp1.HSP_MEMBER_FORMULA F ON
M.MEMBER_ID = F.MEMBER_ID
WHERE M.DIM_ID = 32
Edited by: 956800 on 24-Jan-2013 05:28
Thanks for your response but I don't have outline extractor and I want all teh details of account dimension in column header as below:
Parent Child Alias Data Storage source plan type formula time balance etc etc
I am able to succeed to some extent but not able to extract Alias , valid for plan types and UDAs attached with each member.