5 Replies Latest reply: Jan 29, 2013 12:31 AM by 959803 RSS

    Account dimension extract from classic application

    959803
      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

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

          Cheers

          John
          http://john-goodwin.blogspot.com/
          • 2. Re: Account dimension extract from classic application
            959803
            Hi John,

            Thanks a lot for your reply.
            The version we are using in 11.1.2.0 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
              959803
              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
              Alias
              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 :

              SELECT
              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",
              CASE
              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",
              Case
              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",
              Case
              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
              • 4. Re: Account dimension extract from classic application
                Rowdy
                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
                • 5. Re: Account dimension extract from classic application
                  959803
                  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.

                  Thanks