Sunday, 8 March 2009

ODI – Hyperion Knowledge Modules - Reporting statistics correctly

If you have ever took looked at the steps in the Hyperion KM’s you will notice each one of them has one called “Report Statistics”



The step is there to record the details of the number of rows processed or the number of rows rejected, after running an integration using one of the KMs and looking in the operator you will see the step producing a warning


The step is set to “Ignore Errors” so it never fails on this step but why is it set up like this?

If you look at any of the Hyperion KMs and the step before the “Report Statistics” you will see that the results are returned into an object



This information is generated from a Java class called Statistics; the class has two variables known as successRows and errorRows and these variables are populated during the execution of the Knowledge Module.

Now if you look at the code “Report Statistics” step.



The step is executing a method toString() in the Statistics class, this method takes the two variables (successRows and errorRows) and formats it into it into an output message.

Number of rows successfully processed: <successRows>
Number of rows rejected: <errorRows>

Now in earlier versions of ODI there wasn’t really anything you could do with these results so the only option was to raise an error so that the information is displayed and the line raise “Planning Writer Load Summary”, statString is doing this.

If you have a look in the operator for any of the Hyperion KM report statistics steps you can see it in action.



This is the reason why the step has “Ignore Errors” selected so the integration doesn’t fail here.

From ODI version 10.1.3.4.2 there were some new substitution methods introduced; substitution methods are basically Java methods that are accessible from Knowledge Modules

These new methods are setNbInsert, setNbUpdate, setNbDelete, setNbErrors and setNbRows, if you look at the above image you will see boxes next to “No. of Rows”, “No. of Updates” etc, using the methods will set the value in these boxes.

The format of each of the methods is the same and you just have to pass a value into it, so say you wanted to use the setNbInsert method the code would be

OdiRef.setNbInsert(<value>) e.g. OdiRef.setNbInsert(10) would set the number of inserts to the value of 10

Now these methods are available it is easy to update any of the Hyperion KMs to use them though remembering at present the KMs only return values for number of records inserted and number of records rejected.



Updating the Report Statistics KM to the above will now set the Number of records inserted, number of records rejected and the total number of records processed. It is also possible to remove the “Ignore Errors” selection, as no exception will be raised anymore.



So now when you run your integration the statistics will be recorded correctly.

I am sure in future releases of the KMs the statistics step will be updated but it is also useful to know what is actually happening and having the ability to fix it.

4 comments:

Anonymous said...

I found your commentary very helpful. We are in dire need of help with LCM and Planning. We are currently working with 11.1.1.1 and the export of LCM is not working. We can use LCM with reporting but planning has been very problematic. It literally starts the process of exporting artifacts and just hangs. We have had Oracle support try and help but no luck. Any suggestions?

kas said...

John,

Your ODI blog has saved the EPM community! I have just built the interface to load entities to planning. It is running successfully and loading the entities to planning. However, it reports back that no records have loaded and writes all records to the error log. I am only seeing this behaviour with the entity load from MS SQL. The account load from a flat file reported correctly. I updated the Report Statistics in the KM to what you suggested here but have the same result. Any thoughts? Thanks

Anonymous said...

2 1/2 years on and it's still valid.
Thanks John you are a legend!

essebi said...

Amazingly helpful!

Thank you John.