“Okay, this column in our BI metadata was supposed to be named Sales, not Sals.

But when we change it, what will happen to the reports that were created, using this name? And how many reports use this column anyway? Maybe we shouldn’t touch anything to keep things working…”

Ever been in such situation?

This question can be answered by opening each report and then looking at it’s contents ) A horrible task indeed.

Actually, this is exactly what BI system API are made for. Using them you should be able to automagically look into each report and find whether this column is used in it.

And as usual in this ‘BI system Needs and Means’ series, let’s look how it’s done in IBM Cognos BI and Oracle BI.

Cognos BI

Cognos BI has had an SDK (Software Development Kit) since ReportNet (for more than 5 years already) and this ‘long-time in market’ allowed to develop a whole ecosystem of tools to breed on top of it.

Concerning the question at the start of the post — there’s:

• Cognos Dynamic Report Updater — a tool to replace change column in every report.
• and a whole bunch of tools, based on SDK:
• and third-party tools

Oracle BI

In Oracle BI, finding such changes is somehow easier, since all reports are stored as xml files in presentation catalog folder. You just write a simple script that iterates on files, finds a string in file and reports back it’s findings (or replaces this string with a new one). Sounds easy, but after spending a couple days at it, I’m not so sure anymore )

There are a few major point against this way

1. It’s unsupported, there are no declared report modification API’s, AFAIK, so you’re scripting at your own sake. That would not be a big problem, if not:
2. Oracle BI presentation service adds a hash tag to report (and folder) names if they exceed 55 characters in length (see more on this topic here .
3. If report’s names are not in ASCII, they are converted to UTF-8 (but starting x symbol is replaced by #), so each non-ASCII symbol takes 5. So if you find file, containing desired string, you can only decode back first 9 letters of it’s name — report names are usually much longer than that. Therefore your searching capabilities are seriously limited.
4. Folder name hashing poses another problem if you’re using Windows. It impossible to iterate through catalogcontaining hashes an # in it’s name — OS listing methods stop working. Hope that situation is better on Linux. An easy but entirely manual workaround is to rename catalog folders to numbers (1,2,3,4) before starting the script ant to rename it back after.

I totally wish that this situation will change in nearest future and there will be a simple Presentation Server API call that will bring report name from file name and vica versa.

As usual, if anyone is interested in scripts  I wrote for the task (python, as usual + vbs) — ping me.