Can you please tell me how can I extract my full account hiearachy with all its poperties together in file format from a classic application which can be easily changed back to the format used by outline load utility.
The only option I m finding it to take an export from hp_account hierarchy table and properties table from oracle database but that will be again two separate file.
From EPMA ,I know that we can use the file generator to extract the file in .ads format which is resuable.
Please help me on this
You didn't mention the version, If you are on 184.108.40.206+ 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
Thanks a lot for your reply.
The version we are using in 220.127.116.11 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.
- 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.