ODI - Who Changed What and When? Search!
By David Allan-Oracle on Apr 30, 2014
Cell phones ringing, systems with problems, what's going on? How do you diagnose? Sound a common problem? Some ODI runtime object failed in a production run, what's the first thing to do? Be suspicious. Be very suspicious. I have lost count of the number of times I have heard 'I changed nothing' over the years. The ODI repository has audit information for all objects so you can see the created date and user for objects as well as the updated date and user. This let's you at least identify if an object was updated.
Firstly you can check the object you are suspicious of, you can do this in the studio, operator navigator or console - the information is also in the SDK and for some adventurous folk in the repository tables. Below you can see who updated the object and when.
You can also check other objects such as variables (were they updated, what's their values?) and load plans.
Casting the web wider -Search
There is a very useful but not very well known feature in ODI for searching for objects, invoke it from the Search -> Find ODI Object menu.
For example if I wanted to query the objects that have been updated in the last 7 days, then this could be used - I can do this for runtime objects like scenarios and load plans as well as design objects. Below you can see I have the search scope as 'Scenarios' - this lets you restrict the search to scenarios, variables and scheduled executions. Here is a table summarizing the search scope and objects within scope;
|Search Scope||Object Types|
|Projects||Folder, Package, Mapping, Procedure, Knowledge Module, Variable, User Function, Sequence|
|Meta-data||Model, Diagram, Datastore, Column, Key, Reference, Condition, Sub-Model, Model Folder|
|Scenarios||Scenario, Scenario Variable, Scheduled Execution|
|Organization||Project, Folder, Model Folder, Model, Sub-Model, Load Plan and Scenario Folder|
|Procedure / Knowledge Modules||Knowledge Module,Option, Procedure, Procedure Command|
|Load Plans||Load Plan|
The search supports searching objects based on the last update date, by object type (scenario, load plan etc.), by object name and by update user. You can define information about the search in the text field in the favorite criteria section.
So if you are working in a large customer deployment and have to cast the web wider, this would let you quickly find objects that have recently been updated across an ODI repository. This can have many useful purposes - could use it for identifying potential suspicious changes, for doing a cross and check between what has changed and what you have been told has changed and also for understanding the changes so you can then perform some subsequent actions - such as exporting and importing them.
The results of this utility are returned into the result panel which can be viewed in a hierarchic tree including foldering or in a linear list, you can then open/edit individual objects. Below you can see the result in a linear list. Selecting the 'Hierarchical View' tab would allow you to see any foldering.
One thing I did note, is that their is no wildcard support for users, so you have to pick a specific user. It would be better if you could search for all objects updated within some date period regardless of user - then be able to sort the objects based on date/name etc. The audit information is also available via the SDK and the non-supported but useful repository tables in order to build custom scripts.
When a search is made you can also save the search (as an XML file) and use this across teams by opening the saved file whenever you wish to use this search criteria. This is useful for discovering what updates have happened recently - whether it is runtime objects or design artifacts, you can use this.