Thursday, February 12, 2009

How to delete dimension from Planning Application

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

** THIS PROCEDURE IS TRIED AND TESTED ONLY ON 9.2 VERSION

BE SURE YOU HAVE A BACKUP OF YOUR APPLICATION BEFOREATTEMPTING 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 thehsp_form_layout table. Recycle services of Planning.
Procedure: 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.
The following assumptions are being made about the dimension: No forms have any references to thedimension. 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 DummyDimension.

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 is50051, 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.

11 comments:

krishna said...

Hi Chinmayi

good blog.if possible could u let me know how to take a backup of a webform secuity and also restoring it on planning web

Hyperionism said...

Hi Krishna,

If you use RDBMS backup of Planning repository, it should also have Webform securities,so no need to take different backup. I am not sure if export-import tool given by Planning can be used to backup webform security..Will explore the same and let you know
--Chinmay

pravi said...

Hi Chinmayi..

does anyone prefer to delete dimension after the deployment because unless identifying their business needs no one should go for the build phase. It involves a lot of risk.

Thanks,
Praveen

Hyperionism said...

HI Praveen,

You are right .. This happens in very rare situations .. It happened once in my project :) ..Someone by mistake added one dimension in Dev ...

konda.....rocks.... said...

Hi Chinmayi

Thanks for making life simple...

Ramesh

lelemon said...
This comment has been removed by the author.
lelemon said...
This comment has been removed by the author.
lelemon said...

you rocks!
thanks.. now my problem is solved :)

ps: i tested renaming dimension on 11.1.2 using your way. it works!

Hyperionism said...

Thanks for feedback lelemon .. Nice to hear tht it is working in 11.1.2 and even for Dimnesion name update !

vihari said...

Hi,
this is Vihari
am using Hyperion planning 11.1.2 DB on Linux server and facing the same problem of deleting the dimension from my planning application.could help any one on this issue step by step.

Urbano8300 said...

http://www.network54.com/Forum/58296/thread/1100126699/1100183369/faux+pa+%28sp-%29++Created+a+dim+in+Planning....now+want+to+delete...Plannng+says+NO%21

Uh oh...if you don't reference the original you stole that content.

Always give credit to the source.