Renaming planning member names using ODI - Revisited

In the last post I went through in detail on how to update the planning cache after changes have been made to the applications RDBMS repository, a section of the post was dedicated to the process to rename planning members and then update the cache.

Many years ago I wrote a post on how to rename planning members using ODI 10g but the post did not contain any information on updating the cache and instead only ran a planning refresh where you could end up with multiple members in Essbase which is not really the best of solutions, yes planning could be restarted and then the refresh run but there is an alternative method which I feel is cleaner and more efficient.

I thought it might be a good idea to revisit the original post and include the process to update the cache, I would have liked to go through the process using ODI 12c but unfortunately planning is still not certified with it and I have no idea when it will be so I will stick with the supported 11g, anyway the concept is the same no matter the version of ODI.

I am going to be renaming members in planning 11.1.2.4 but the process will work in pretty much any version of planning.

Before I start I must give out the usual disclaimer that this post is just to give an idea of a possible method to rename planning members and I am not suggesting that direct changes the planning repository is a supported route, if you do decide to use this method please don’t blame me if it goes wrong and you end up in a bad place :)

There is going to be an assumption that you have ODI skills and this not going to be a training exercise, also if you have not already done so please read through my last post as it will provide an understanding of the planning cache and the tables, SQL required to rename member names.

In this example I am going to rename members in the product dimension, the members are PROD1 > PROD4

I have also created a form to validate the member renames and that the data is kept intact, this should confirm that the renaming process was successful.

Now on to the process of renaming members which will all be done using ODI.

I have a database table which contains the members to be renamed, it is kept nice and simple with the current member name and the new name.

The information does not have to be stored in a table it could easily be a file or other source which ODI can read.

The table was reversed as a datastore.

The four required tables from the planning applications repository were also reversed.

The HSP_OBJECT table is the core table in a planning application and is important as it holds the member names which will require updating.

OBJECT_ID – Each object inside planning has a numerical id, so for each member there will be a unique object id.

OBJECT_NAME – In my example this holds all the member name information.

OBJECT_TYPE – This column is driven by a numerical ID and defines what the object is within planning e.g. form, user, member etc

OLD_NAME – This contains the previous name of an object, so if a member is changed in planning the OBJECT_NAME will hold the new name and OLD_NAME holds the previous name.

MODIFIED and the MOVED column will require to be updated with a timestamp, even though the member was not moved planning does update this column when a member is renamed, it is not vital these are updated but to stay consistent with the way planning works it is a good idea to keep the changes in line.

MODIFIED_BY is of course the user that makes the changes.

The HSP_OBJECT_TYPE table contains a description of what object is within planning.

As it will be members from the product dimension that are being updated and they are part of a custom dimension then the type is known as “User Defined Dimension Member”.

The OBJECT_TYPE maps to the OBJECT_TYPE column in the HSP_OBJECT table, in theory you don’t even need this table is you already know what the object type value is but I am going to use it because it adds visibility to what type of dimension is being updated.

The HSP_UNIQUE_NAMES table basically contains all the unique member names in the planning application, as member are being renamed this table requires updating.

You can probably work out that the OBJECT_ID column maps to the same column in the HSP_OBJECT table.

The final table is the HSP_ACTION table which controls what objects are refreshed in the cache and will require records inserting for each member that has been renamed, this is step is carried out once the HSP_OBJECT and HSP_UNIQUE_NAMES tables have been updated.

I did explain this is in the last blog but here is a quick overview of the columns.

ID column contains a numeric value which increments by 1 each time a new insert is made into the table, in Oracle a sequence is used and SQL Server it is an identity column.

FROM_ID is who made the change but I always use 0 as it is not really that important.

TO_ID column should be the target ID but this will be usually set as 0.

ACTION_ID column defines the type of change 1=Add, 2=Modify, 4=Delete, as members are being modified the value will should be set to 2

OBJECT_TYPE column is the type of object that was changed which should be clear now.

PRIMARY_KEY column indicates which object IDs have been modified, this will be the ID of the member that is being renamed.

MESSAGE column is always blank and can be ignored.

ACTION_TIME column is a timestamp for when the activity occurred.

So that covers the tables involved and the next steps are to create three interfaces which will insert/update them.

First interface will update the member names in the HSP_OBJECT table.

In the mappings tab of the interface the datastores HSP_OBJECT, HSP_OBJECT_TYPE and MEMBER_UPDATE are dragged on to the source area.

HSP_OBJECT.OBJECT_NAME is joined to MEMBER_UPDATE.ORGINAL_NAME so only the records where there is a match are updated.

