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.

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.

ODI Series - Loading HFM metadata

In the previous blog I went through getting up and running with the HFM ODI adaptor, today I am going to start looking at the different load methods available with the four available Knowledge Modules.

Through the use of the Knowledge modules you will be able to do the following :-


• Load metadata and data
• Extract data
• Consolidate data
• Enumerate members of member lists

The Knowledge modules basically replicates the functionality that is available in the HFM desktop client, if you have ever used the client then you be comfortable using the ODI adaptors 



The objective today is load metadata information from a flat file into the Account dimension of an HFM application, if you have loaded metadata using any of the other Hyperion adaptors then you will see no difference in using the HFM adaptor and the process is straightforward, in fact this example I am going to go through couldn’t get much simpler.

You will need to make sure you have imported in the KM - IKM SQL to Hyperion Financial management Dimension.



With the KM you are able to load metadata in dimensions Account, Currency, Custom 1-4, Entity and Scenario.


As I will be loading from a flat file you will also need to have imported in the KM – LKM File to SQL

Currently I have the following hierarchy in my sample HFM application (this hierarchy was loaded from the Simple Demo files that are available in the HFM installation directory)




In my example I want to load the following expense members as a child of Account.



If you have a look at the Account datastore in the Designer you will see all the available properties that can be set when loading in metadata.



I am not going to go through what each of the columns means, as this is all available in the HFM documentation, if you have a read here you will find the details for each of the properties on the Account dimension, the documentation also contains information on the other dimensions.

Now I created a file template with the exact same column headers as the datastore columns, I find it easier to match the column headers as when you have auto mapping enabled in ODI it will set all your target mapping for you without having to manually set them up. You don’t have to create a template with all the columns if you don’t like, most of the time you might never use some of the columns so it is up to you if you include them in your file.



The file was populated with all the metadata information.



Once you the file is completed then it is on to defining the file model and datastore in the designer.




Create a new model of file technology and use a logical schema that points to the correct location of where the file will be stored. I am using a logical schema set up in previous blogs and just points a directory where most of the flat files I use are stored.




Next a new DataStore is inserted under the model, the file is manually selected using the browse functionality as the file is sitting on the same machine as where ODI is installed.



If you looked at the image of the file I am using you will notice it was a CSV and has heading so I set the file format as Delimited, the Heading number of Lines is 1 and the field separator is set to comma.




Reversing the file returns and stores all the columns in the datastore against the header columns in the csv file. After reversing you may need to change length of the columns, I have just left them set to the default for the example.

I usually set flat file types to String and then if I need to convert them to a different type I do it on the mappings of an interface, I do this because I find I have more success and encounter less when loading from files.

Right on to creating the interface to load the load from the file into the HFM application.



I am just using the SUNOPSIS_MEMORY_ENGINE as the staging area, this is because the volume of data from the flat file is very small, I would think that when loading metadata into a HFM then the volume would usually be pretty low.

You will always need to set a staging area as the target (HFM) can not be used to transform data as it has not SQL capability.



Drag the file source datasource on to the source of the diagram.



Drag the HFM Account DataStore on to the target of the diagram, as the columns on my source match the target all the columns are auto-mapped.

The warnings are just done to column length difference between source and target; if your source data is consistent then you don’t need to worry about it otherwise you could substring the length of the data in the target mapping or change the length in the source file datastore.



The default settings are kept for the LKM.



The IKM only has a few options

CLEAR_ALL_METADATA_BEFORE_LOAD - All dimension members and corresponding data, journals, and intercompany transactions in the application database are deleted.

REPLACE_MODE -

No= Merge - If a dimension member exists in the load file and in the application database, then the member in the database is replaced with the member from the load file. If the database has other dimension members that are not referenced in the load file, the members in the database are unchanged.

Yes = Replace - All dimension members in the application database are deleted and the members from the load file are put into the database.


In this example I am just doing a simple load for a few members so all the default settings are kept. I enabled the log and set the full path and filename.



After executing the scenario check the operator to make sure it was successful.



In the log file you will notice another log is generated, the log is the equivalent of what the HFM client produces, it is a validation of the source data.



If the interface fails then the additional log is the place you should check to find out the reason why the validation failed, I have no idea how the numeric code in the log filename relates to anything, it doesn’t look like a timestamp, maybe it is just created so the file will be unique.



In the HFM application you should now be able to select the new members that have been loaded. So there we have it a really simple example of loading metadata into HFM.

As I said in the first HFM blog I am going to keep it to the basics just to get people up and running with the different functionality available with the HFM adaptor.