5 Replies Latest reply on Jan 29, 2013 6:31 AM by 959803

    Account dimension extract from classic application

      Hi Gurus,

      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

        • 1. Re: Account dimension extract from classic application
          You didn't mention the version, If you are on 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


          1 person found this helpful
          • 2. Re: Account dimension extract from classic application
            Hi John,

            Thanks a lot for your reply.
            The version we are using in 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.
            • 3. Re: Account dimension extract from classic application
              HI jOhn,

              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",

              CASE M.CONSOL_OP1
              WHEN 0 THEN '+'
              WHEN 1 THEN '-'
              WHEN 2 THEN '*'
              WHEN 3 THEN '/'
              WHEN 4 THEN '%'
              WHEN 5 THEN '~'
              WHEN 6 THEN '^'
              END AS "Operator",
              CASE M.DATA_STORAGE
              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",
              CASE M.TWOPASS_CALC
              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
              M.MEMBER_ID = F.MEMBER_ID
              WHERE M.DIM_ID = 32

              Edited by: 956800 on 24-Jan-2013 05:28
              • 4. Re: Account dimension extract from classic application
                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
                1 person found this helpful
                • 5. Re: Account dimension extract from classic application
                  Hi Ram,

                  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.