HSP_OBJECT.OBJECT_TYPE is joined to HSP_OBJECT_TYPE.TYPE_NAME and a filter is added to only return where the type of “User Defined Dimension Member”, in normal circumstances I would use a variable for the value so it can be set at runtime but I am trying to keep this as simple as possible.

You could argue that a join or filter is not required on the object type is not required which in the main could be true but say there is a member and a form with the same name there is a possibility of multiple records being returned which is not good.

The HSP_OBJECT was added as the target datastore and columns mapped.

The mappings define that the OBJECT_NAME will be updated with the new member name from the MEMBER_UPDATE table and the existing member name will be updated in the OLD_NAME column, the current timestamp will be updated in MODIFIED and MOVED columns and MODIFIED_BY will be updated with ‘admin’

In the target flow area I opted for the KM – “IKM Oracle Incremental Update” but feel free to use the KM that is best fit for you.

The INSERT option was set to false and UPDATE kept as true as I only want to update records and not insert as that would be bad.

On to the next interface which will be used to update the unique member name table with the new member names.

This time only the HSP_UNIQUE_NAMES and MEMBER_UPDATE datastores are required in the source area.

A join was made between OBJECT_NAME and ORIGINAL_NAME so only the records where the member name exists in the MEMBER_NAME table will be updated.

HSP_UNIQUE_NAMES was added as the target datastore and the OBJECT_NAME column is mapped to the new member name.

Once again the same IKM is used in the flow.

The same options as the previous interface are set.

The final interface will update the planning cache for the members that have been renamed which means records will need to be inserted into the HSP_ACTION table.

The same datastores that were used in the first interface are added to the source area

The only difference is this time the join is between OBJECT_NAME and NEW_NAME because the OBJECT_TABLE has been updated with the new member names.

In the target datastore mappings the ID column is set to use an Oracle sequence to generate the next ID to use, the sequence is already available in the planning application schema and even though it is probably not that important I am going to stick with it as that is the way planning generates the ID, obviously this doesn’t apply if you are using SQL Server.

In the flow this time I go with the IKM SQL Control append as I only want to insert records.

The FLOW_CONTROL option is disabled as it is not required in this instance.

All the interfaces are now ready but we are not finished yet as there is an additional piece that is required in the renaming process and that is the planning refresh which will push the changes down to the Essbase database.

I could easily create another interface which would load no records and use “IKM SQL to Hyperion Planning” with the “REFRESH_DATABASE” option set to “True” but I am going to be different and try a different method.

I am going to create a procedure which will only run a planning refresh.

Only one command is required to be added to the procedure.

In the “Command on Source” tab I have set the Technology to “Hyperion Planning” and selected the logical schema for the sample planning application.

I have defined this in the source so that in the “Command on Target” I will be able to pick retrieve the server name, application user name and password to connect to the planning application.

In the “Command on Target” I select Jython as the technology as I am going to add some code that will run a planning refresh.

I added the following Jython code.

from java.util import HashMap
from java.lang import Boolean
from java.lang import Integer
from com.hyperion.odi.common import ODIConstants
from com.hyperion.odi.planning import ODIPlanningConnection

# Target planning connection properties
serverName = "<%=odiRef.getInfo("SRC_DSERV_NAME")%>"
userName = "<%=odiRef.getInfo("SRC_USER_NAME")%>"
password = "<%=odiRef.getInfo("SRC_PASS")%>"
application = "<%=odiRef.getInfo("SRC_CATALOG")%>"

srvportParts = serverName.split(':',2)
srvStr = srvportParts[0]
portStr = srvportParts[1]

# Put the connection properties
targetProps = HashMap()
targetProps.put(ODIConstants.SERVER,srvStr)
targetProps.put(ODIConstants.PORT,portStr)
targetProps.put(ODIConstants.USER,userName)
targetProps.put(ODIConstants.PASSWORD,password)
targetProps.put(ODIConstants.APPLICATION_NAME,application)

print "Iniatialise and connect to planning application"

# connect to planning and set parameters
odiPC = ODIPlanningConnection(targetProps)
odiPC.connect()

# run refresh without filters
odiPC.getWrapper().runCubeRefresh(Boolean("false"), Boolean("false"))

print "Planning refresh completed"

The script stores the planning server, application, user name and password and initialises a connection to the planning application using this information.
It then runs a planning refresh without security filters.

Right so now I can put this all together in a package.

I included a simple wait step between the cache table being updated and the planning refresh to allow time for the cache to be updated before pushing the changes to the Essbase database.

Time to test out the process and run the package.

All steps were successful so back to the planning application to confirm whether the members have been renamed.

Perfect the changes are visible which means the rename and update cache process is working, how about on the Essbase side.

