Text searching the SQL of BI Publisher Data Model contents in the OBIEE catalog

If you're working in an environment where there are a lot of Business Intelligence Publisher (BIP) reports in an on-site OBIEE instance, sometimes you want to impact assess, or find usages of a certain database table or column. The following shell script is a very simple way to search the Data Model SQL in BI Publisher reports in the filesystem. Note of course this only works if you have an on-site OBIEE instance. For Oracle eBusiness Suite you'd be better to query the CLOBs in stored XML Publisher tables, and for Cloud based Oracle Analytics - you'll need to work that one out!

Change the CAT= like to store the location of the shared folder that you're catalog resides in.

Save the contents to a file called bipdmsearch and remember to chmod 755 bipdmsearch so you can execute it.

#!/bin/sh

if [ -z "$1" ]; then
 echo "Usage: bipdmsearch <search string>"
 exit 1
fi

if [ -z "$CAT" ]; then
 CAT=/u01/app/obi12c/config12.2.1.2/bi/bidata/service_instances/ssi/metadata/content/catalog/root/shared
fi

echo "cd $CAT"
cd $CAT

echo "Search for $1"
find . -type f -name \*xdm\* -exec grep -il "$1" {} \;

echo "Done"

This is a post from Gareth's blog at http://garethroberts.blogspot.com

... and we're back!

Wow - it has been a long time since I posted here! So much so that the world has changed - with an onslaught of content and apps and tools and technologies and Cloud! However that said I've found myself coming back to visit my old posts from time to time as technical references to especially fiddly things are often lost in the mix of content these days. I've always had a list of things to add here, and over the last few years I've been working in-depth with the Oracle Business Intelligence Suite and picked up a bunch of tips and tricks along the way, so the list is even longer now ;-)

Although there are more people documenting more things there are still plenty of techniques that are obscure but warrant being being referenced.

So, we'll start off with a couple of easy ones, maybe something using OBIEE back end catalog updates, then kick into some tougher ones .... maybe Google Earth KML output from Oracle SQL/BI Publisher or something like that :-)

Enjoy ...

This is a post from Gareth's blog at http://garethroberts.blogspot.com