Sunday, 30 August 2015

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 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()

print "Iniatialise and connect to planning application"

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

# 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


SH said...

Nice one John

Chris Rothermel said...

RE: "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."


Utku Sönmez said...

Hello John, that's a great post, exactly what I needed. I have a further question. I need to match the aliases and the names in HSP_OBJECT table. I want to rename the names according to their aliases. If I write 'Alias' instead of 'User Defined Dimension Member', it renames the aliases which I do not want. Hoping you to give me an idea.