The data is still intact which means all is good with the renames in the Essbase database.

Now I have a process that can be reused on different dimensions or planning applications with minimal disruption and effort

Planning - updating the cache after repository changes

This is one of those blogs that I meant to write up but never got around to doing so, recently I read an excellent article from the DEV EPM guys on using ODI to manage planning security which ignited my drive to write up this blog, don’t worry this post does not involve ODI in case you got worried and started to switch off.

In the article it goes through making changes to the planning repository and to apply these changes a planning restart is required due to the planning cache, it is a shame as having to restart planning after updates can feel like a negative to a good solution due to the downtime that is involved and I know in the past I have been guilty of suggesting doing the same to get around the cache issue when there is an alternative method available.

I know there are many out there are aware of the alternative to updating the cache without restarting planning but I don’t often see it documented so for those that don’t know this is for you, if you do know then you have some free time to read a different blog or realise there is more to life :)

Before I start I should emphasize that I don’t recommend making any changes to a planning applications repository without understanding what you are doing and the risks involved.

The planning way caches information has been around for a long time, maybe since the beginning or the early versions, basically most of the information about the application is loaded into the memory when the application server is started and any changes are made to the repository and the cache, reading from the cache obviously helps performance and no doubt means less activity on the database repository side.

When any changes are made to an application the information about the change is written to a table named HSP_ACTION, a process reads this table on a set frequency and updates the cache then removes the entry from the table, so basically if you are making changes to any of the tables if you don’t update the HSP_ACTION table the cache will not be updated, well until the application server is restarted and the cache rebuilt.

You may be asking why use a table to control the cache and not just update it when changes are made, well one reason is that if you have multiple instances of a planning Java application server then there needs to be a method to update the cache on each instance, also I am guessing but it is probably more efficient to have a separate process that runs on a set frequency to manage the cache.

Using this method also has its drawbacks as if there are multiple instances of planning and one instance reads and clears information from the HSP_ACTION table before a separate instance has read the table you can end up depending on which server you are accessing being shown different information as they are out of sync, for example if you access planning on server A, make a change to security then server A reads the cache table and clears the entries before server B reads it then if you access the application on server B the security change will not be visible and will not until a restart.

To alleviate this problem there are two planning properties that can be applied – PLANNING_INTERVAL and EXPIRED_INTERVAL

POLLING_INTERVAL will be the time in milliseconds that the HSP_ACTION will be checked and the EXPIRED_INTERVAL which be the time in milliseconds before the entry will expire and be removed, playing around with these settings can be also be useful when you are trying to analyse what is happening in the table as by setting the expired interval to a higher value this allows more time to view the table entries before they are removed.

So let’s make a change to a planning application and check for changes in the table.

I added read access for one user against one member and the following records were inserted into the HSP_ACTION table.

Depending on how much you know about the planning repository will probably determine whether the entries all look like gibberish or not.

Three records were added to the table but only the first one is important if you intend on updating the cache yourself so that is the one I will be focusing on.

The ID column contains a numeric value which increments by 1 each time a new insert is made into the table, in Oracle a sequence is used and SQL Server it is an identity column.

The FROM_ID is who made the change, if the change is made from planning then each server will have an active ID which is held in a table named HSP_ACTIVE_SERVERS

I think many years ago when I first tried to imitate updating the cache I used the same server id which was my downfall as it didn't update the cache, I have since found it can simply be achieved using 0 as the ID.

The TO_ID column should be the target ID but this will be usually set as 0.

The ACTION_ID column defines the type of change 1=Add, 2=Modify, 4=Delete so in my example a new access permission was added for a user so the ID is 1.

The OBJECT_TYPE column is the type of object that was changed, the description of some of these types can be determined from the HSP_OBJECT_TYPE table but not all as 15 is not in there but in this case it means access permissions.

The PRIMARY_KEY column indicates which object IDs have been modified, if it is multiple objects like in the example then these are separated by a comma.

In the example the object IDs are 51434 and 51435, to find which object names these relate to then you can use the HSP_OBJECT table.

To understand the object type then this information can be found in the HSP_OBJECT_TYPE table.

The MESSAGE column is always blank and can be ignored.

The ACTION_TIME column is a timestamp for when the activity occurred.

So for the one record that is inserted into the HSP_ACTION table we can determine that the cache needs to be updated for access permissions for the user "jgoodwin" and account member name "MoreToLife".

By putting this knowledge together we should be able to manually update the cache by directly modifying the repository tables without the need to restart planning.

To test this theory I will take the same example which I have removed the security.

The first step is to add the access permissions which is defined in table HSP_ACCESS_CONTROL, I am not going to go into all the details of the table as this post is aimed at updating the cache but the comments on the table should give a good enough idea.

