The other day we had a problem with the nightly batch load of our Business Intelligence system the Data Warehouse. Actually we have problems all the time, we run some 20.000 jobs a month so problems is expected, but the problem we had the other day where special. En ETL process refused to run due to missing input. WTF! these files have never failed to show up before, well almost never. When tracing the error we found the SAP job assembling the information for us was not run, so our dependent extraction routine had failed with ‘SAP information not refreshed, abending...’ Following a long chain of dependent events, we found out the guys in our Tierp factory wanted vacation this summer too :-) To keep our customers happy, a slight change in the shop calendar was done to increase the production before the vacation period. These are all nice problems; continue keep customers happy, increase production, vacation. The nasty problem was the changed shop calendar was flawed and the cover time calculation bombed out which ultimately led to the above ‘SAP information not refreshed, abending...’. We notified SAP operations and the problems was fixed in no time. Our waiting ETL process munched the late input files and in the end a Qlikview process was triggered and some Qlikview applications was refreshed with the new information.
All these problems would have been sorted out and fixed by the operations without our interference, but we are the early birds, the BI users are the first to spot delays in the IT factory. The IT operation is monitored, but there is no special monitoring of the early precursors for the BI processes, so I said to myself why not do just that. So I knocked together a simple monitor for this purpose.
I keep a list of jobs I want to follow up, fetch all jobs run in SAP and joins those with my list to find out which jobs has not completed successfully and mail them to me.
This was easy peasy. (If you have a Lotus Notes mailbox and do not know Lotus Notes well, stick to a simple text message!)
I did the monitor in an ITL schedule with two jobs. The first job ‘getMonitoredJobs’ sifts failing jobs and the second ‘send_mail’ sends a mail to concerned if there is failing jobs. You use the bapi BAPI_XBP_JOB_SELECT to extract job information from SAP, the only snag is you have to run this bapi in a XMI/XBP session .
And the result, an example:
I wrote the spanish myself, without the help of Google translate. I used Google translate for a translation into english though. Hi, work (s) important for the data warehouse, SAP fiasco , not bad at all, I suspect it can be expressed better, but what the Hey I do not know spanish. And it sounds fantastic in Google Translate :))
Switch to english and have Google Translate speak up, very amusing. You can be creative with Google translate in ways I never thought of before.