Welcome to All Things Data Integration: Announcements, Insights, Best Practices, Tips & Tricks, and Trend Related...

ODI - Who Changed What and When? Search!

David Allan

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
 Packages  Package, Step
 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. 




Join the discussion

Comments ( 2 )
  • Ravi Kashi Friday, January 5, 2018
    Hi David,

    I found this post very useful and informative.
    If you can get the missing images back to this post, it would be very helpful.

  • Hi Ravi Tuesday, January 16, 2018
    Hi Ravi
    The images should be restored now.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.