ODI Series - When is a planning refresh not a refresh

You may have come across this issue if you are using the refresh functionality in an interface using the Planning IKM or may not if you trust everything you see in the operator.

The problem with the refresh functionality is that if a refresh fails for one of many reasons instead of the interface failing it is marked as successful in the operator.

Now if you have a data load straight after a refresh that has failed then it is a possibility you are going to end up with rejected records due to members not existing in essbase.

First of all let me replicate the issue.



I have added some nonsense as a member formula.



Running a planning refresh detects a problem with the formula when verifying and the refresh fails.



Nothing wrong there, so now let’s run an ODI interface against the same planning application and has the "REFRESH_DATABASE" IKM option set to yes.



In the operator the execution is shown as successful so you would assume that the refresh was also successful.
Not so fast, if you have a look at the log generate you will spot the problem.



As you can see the refresh has failed with basically the same error as if you had run the refresh from planning.

So is this a bug, well it all depends if you believe the interface was successful or not, I would say the interface was not successful as the only way to find out if there was a failure with the refresh it to look in the log, this is not so good if you want to run a data load after the interface.

It would be nice if you were given an option for the interface to fail or not depending on if the planning refresh failed.

Ok, so is there way around this, well if you read my blog on renaming members you will remember I created a custom IKM that would just run a planning refresh. If you want to know the full details I recommend reading through the blog or just the part where I create the custom IKM.

The custom IKM is just basically just one step, it is an updated version of the step “Prepare for loading” from the standard “IKM SQL to Planning” jython script, and it connects to planning and executes a refresh.

The code for the IKM is :-

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

#
# Target planning connection properties
#
serverName = "<%=odiRef.getInfo("DEST_DSERV_NAME")%>"
userName = "<%=odiRef.getInfo("DEST_USER_NAME")%>"
password = "<%=odiRef.getInfo("DEST_PASS")%>"
application = "<%=odiRef.getInfo("DEST_CATALOG")%>"

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

#
# Put the connection properites and initialize the planning loader
#
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 "Initalizing the planning wrapper and connecting"

dimension = "<%=snpRef.getTargetTable("RES_NAME")%>"
loadOrder = 0
sortParentChild = 0
logEnabled = <%=snpRef.getOption("LOG_ENABLED")%>
logFileName = r"<%=snpRef.getOption("LOG_FILE_NAME")%>"
maxErrors = 0
logErrors = <%=snpRef.getOption("LOG_ERRORS")%>
cubeRefresh = 1
errFileName = r"<%=snpRef.getOption("ERROR_LOG_FILENAME")%>"
errColDelimiter = r"<%=snpRef.getOption("ERR_COL_DELIMITER")%>"
errRowDelimiter = r"<%=snpRef.getOption("ERR_ROW_DELIMITER")%>"
errTextDelimiter = r"<%=snpRef.getOption("ERR_TEXT_DELIMITER")%>"
logHeader = <%=snpRef.getOption("ERR_LOG_HEADER_ROW")%>

# set the load options
loadOptions = HashMap()
loadOptions.put(ODIConstants.SORT_IN_PARENT_CHILD, Boolean(sortParentChild))
loadOptions.put(ODIConstants.LOAD_ORDER_BY_INPUT, Boolean(loadOrder))
loadOptions.put(ODIConstants.DIMENSION, dimension)
loadOptions.put(ODIConstants.LOG_ENABLED, Boolean(logEnabled))
loadOptions.put(ODIConstants.LOG_FILE_NAME, logFileName)
loadOptions.put(ODIConstants.MAXIMUM_ERRORS_ALLOWED, Integer(maxErrors))
loadOptions.put(ODIConstants.LOG_ERRORS, Boolean(logErrors))
loadOptions.put(ODIConstants.ERROR_LOG_FILENAME, errFileName)
loadOptions.put(ODIConstants.ERR_COL_DELIMITER, errColDelimiter)
loadOptions.put(ODIConstants.ERR_ROW_DELIMITER, errRowDelimiter)
loadOptions.put(ODIConstants.ERR_TEXT_DELIMITER, errTextDelimiter)
loadOptions.put(ODIConstants.ERR_LOG_HEADER_ROW, Boolean(logHeader))
loadOptions.put(ODIConstants.REFRESH_DATABASE, Boolean(cubeRefresh))

# connect to planning and set parameters
odiPC = ODIPlanningConnection(targetProps)
ODIPlanWrite = ODIPlanningWriter(odiPC)
ODIPlanWrite.beginLoad(loadOptions)
# run refresh with or without filters
odiPC.getWrapper().runCubeRefresh(Boolean("false"), Boolean(<%=odiRef.getOption("REFRESH_FILTERS")%>))
# clean up
ODIPlanWrite.endLoad()

In its current state if the above script is executed and the planning refresh fails the execution would not fail, though with adding in a little bit of error trapping then this could be controlled.

First of all I added an extra option to my custom IKM



With this option I can catch whether I want to make the step fail or not.

I added in the following import in the original script



This is so I could use ODIPlanningException class in the script, this class gets called upon if there are errors while refreshing.



Next I updated the final section of the jython script from the section
“# connect to planning and set parameters“



Basically the enhanced script tries to run a planning refresh, if it fails it checks the value in the IGNORE_ERRORS option, if it is not Yes (1) then it will raise an error and cause the step to fail with the error message that the planning java api generated.

When using the Try command in the jython script the tabulation is very import otherwise it will just fail with an obscure message.

I also cleared out many of the KM options such as the logging features as they are not required anymore.



I added an extra command step into the KM



I have added this step so I can deal with the situation where the option “IGNORE_ERRORS” is set to No and there is a planning refresh error, I still want to raise an error but I don’t want the interface to fail.



So the step checks if there is a value in planErr (this is generated in the previous step if there is an exception in the refresh), if there is a value it will raise an error, pretty much like in the previous step, the only difference is I checked “Ignore Errors”, so if the error is raised it will not fail the whole execution (in the interface those are the amber steps)

If this is all over your head and you just want to be able to use the IKM then you can download it from HERE

Let us try it out.



In this attempt I have left the option “IGNORE_ERRORS” to the default of “Yes” so even if the planning refresh fails the interface should be successful.




The execution was successful but there was a planning refresh error and this has been logged to the operator, the step is marked as amber so the whole execution has not failed.



This time I am going to run the same interface but set “IGNORE_ERRORS” to No



And this time the execution has failed and the error has been written to the operator.

With this custom KM I now have the ability to refresh any planning application without having to load meta/data and I can set the execution of the interface to fail or not if there is a problem with the planning refresh.