Wednesday 31 March 2010

ODI Series - Loading HFM data

In the last ODI – HFM blog I went through loading metadata into a HFM application, today I am going to take a quick back to basics look at loading data into the same application.

In the last part I loaded 5 new members into the application


Today I am going to load data into the three bottom level members, the members aggregate to the next level so I don’t need to bother loading any data into the higher levels.



I started out with a basic HFM form that I am going to populate with data, I have just designed this form for demonstration purposes as I know you would usually use a form to manually enter data.



In the designer if you expand your HFM application model you will see a DataStore called HFMData, this will be used as the target and contains all the dimensions of the application and a column DataValue to load the values into. 



The overall process is simple enough :- create your source, in this example is going to be a flat file, create an interface with source and target plus mapping logic, set options and execute.



To make life simpler and have a source file DataStore that matches the HFMData DataStore I duplicated the DataStore



As my source is going to be flat file, I dragged the copy into my File Model as shown below



So now I have a file datastore that is an exact match of the target, I just have to set up some of the properties for the DataStore



I updated the name, alias and resource name to point to a CSV file



I set the File Format to be delimeted as it is going to a csv file, comma as the field separator and quote as the Text Delimeter.

Obviously you don’t have to follow my method, this method is just quick and easy if the source and target match.

Your source may be totally different and so would just have to go down the route of reverse engineering it first, I have covered this area in blogs in the past.



Next I created the source file with the columns to match the columns of the DataStore, I populated with some dummy data against the POV of the web form I designed. The file was then moved to the location where the logical schema used in file model points to (defined in the topology manager)



Next I created a new interface to perform the data load, I am using the memory engine as the staging area this is just because I have a small amount of data. I only really use the memory engine when the source data is small or the transformations are lightweight.



In the diagram the source file DataStore was dragged on to the source diagram and the HFMData DataStore on to the target, as the source and target columns are identical all the mapping were automatically done. I am not uses any additional logic so I can kept the default mappings.



The IKM selected for loading is “IKM SQL to Hyperion Financial Management Data”.

The IKM has a number of options
 :-

CONSOLIDATE_ONLY 


Valid values: Yes and No. If set to Yes, data is consolidated but not loaded.

IMPORT_MODE
Determines how data in the application cells is handled during data load. 


Valid values:

Merge (default)—For each unique point of view that exists in the load data and in the
application, the load data overwrites the data in the application. For each unique point
of view that is in the load data but not in the application, the load data is loaded into
the application.

Replace—For each unique point of view in the load data, the system clears
corresponding values from the application, and then the data is loaded.

Replace by Security—For each unique point of view in the load data to which the user
has full access rights, the system clears corresponding values from the application, and
then the data is loaded. Cells to which the user lacks full access are ignored.

Accumulate—For each unique point of view that exists in the load data and in the
application, the value from the load data is added to the value in the application.

ACCUMULATE_WITHIN_FILE
Valid values: Yes or No (default)
If set to Yes, multiple values for the same cells in the load data are added before they are
loaded into the application.

FILE_CONTAINS_SHARE_DATA
Valid values: Yes or No (default)
Set to Yes if the load file contains ownership data, such as shares owned.

CONSOLIDATE_AFTER_LOAD
Valid values: Yes or No (default)
If set to Yes, data is consolidated after being loaded.



CONSOLIDATE_PARAMETERS

Specifies the parameters for consolidation as comma separated values in this order: Scenario (required), Year, Period, Parent.Entity, and Type..

Valid Type parameter settings:
I” = Consolidate

D” = Consolidate All with Data
A” = Consolidate All
C” = Calculate Contribution
F” = Force Calculate Contribution

Example : Actual,2010,2,Canada,A

I just kept all the default options that meant I would merge the data, I did enter the path and filename for the log.



Executing the interface was successful.



As you can see the data has been loaded into the HFM application and the data can now be seen on the Form.



So what if my data was not unique and I need to sum up the values. I added extra line in my data file, if I use the default merge method the non-unique records would get overwritten.




Actual Equipment has been loaded with a value of 50, the first record with a value of 1000 is loaded and then overwritten with the next record for that POV with 50.





To accumulate the data you can either set the IMPORT_MODE to Accumulate, this would added to existing values in the application for the POV data point being loaded.



This time any data against a POV that already has data against it is accumulated



Or you can set ACCUMULATE_WTHIN_FILE to Yes, this would sum up the data from the source before it is actually loaded into the planning application.



The IMPORT_MODE is set to merge this means that the existing data in the application is replaced but because I have set accumulate within file to yes it aggregates all the data first before loading into the application



If I wanted to consolidate data after the load I would set CONSOLIDATE_AFTER_LOAD to Yes and set the POV and parameters in the option CONSOLIDATE_PARAMETERS.

So there we go a nice easy and quick example to loading data in a HFM application, this should give you a good starting point to building your own data loads.

Sunday 14 March 2010

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.