8 Replies Latest reply: Oct 3, 2012 12:16 PM by user13130025 RSS

    Export allocated data from HPCM back to HP

    958712
      Hi Gurus,

      I'm very new to HPCM... Our team now is working on a HP and a HPCM model and try to link them together. We're stucked at the point of how to export/feed the allocated data at HPCM back to HP?

      Our scenario is:
      1. we got an expense account with receiving costs from other accounts
      2. We set user can only view and generate reports at HP
      3. Both HP and HPCM dimensions are the same; HPCM dimensions set as "local" for the sake of data integrity at HP
      3. The cost in that expense account after allocation exporting back to HP dimension

      Is our approach correct?
      If yes then how to do the data feed from HPCM to HP? We've tried to set to "shared" but doesn't work. And indeed this approach doesn't fit our model as we just need some accounts values in HPCM back to HP.

      Any ideas?
      Thanks in advance!

      G
        • 1. Re: Export allocated data from HPCM back to HP
          MattRollings
          DATAEXPORT and a Load Rule, Data Synchronization, ODI

          There are many tools that can extract data from Essbase.

          ::EDIT::

          Whoops! I forgot 11.1.2.2 has microcosting which is stored in a relational database.

          What HPCM engine are you using? Standard or Microcosting?

          Edited by: MattRollings on Aug 29, 2012 8:33 AM
          • 2. Re: Export allocated data from HPCM back to HP
            958712
            Thanks for your update!

            We're using 11.1.2.2 standard and yes we have come up some research using dataexport and load rule. But it's mainly extracting from Essbase to HPCM. How about from HPCM to Essbase? Is it the same way?
            • 3. Re: Export allocated data from HPCM back to HP
              MattRollings
              HPCM is bulit on Essbase, so it should not matter. Micro-costing is different, it uses a relational database to do the allocations not a BSO cube.

              DATAEXPORT plus a load rule is probably the simplest and most effective method without using something like ODI.
              • 4. Re: Export allocated data from HPCM back to HP
                955699
                Hi

                This thread here seems quite useful to me as well.

                Other than using data export with a load rule, can other options like using interface table, data synchronization across applications using EPMA be used?

                I have posted a question on the using of interface table to sync data,

                Data sync using interface table

                does it appear that I might have missed some steps?

                K
                • 5. Re: Export allocated data from HPCM back to HP
                  MattRollings
                  You can use data sync, but if you are moving data from one essbase application to another, it can be slow - especially with the multitude of dimensions that HPCM creates.

                  You are usually looking at 40,000 records per second out of planning (which is not fast).

                  I guess I am still missing what you are trying to do. You do not need to use the interface tables, what EXACTLY are you trying to do? If you are trying to seed Driver and Expense data into the HPCM calculation cube (Essbase BSO), just use load rules and DATAEXPORT calc scripts. If you are trying to get data into the staging tables for HPCM, you need to use SQL or an ETL tool.

                  Don't over-engineer the solution... it will end up being slow. Figure out a solution that has the least amount of steps, is efficient, and will scale well.
                  • 6. Re: Export allocated data from HPCM back to HP
                    958712
                    Hi Guru,

                    Your information is very helpful! Thanks!

                    Now I'm working on the data export and load rule. Here is part of the export file data:

                    DirectAllocation     GenealogyAllocation     AllAllocations     SysAllocVar1     SysAllocVar3                                   
                    FY12     [SCPOCCostSubCat].[NoMember]     [ACPOCCostSubCat]@[A50121001M04]     [LOPOCCostSubCat]@[A50121001M04]     BegBalance     [ACPOCActivity]@[MM01]     [LOPOCActivity]@[MM01]     Actual     HEG14Y     Working     CostReceivedPriorStage     123.076923
                    FY12     [SCPOCCostSubCat].[NoMember]     [ACPOCCostSubCat]@[A50121001M04]     [LOPOCCostSubCat]@[A50121001M04]     BegBalance     [ACPOCActivity]@[MM01]     [LOPOCActivity]@[MM01]     Actual     HEG14Y     Working     FixedDriverValue     8



                    Yet the loading returned an error as I believe there are some data which HP cannot cater those HPCM specific data, e.g. "[SCPOCCostSubCat]"

                    Should we write script / is there any functions that we can specify which which column of data to be exported and feed into HP?

                    Thanks again!

                    G
                    • 7. Re: Export allocated data from HPCM back to HP
                      MattRollings
                      you are loading data from a non-unique enforced to a unique enforced application. You cannot just straight load it to the application, you are going to have to do some manipulation to the file in the load rule.
                      • 8. Re: Export allocated data from HPCM back to HP
                        user13130025
                        If you are moving genealogy data (data from more than 2 non consecutive stages) you would pull the data from the ASO reporting cube. Try doing it the old fashion way, report script to text file, then an import to Planning. In the load rule get rid of all of the extra luggage that is embedded into the HPCM dimension member names.