I have added a simple version of the Load Plan Monitor to GitHub (Available Here). Please provide any feedback via GitHub.
Whilst using the standard Oracle tools for load plan execution monitoring and diagnosis I can’t help but feel sometimes that it’s a little “clunky”. An overview of the execution can be seen from BIACM and progress can be seen through ODI Studio Operator, however I either have to look in multiple places to see the current progress and state of a load plan, there are many clicks needed to get somewhere,or the information isn’t quite as I want it (or sometimes there is far too much information).
In reality, most of the time the things I am looking for are a rough idea of how far through the load plan we are, and whether there have been any failures. So here is a quick sneak preview of something I’ve spent a little bit of my free time playing with recently.
I developed this in Oracle Application Express (APEX), a free web-based rapid application development tool from Oracle. It’s a very basic application that I put together however it lets me see instantly the progress of my load plans (based purely on number of executed scenarios as opposed to a time-based algorithm), with the ability to drill into any failures which may have occurred. Over and above all that it has provided an excellent opportunity to understand the underlying metadata structures used within ODI which has given me some nice ideas for future enhancements and other process automations. It is read-only, i.e. it doesn’t support any options for interacting with ODI/BIACM (starting load plans etc), however that isn’t really something I’ll be looking at due to the risks involved and potential for things going wrong. Anything along thoses lines would of course affect your support agreement with Oracle should you need assistance on a related issue.
This is by no means a finished article, however it’s the first few tiny steps towards what will hopefully be a useful tool for us internally. The interface definitely needs a tidy up and there are many more bits of things I’d like to use this for. Ideas at the moment (amongst others) are
- Auto-refresh
- Better Failure alerting (although I do have separate scripts to do this)
- Export log information
- View load plan in a tree structure
- Better error diagnosis
- Slicker interface
- Proactive monitoring – have load times for a particular step jumped significantly etc? Do certain times of the plan have too many sessions running?
- Step trends, row count rate of increase, warehouse statistics etc.
Feel free to comment with any more!