Once again HSP_OBJECT is your friend here to find the USER_ID and OBJECT_ID to insert into the table, the ACCESS_MODE is going to be Read so will be 1, the FLAG will be member so 0.

With a simple piece of SQL this can be achieved:

insert into HSP_ACCESS_CONTROL (user_id,object_id,access_mode,flags)
select usrname.object_id as user_id,mem.object_id as member_id,1 as access_mode,0 as flag
from hsp_users usr, hsp_object usrname,hsp_object mem
where usr.user_id = usrname.object_id
and usrname.object_name = 'jgoodwin'
and mem.object_name = 'MoreToLife'

The SQL inserts read member access for the user “jgoodwin” on the member “MoreToLife”

HSP_ACCESS_CONTROL

On to the cache update which is achieved with a similar SQL statement using the knowledge that has been acquired on the HSP_ACTION table

insert into HSP_ACTION (id,from_id,to_id,action_id,object_type,primary_key,message,action_time)
select HSP_ACTION_SEQ.NEXTVAL as id,0 as from_id,0 as to_id,1 as action_id,15 as object_type,
usrname.object_id || ',' || mem.object_id as primary_key,null as msg,CURRENT_TIMESTAMP
from hsp_users usr, hsp_object usrname,hsp_object mem
where usr.user_id = usrname.object_id
and usrname.object_name = 'jgoodwin'
and mem.object_name = 'MoreToLife'

The SQL is based on Oracle but only requires minor changes to the ID and ACTION_TIME columns so it will be valid for SQL Server, usually I would include additional joins because there could be a member and say a form with the same name but I am just keeping this as simple as possible for demo purposes.

The next time the automatic polling of the HSP_ACTION occurs the changes to the cache should be implemented.

Success the cache has been updated, even though the example was only for one record the logic is exactly the same if you want to make multiple updates to the cache.

Right, let me go through one more example and this time renaming planning member names, renaming member names has always been a bit of a pain and I wrote a post over 5 years about using ODI to try and achieve it, at the time I said running a planning refresh would update the cache which it did in a way but you could end up with multiple members in Essbase which is not good.

Depends how things go I may actually rewrite the blog and include updating the cache but for now I will go through the manual method.

I manually renamed the account member “MoreToLife” to “More2Life”

This member change created 3 new records in the HSP_ACTION table.

Even though there are 3 records the only one that is important is the one highlighted, the OBJECT_TYPE is 32 which by checking the HSP_OBJECT_TYPE table means the Account dimension.

The ACTION_ID this time is 2 which means modify.

The PRIMARY_KEY like previously mentioned relates to the OBJECT_ID in the HSP_OBJECT table

When you rename a planning member the column OLD_NAME in the HSP_OBJECT table is updated to reflect the change.

There is also a change to table which holds all the unique member names in the planning application.

I think you probably can work out what the MODIFIED, MODIFIED_BY columns are all about and the MOVED column gets updated with a timestamp as well even though the member was not moved, it is not vital these are updated but to stay consistent with the way planning works it is a good idea to keep the changes in line.

That is all there is to it and now if you put all that together it should be possible to rename planning members and update the cache so let’s give it go.

I created a simple form with the “More2Life” member and data against it to validate that everything looks good after the updates.

Now to rename the member to “MoreToLife”

update HSP_OBJECT set object_name = 'MoreToLife', old_name = 'More2Life',>
modified=CURRENT_TIMESTAMP,moved=CURRENT_TIMESTAMP,modified_by='admin'
where object_name = 'More2Life'

Update the unique member name table with the renamed member name.

update HSP_UNIQUE_NAMES set object_name='MORETOLIFE' where object_name ='MORE2LIFE'

Next one record is inserted into the HSP_ACTION table to update the cache.

insert into HSP_ACTION (id,from_id,to_id,action_id,object_type,primary_key,message,action_time)
select HSP_ACTION_SEQ.NEXTVAL as id,0 as from_id,0 as to_id,2 as action_id,32 as object_type,
mem.object_id as primary_key,null as msg,CURRENT_TIMESTAMP
from hsp_object mem
where mem.object_name = 'MoreToLife'

Finally the member rename needs to be pushed to Essbase so a planning refresh is run.

A check of the account dimension confirms the member name has been successfully renamed.

The form is also displaying the data against the renamed member so all good with the process

These were just a couple of examples of updating the cache and with a bit more work an automated solution could be developed, the logic can be applied to any object in the planning application where the cache is in operation.

Even though this method has been around since the caching system was introduced in planning but I am sure not everybody knew how it works, so hopefully say goodbye to having to restart planning when updating the repository.