We recently evaluated the ODI-OBIEE data lineage feature that was added to the most recent release of ODI 11g.
As part of this evaluation we came across various limitations of this out of the box data lineage functionality.
First of all we came across what I believe to be two bugs (one of them serious):
1. The data lineage feature does not take temp interfaces into account, i.e. if you are using a temp interface in any of your data flows from source to final target then the data lineage is broken. I logged this issue on 17 October 2011 with Oracle Support. However, up until today they have not been able to set up a joint OBIEE 11g/ODI 11g environment to reproduce the issue. Quote "The OBIEE team is having some issues with the OBI instance". I never bothered escalating the issue as we decided going against ODI - OBIEE data lineage anyway.
2. Another smaller issue has to do with the fact that for report columns that contain a formula, data lineage is broken.
In the report below we are using a formula.
However, in the data lineage report this is missing. I would have expected to get data lineage for any of the columns that are used. In our case for both amount sold and quantity sold.
The above are just bugs that can be easily fixed. However, the other issue we came across is more serious and cannot be addressed easily. Basically data lineage will be broken/lost if you are using views in your interfaces or procedures to populate your target tables.
The reason for this is that the view will mask the columns of the underlying table and may include additional transformation logic etc. As far as I can see, there is no easy solution to this. One such option would be to write an SQL parser that parses the columns of the underlying table(s) and maps them to the target table.
My estimate is that at least 90% or more of implementations are using views to extract data, e.g. it is best practice to use views to extract data from your data warehouse to your data marts to insulate your data marts from changes to the data warehouse.
What I would be interested in is to hear from people who are using the out of the box data lineage feature and what experience they have had and if they were able to overcome the above limitations.