This content has been marked as final. Show 5 replies
You didn't mention the version, If you are on 22.214.171.124+ 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.html1 person found this helpful
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
Thanks a lot for your reply.
The version we are using in 126.96.36.199 so the outlineload will not work so the only option left is to query at oracle datbase to get the details which will take time.
Thanks I will try the option suggested by you.
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
There is a way you can extract Essbase outline members using outline extractors. You can also simply zoom in to all levels using add-in if you don't need any parent child relation in separate columns.1 person found this helpful
- Ram Reddy
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.