The latest version of OBIA — 11.1.1.10.2 — came with a variety of new features. In this blog post, I will show you how Error Handling is managed, how it now works within many of the knowledge modules, what the report html files look like, and how to set up the email process.
ETL Error Handling
With the latest BI Apps version, the Operator Navigator showing a green checkmark for a session does not guarantee everything went smoothly during that session. For our example, the W_PARTY_ORG_DS session had an issue in the middle of the run.
For step two, we had an error. To understand what happened, we will expand the step and look at the task that failed, 37 – Insert new rows.
Here, we see we had a record with a value too large for a column. In previous versions, this would have stopped the entire load plan. But, this showed as a successfully completed session, so how exactly does the error handling work? To find out more, we can open the related package to see the steps.
This package workflow has some new logic. Specifically, the interface now has a ‘KO’ line for interface failure. So, when our interface fails, these two variables are refreshed and evaluated.
Notice that this logic is updating the variable DIAG_EXEC_NUMBER and then evaluating if the value is greater than or equal to 2. If not, run the interface again. If we’ve looped two or more times, raise an exception and stop the load plan.
“But wait,” you say. “How does running an interface again change anything about the value that is too big?” Excellent question. For the next part of Error Handling logic, we are going to look into the Knowledge Module to see what’s happening when we run the interface again.
Inside of the interface, on the Flow tab, the Integration Knowledge Module (IKM) used for this mapping has a very small clue. In the description, there’s a note that says, “Data can be controlled. Invalid data is isolated in the Error Table and can be recycled.”
When the interface runs again, the knowledge module runs extra C$ and I$ steps and isolates error records to be inserted into the E$ tables. Then the rest of the records are processed and the step completes successfully — allowing the load plan to continue uninterrupted.
All the ETL Operators who are getting calls at 2 AM for a failed load plan can rejoice!
“But wait,” you say. “There are now error records, but the load plan completed successfully. How do I know what those errors were, so I can manage them at a much more reasonable time the next morning?” Another excellent question!
ETL Run Reports
Each load plan now generates a report. This will give fantastic information about the success and failures of the load plan scenarios. These live on the ODI Server by default, in the following directory:
$MW_HOME/instances/instance1/biapps/logs/etl
These files are in html format. When you open one, this is an example of what you see for automatically corrected issues and if there were no hard failures. Notice the Task Name, Session Name, and Error Message are all provided.
Below is a different example that shows a report where there were hard failures and the load plan ended up being restarted. Here, it gives the Scenario Name that failed and the ODI Error Message.
Remember from the package that when an interface doesn’t successfully run after two loops, an Error is raised, and the load plan enters a failed state. This variable puts logic, step, and error information into a table located in the audit tables — which are used to build the load plan reports.
“But wait,” you say. “Isn’t there some way to send this information in an email when a load plan is complete, whether the run failed or succeeded?” You are going to be top of the class, with this kind of reasoning!
ETL Email Notification
Both the end steps in the load plan and the exception steps in the load plan have the scenario Send Email. The package shows that there is a variable evaluated to determine what kind of email to send, and then runs a procedure.
The SMTP Send Email Notification procedure is shown here and has several commands.
Notice that the command for Reading Options has several variables it is trying to test. These all hold the email server and connection information, as well as who the email is sent from and to, and even the email message body itself.
The Send Email command has the code to take all the previous information and build an actual email message to send.
The variables can be seen in ODI Studio in the Designer Navigator, but that is not the ideal place to set the email settings. Configuration Manager (BIACM) is the best place to set up your email to get these reports sent to you.
When logged into the BIACM URL, use the Manage Data Load Parameters task to search for DIAG as a Parameter Name.
From here you can see all the DIAG_EMAIL variables/parameters and can appropriately set up your email server, recipients, and email mode.
As you can see, these new features are really the ETL Operator’s dream come true. And while this most recent version of OBIA may only be a patch set release in terms of bug fixes and source content updates, these new ETL features make a compelling case for upgrading.
To get more OBIA tips, download the presentation...
