7 Replies Latest reply: Oct 10, 2012 9:28 PM by 919044 RSS

    How to export supporting details and cell text comments from planning cube

    919044
      Hi,

      Do you know how to export supporting details and cell text comments from planning cube??

      Thanks...
        • 1. Re: How to export supporting details and cell text comments from planning cube
          Mehmet Sevinc
          Which version are you on? Life cycle management could be an option if you are on v11.x

          Ranzal has a pretty good blog about it: http://ranzal.wordpress.com/2010/03/24/using-oracles-hyperion%C2%AE-life-cycle-management/

          Supporting details and cell text comments are under Relational Data: http://docs.oracle.com/cd/E17236_01/epm.1112/epm_lifecycle_management/apis04s03.html

          Cheers,
          Mehmet
          • 2. Re: How to export supporting details and cell text comments from planning cube
            919044
            Hi,

            Actually I want to export in a text file or csv file along with data lines or without them


            Thanks
            • 3. Re: How to export supporting details and cell text comments from planning cube
              953582
              This data is in the planning repository. The data model of all the Hyperion schemas can be downloaded at http://docs.oracle.com/cd/E17236_01/epm.1112/epm_data_models_1112200.zip.

              h3. Supporting Detail
              The supporting detail is in the HSP_COLUMN_DETAIL and HSP_COLUMN_DETAIL_ITEM table. They can be queried together joining on DETAIL_ID. The HSP_COLUMN_DETAIL table has the IDs of the members in all the dimensions. This can be joined to HSP_UNIQUE_NAMES table to get the member name.

              Example SQL
              SELECT HSP_COLUMN_DETAIL.DETAIL_ID,
              HSP_COLUMN_DETAIL_ITEM.VALUE,
              HSP_COLUMN_DETAIL_ITEM.POSITION,
              HSP_COLUMN_DETAIL_ITEM.GENERATION,
              HSP_COLUMN_DETAIL_ITEM.OPERATOR,
              HSP_COLUMN_DETAIL_ITEM.LABEL,
              HSP_UNIQUE_NAMES.OBJECT_NAME AS Scenario,
              HSP_UNIQUE_NAMES1.OBJECT_NAME AS Account,
              HSP_UNIQUE_NAMES2.OBJECT_NAME AS Department,
              HSP_UNIQUE_NAMES3.OBJECT_NAME AS Period,
              HSP_UNIQUE_NAMES4.OBJECT_NAME AS Version,
              HSP_UNIQUE_NAMES5.OBJECT_NAME AS YEAR
              FROM HSP_COLUMN_DETAIL
              INNER JOIN HSP_COLUMN_DETAIL_ITEM
              ON HSP_COLUMN_DETAIL.DETAIL_ID = HSP_COLUMN_DETAIL_ITEM.DETAIL_ID
              LEFT JOIN HSP_UNIQUE_NAMES
              ON HSP_COLUMN_DETAIL.DIM1 = HSP_UNIQUE_NAMES.OBJECT_ID
              LEFT JOIN HSP_UNIQUE_NAMES HSP_UNIQUE_NAMES1
              ON HSP_COLUMN_DETAIL.DIM2 = HSP_UNIQUE_NAMES1.OBJECT_ID
              LEFT JOIN HSP_UNIQUE_NAMES HSP_UNIQUE_NAMES2
              ON HSP_COLUMN_DETAIL.DIM3 = HSP_UNIQUE_NAMES2.OBJECT_ID
              LEFT JOIN HSP_UNIQUE_NAMES HSP_UNIQUE_NAMES3
              ON HSP_COLUMN_DETAIL.DIM4 = HSP_UNIQUE_NAMES3.OBJECT_ID
              LEFT JOIN HSP_UNIQUE_NAMES HSP_UNIQUE_NAMES4
              ON HSP_COLUMN_DETAIL.DIM5 = HSP_UNIQUE_NAMES4.OBJECT_ID
              INNER JOIN HSP_UNIQUE_NAMES HSP_UNIQUE_NAMES5
              ON HSP_COLUMN_DETAIL.DIM6 = HSP_UNIQUE_NAMES5.OBJECT_ID

              h3. Text Values
              The cell text values are in the HSP_TEXT_CELL_VALUE table. This table has the text value and the unique number associated to it that exists in Essbase. To join the text to the members, you would have to export the data in Essbase and join the value to the TEXT_ID field.


              Kyle Goodfriend
              http://www.in2hyperion.com
              Please make sure to assign helpful/answered to responses if applicable - it rewards those who help and benefits the user community.
              • 4. Re: How to export supporting details and cell text comments from planning cube
                919044
                Hey Kyle,

                Thanks for help!!

                Can you please tell me how to export comments also?

                This is also very important I missed at first.

                Thanks!!!
                • 5. Re: How to export supporting details and cell text comments from planning cube
                  JohnGoodwin
                  Have a look at tables HSP_CELL_NOTE & HSP_CELL_NOTE_ITEM, the models document should give you more info, usually ids will need to join to the HSP_OBJECT table to provide a more meaningful description.

                  Cheers

                  John
                  http://john-goodwin.blogspot.com/
                  • 6. Re: How to export supporting details and cell text comments from planning cube
                    953582
                    It would be something like this.

                    SELECT HSP_CELL_NOTE.NOTE_ID,

                    HSP_UNIQUE_NAMES.OBJECT_NAME AS Scenario,
                    HSP_UNIQUE_NAMES1.OBJECT_NAME AS Account,
                    HSP_UNIQUE_NAMES2.OBJECT_NAME AS Department,
                    HSP_UNIQUE_NAMES3.OBJECT_NAME AS Period,
                    HSP_UNIQUE_NAMES4.OBJECT_NAME AS Version,
                    HSP_UNIQUE_NAMES5.OBJECT_NAME AS YEAR
                    FROM HSP_CELL_NOTE
                    INNER JOIN HSP_CELL_NOTE_ITEM
                    ON HSP_CELL_NOTE.NOTE_ID = HSP_CELL_NOTE_ITEM.NOTE_ID
                    LEFT JOIN HSP_UNIQUE_NAMES
                    ON HSP_CELL_NOTE.DIM1 = HSP_UNIQUE_NAMES.OBJECT_ID
                    LEFT JOIN HSP_UNIQUE_NAMES HSP_UNIQUE_NAMES1
                    ON HSP_CELL_NOTE.DIM2 = HSP_UNIQUE_NAMES1.OBJECT_ID
                    LEFT JOIN HSP_UNIQUE_NAMES HSP_UNIQUE_NAMES2
                    ON HSP_CELL_NOTE.DIM3 = HSP_UNIQUE_NAMES2.OBJECT_ID
                    LEFT JOIN HSP_UNIQUE_NAMES HSP_UNIQUE_NAMES3
                    ON HSP_CELL_NOTE.DIM4 = HSP_UNIQUE_NAMES3.OBJECT_ID
                    LEFT JOIN HSP_UNIQUE_NAMES HSP_UNIQUE_NAMES4
                    ON HSP_CELL_NOTE.DIM5 = HSP_UNIQUE_NAMES4.OBJECT_ID
                    INNER JOIN HSP_UNIQUE_NAMES HSP_UNIQUE_NAMES5
                    ON HSP_CELL_NOTE.DIM6 = HSP_UNIQUE_NAMES5.OBJECT_ID

                    Kyle Goodfriend
                    http://www.in2hyperion.com
                    Please make sure to assign helpful/answered to responses if applicable - it rewards those who help and benefits the user community.
                    • 7. Re: How to export supporting details and cell text comments from planning cube
                      919044
                      Thanks Keyle and John for the support and Time...