Deleting a custom dimension after creating an application

In this post, I will deviate from the Planning & TM1 series and go back to my roots, Hyperion Planning :)

Before I start I better clarify what I'm going to do is not something I would do personally in a real project if I'm being honest, I only do this so I better understand the technology I'm working on and what is happening in my backyard, if it can add value in any way so be it, otherwise please ignore and move on.

We (Hyperion Planning folks) all know that in any Planning application once you add a new dimension, there is no way back, so I thought I will show how to actually delete one dimension after creating a classic Planning application.

So I will start by creating a simple single currency Planning application.

As explained in an earlier post, we will have our six standard dimensions created by default, so I'm going to create one more custom dimension and call it CustomDim.

I will create three new members in Account, Entity, and CustomDim dimensions, and rename the default members in Version and Scenario dimensions, refresh the application and my outline will look like this.

Now I will create a dataform and reference a member in each of the seven dimensions as shown below.

Open the dataform, enter some data and save.

So now I have my application with seven dimensions and one dataform, I have decided I no longer want the dimension CustomDim, so I want to delete it, but I want to keep the dataform only without any reference to the CustomDim dimension. So before I do that I need to know the exact list of objects I have created after my application got deployed:

1. New Account member (Dummy Account)
2. New Entity member (Dummy Entity)
3. New dimension (CustomDim)
4. New CustomDim member (Dummy CD member)
5. New dataform (Dummy Dataform)

I will explain in a bit why I listed the objects in the exact order of creation, but for now, I'll go to the database and run  select * from HSP_OBJECT table  (here you find all objects created in the application, be it a dimension, member, task list, or dataform...).

The root cause of the issue here is CustomDim dimension, and from HSP_OBJECT table I know it's unique id is 50065, so what I need to do is delete any record referring to this id and its dependants (members created in the dimension, in this case 50066). before we do this we must understand the backend tables structure. the following list of tables got affected after I created the new members, dimension, and dataform.

HSP_OBJECT  (All records of new object such as members, dimensions, task lists, dataforms etc)
HSP_DIMENSION (New dimension record)

HSP_MEMBER  (New members records)

HSP_FORM (New dataform record)

HSP_FORM_LAYOUT (Layout specifications for the dataform)

HSP_FORMOBJ_DEF_MBR (Member selections for the dataform)

In a nutshell, I'll delete the member "Dummy CD member" from the dataform member selection, delete the dimension "CustomDim" from the dataform "Dummy Dataform", delete the member "Dummy CD member" from the dimension hierarchy, and finally, delete the dimension "CustomDim".

The unique id for CustomDim object is 50065 and for Dummy CD member 50065.

Deleting the "Dummy CD member" member reference in the dataform "Dummy Dataform".

Deleting the dimension "CustomDim" reference from the dataform "Dummy Dataform".

Deleting the member "Dummy CD member" from the dimension hierarchy.

Deleting the dimension "CustomDim" from the outline.

Deleting the "CustomDim" and "Dummy CD member" object references from HSP_OBJECT.

Now I'll stop and start the Planning service, log in to the application, check my Dummy Dataform, Planning outline, and my Essbase outline.

Yes, the CustomDim dimension was deleted from my Planning outline and the dataform, but not from Essbase outline, so I'll refresh the application from Planning to update my Essbase outline.

Voila, no more CustomDim dimension, now I can open the updated dataform and continue working.

Of course in real life it is way more complicated, and probably it is just easier to delete the application and start from the scratch.

That's it, hope it was fun.


UPDATE:

If you want to use the same dimension/member name again, you need to delete all references to the deleted dimension/member(s) from HSP_UNIQUE_NAMES table.


Until we meet again, may the Cosmos be with you.