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”
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.
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.
Thanks john for your Post. It is really helpful.
ReplyDelete