Wednesday, May 6, 2009

How to Delete Dimension from Hyperion Planning Application

If you create a custom dimension in planning and wish to delete it, here is how you might remove it from SQL manually. We recommend that only someone familiar with relational databases attempt this.

Outlined below are the steps to remove a dimension from the Oracle relational store. The directions are for SQL, but the table names should be the same in Oracle.

This procedure should only be performed by your SQL or Oracle DBA.

BE SURE YOU HAVE A BACKUP OF YOUR APPLICATION BEFORE ATTEMPTING THIS PROCEDURE.

You will need to delete all the children first, then the dimension.

If you have the dimension in forms, you may also need to (after step 5) go into the hsp_formobj_def_mbr table and delete all references of that object ID. Then delete the dim id in the hsp_form_layout table.

Recycle services of Planning.



The following assumptions are being made about the dimension:

* No forms have any references to the dimension.
* Any and all attribute dimensions have been removed from the dimension.
* Any and all members have been removed from the dimension.
* All security access has been removed from dimension.
* Any Alias associated with the dimension has been removed.

In this example we have created a dimension called Dummy Dimension.

1. Open the Enterprise Manager for SQL server
2. Open the database that has your planning application
3. Open the table HSP_OBJECT
4. You need to find the row that has the dimension name you want to delete:
5. Take note of the OBJECT_ID in my example it is 50051, don't delete it yet.
6. Open the table HSP_MEMBER
7. Find and delete any rows that have a MEMBER_ID equal to the OBJECT_ID from step 5.
8. Close the Table HSP_MEMBER
9. Open the table HSP_MRU_MEMBERS.
10. Find and delete any rows that have a DIM_ID equal to the OBJECT_ID from step 5.
11. Close the table HSP_MRU_MEMBERS.
12. Open the table HSP_DIMENSION
13. Find and delete any rows that have a DIM_ID equal to the OBJECT_ID from step 5.
14. Close the table HSP_DIMENSION
15. Also delete it's reference from the HSP_UNIQUE_NAMES table.
16. Delete the row in the HSP_OBJECT table that you found in step 4.
17. Close the HSP_OBJECT table.
18. Open Essbase Application Manager for the application and remove the dimension from the necessary Essbase outlines.
19. Restart the application server.

Now when you open the application in Planning the dimension will be removed.
The next step is to perform a refresh to make sure planning and Essbase are in sync.

3 comments:

  1. Hi,

    This post is very helpful to Planning beginners,
    can you tell me the difference between the global variable and substitution variable

    ReplyDelete
  2. This works fine if there no attribute dimension for the dimension.

    ReplyDelete