This discussion is archived
11 Replies Latest reply: Nov 16, 2012 10:20 AM by cameronmcclurg RSS

Hyperion Planning Comments and Supporting Detail

cameronmcclurg Newbie
Currently Being Moderated
I understand that Comments and Supporting Detail are kept in SQL. The question is which DB (if not HYP_PLANNING) and what is the table? thanks...
  • 1. Re: Hyperion Planning Comments and Supporting Detail
    JohnGoodwin Oracle ACE
    Currently Being Moderated
    Try

    HSP_COLUMN_DETAIL & HSP_COLUMN_DETAIL_ITEM
    HSP_CELL_NOTE & HSP_CELL_NOTE_ITEM

    Cheers

    John
    http://john-goodwin.blogspot.com/
  • 2. Re: Hyperion Planning Comments and Supporting Detail
    cameronmcclurg Newbie
    Currently Being Moderated
    Thanks John. That was really helpful. I was able to locate all my comments and supporting detail. Here's what I'm trying to and I wanted to see if anybody has experience with this.

    For our discussion purposes, we have two main versions: Working and Final. When the budget cycle is over, all budget/working is copied to budget/final. However, our Financial Planning and Analysis group keeps budget/working open and continues to modify it for new budgets, etc. So, occasionally, we have to copy budget/working to budget/final. They use cell comments and supporting details quite extensively and the business requirement is to copy these to Final as well. Because of that, we've maintained this function as an administrative function (using Administration - Copy Data).

    I have explored automating this function, but I've had a problem. My plan would be to run an calc overnight which would copy budget/working to budget/final. The problem is that cell comments and supp details would not be copied via a calc. In order to do it right, i would need to automate a SQL statement that would copy these to the proper version. It doesn't seem that easy though as it looks like each comment gets a unique NOTE_ID IN HSP_CELL_NOTE_ITEM. I would think I'd have to insert the same note but get a sequential, auto numbered NOTE_ID and then link it to the new version object.

    I think this is solvable with the correct SQL, but rather than reinventing the wheel, i'm wondering if anybody else has dealt with this and how did you solve it?
  • 3. Re: Hyperion Planning Comments and Supporting Detail
    Jake Turrell Expert
    Currently Being Moderated
    I'm sure there's a way to do this with SQL, however it might be more straight forward to automate this using the Lifecycle Managment Utility. Here are the steps I would take:

    1) Using the LCM utility, export Supporting Detail and Cell Text to a file. (It will create two separate XML files.)

    2) In your batch process, update the XML files with the target version name.

    3) Import the XML files back into your Planning app.

    Hope this helps,
    - Jake
  • 4. Re: Hyperion Planning Comments and Supporting Detail
    cameronmcclurg Newbie
    Currently Being Moderated
    Can that be automated? In other words, can we set that up to run every night via batch scripting, etc.?
  • 5. Re: Hyperion Planning Comments and Supporting Detail
    Jake Turrell Expert
    Currently Being Moderated
    Yes. This is the utility that automates LCM:

    http://docs.oracle.com/cd/E17236_01/epm.1112/epm_lifecycle_management/ch07s05s05.html

    - Jake
  • 6. Re: Hyperion Planning Comments and Supporting Detail
    cameronmcclurg Newbie
    Currently Being Moderated
    Sounds promising. I'll take a look. Thanks!
  • 7. Re: Hyperion Planning Comments and Supporting Detail
    921751 Newbie
    Currently Being Moderated
    Hi,

    As per your requirement you want to not only copy data from one version to another but also its Comments,Supporting Details.
    In workspace, choose your planning application. Go to Tools->Copy Version.
    This give you the option to copy data from one version to another for a particular scenario.Also you can choose to copy Copy Account Annotations,Copy Comments,Copy Documents,Copy Supporting Details. However this may override the existing comments etc if there in the target version.
    With the version 11.1.2.2 the Cell comments can have trail of the information entered so this may help to keep track of prior entered comments. However if any specific cell comment would have been entered at target version it may be overriden.
    I have not tried this yet for comments , let me know if you were able to achieve what you desired.

    Thanks
    SN

    Edited by: SN on Nov 5, 2012 3:25 PM
  • 8. Re: Hyperion Planning Comments and Supporting Detail
    cameronmcclurg Newbie
    Currently Being Moderated
    Jake-
    In order to do what I'm trying to do, I need to automate the export of metadata from SQL as well. We've successfully used the Utility.bat to import the XML file once we exported it manually through LCM and modified the dimension value. However, we need help on the export as well. I don't see this utility working on the export side. Essentially, we'd have to be able to parameterize what elements we wanted to export, in this case supporting details and comments.

    The other alternative to this is somehow figure out what the LCM process is doing during the "Execute Migration" and copy it. Essentially, it is pulling from the SQL database and exporting into a specific XML file format. Are there any tools (hyperion, SQL or otherwise) that could do this export and conversion for us? I'm not an XML expert.

    Thanks-
    Cameron
  • 9. Re: Hyperion Planning Comments and Supporting Detail
    Jake Turrell Expert
    Currently Being Moderated
    Cameron,

    You should be able to export a file from LCM in an automated fashion.

    My theoretical process involves four XML files (you could name these anything - these are just my names):

    1) Export_Migration_Definition.xml (This XML file tells the Utility.bat what to export into files 3 & 4 below - Comments and Supporting Detail.)
    2) Import_Migration_Definition.xml (This XML file tells the Utility.bat what to import into your Planning app - again Comments and Supporting Detail.)
    3) Cell Texts.xml (This is the file that LCM creates with cell text. We'll need to change the version name in this file.)
    4) Supporting Detail.xml (This is the file that LCM creates with supporting detail. We'll need to change the version name in this file.)

    So where did I get files 1 & 2? If you go into LCM, and setup a manual migration, at the end, you'll see a button to "Save Migration Definition". Planning will create these two files for you. You'll need to go through the motions as if you were going to execute an export, and an import. Except you won't go all the way. At the end of the process, instead of pressing "Execute Migration", you'll press "Save Migration Definition" instead.

    Once you've got the Export_Migration_Definition.xml and Import_Migration_Definition.xml, you can automate the export and import. At this point you'll be exporting and importing the same comments and supporting detail, just to make sure it works. Then you need to insert a step in your batch process that does a find/replace on the Version name. You'll be doing the find and replace on files 3 & 4 above. I used Google to find several examples of Find/Replace as part of a batch process.

    Please let me know if you see any holes in this. I really don't think you need to interact directly with the repository for either the export or the import. In reality, you don't want to import directly into the SQL repository anyway, because you would typically need to recycle Planning for your changes to take effect.

    Hope this helps,
    - Jake
  • 10. Re: Hyperion Planning Comments and Supporting Detail
    Jake Turrell Expert
    Currently Being Moderated
    Here's the XML file I created (for the export) using the "Save Migration" button in LCM:

    <?xml version="1.0" encoding="UTF-8"?>
    <Package name="web-migration" description="Migrating Product to File System">
    <LOCALE>en_US</LOCALE>
    <Connections>
    <ConnectionInfo name="MyHSS-Connection1" type="HSS" description="Hyperion Shared Service connection" user="" password=""/>
    <ConnectionInfo name="AppConnection1" type="Application" product="HP" project="Planning" application="PutYourAppNameHere" HSSConnection="MyHSS-Connection1" description="Source Application"/>
    <ConnectionInfo name="FileSystem-Connection2" type="FileSystem" description="File system connection" HSSConnection="MyHSS-Connection1" filePath="/Temp_File_System"/>
    </Connections>
    <Tasks>
    <Task seqID="-1">
    <Source connection="AppConnection1">
    <Options/>
    <Artifact recursive="false" parentPath="/Relational Data" pattern="Cell Texts"/>
    <Artifact recursive="false" parentPath="/Relational Data" pattern="Supporting Detail"/>
    </Source>
    <Target connection="FileSystem-Connection2">
    <Options/>
    </Target>
    </Task>
    </Tasks>
    </Package>
  • 11. Re: Hyperion Planning Comments and Supporting Detail
    cameronmcclurg Newbie
    Currently Being Moderated
    Thanks a lot Jake. This has been very helpful for me. I haven't completed the prototype yet, but plan on returning to this logic when we try to do it. I think what you've outlined makes perfect sense and should work for us.

    Thanks...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points