Tuesday, May 17, 2016

Oracle Hyperion Planning VS IBM Cognos TM1 Series - Part 5 (Creating Users)

In this post I will show how to create a single and group of users in Hyperion Planning and Cognos TM1, I will cover the creation part only going into provisioning and more details which I will cover in a later post.

Let get on with it.

Hyperion Planning
We create users in Hyperion Shared Services, so log in to your Hyperion Workspace and navigate to Shared Services.

Expand User directories, Native Directory and right click on Users as shown above, enter your username and password and click create.

Now search for the user.

Pretty simple really, now suppose we want to create 100 users, what we need to do is use the Lifecycle Management utility to export users from Shared Services, edit the exported file and simply import it back again. 

Write down the list of users in the following format.

Import the file back again using LCM and you have your users in one go and that's it.

Cognos TM1

Now let us create our user manually (aka client in TM1), we can do this using TM1 Architect or Performance Modeler, right-click on your application server and navigate to Security->Clients/Groups...

Right click on the last row and Add Client, enter your client name.

And if you want to see the same only in Performance Modeler.

Now I want to create 10 users, and this is the part I love about TM1 :)

I will create a new Turbo Integrator process, with datasource set as None.

Go to Advanced Tab/prolog section, and use the AddClient() TI function, save the process.

Now run the process.

That's it :)

Yes, I agree TM1 Turbo Integrator rocks. I'm going to keep it nice and simple in this post so that's it for today.

Until we meet again, May the Cosmos be with you!

Wednesday, May 11, 2016

How to change your Hyperion Planning application's first year member?

In this post, I will show how to change your Hyperion Planning's application first year member. Personally, I think the best way is to delete and create your application again, but it never hurts to know.

So now back to my lab rat like Hyperion Planning application, this is my outline and notice my first year member is FY16.

I'll create a simple dataform with my Years dimension in the rows section, and select IChildren(Years) to show whatever members are there in my Year dimension hierarchy.

And my dataform.....

Now we'll go to the backend tables, I need to know what is the unique id for FY16 from HSP_OBJECT and then update the name in the following:

1. HSP_OBJECT (column name = OBJECT_NAME and OLD_NAME) 
2. HSP_CALENDAR (column name =  FIRST_YEAR)

Start with HSP_OBJECT query.

FY16 object_id is 50002, I will run the following update statement to change my object_name and old_name, why old_name? just for the sake of consistency really, this column gets updated when you rename a member name or create it for the first time, in the case of Years dimension, it will always be the same as object_name because you cannot rename a member in Years dimension.

Now because I never trust my update statements, yes I'm funny, I will query my HSP_OBJECT table.

Moving on to the HSP_CALENDAR table.

I need to change 2016 to 2015.

Finally my HSP_UNIQUE_NAMES table.

I need to change FY16 to FY15 in this table so I can add a new member with the same name later on.

Restart planning services, open your Planning and Essbase to compare both, as shown below my Essbase outline is still showing FY16, so I need to refresh my database.

Now I'll go back to my dataform and it is showing FY15 instead of FY16.

Let us add a new year.

And change my Dummy Scenario member end year property.

Finally back to my dataform to confirm I can edit my new year member.

That's it for today if you like my post please DON'T do anything :)

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

Tuesday, May 3, 2016

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.


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.