OBIEE - Catalog Cleanup

Catalog Cleanup

Do you sometimes look at the catalog and wonder what kind of timebomb is lurking in there? I have nightmares. You've designed a catalog layout and asked developers to follow standards and follow the guidelines, but one year later......? What about two or even three years later? What Answers work has been started and abandoned, unfinished and unworking? What Answers remain from 'deleted' or 'hidden' dashboards and now no longer work due to repository changes?

The challenge

We are a small team of developers so so for us automation of tasks is key. For each report in the catalog what we need to know is:
Does this report generate a useable query? (Is it broken)?
Is this report on a dashboard, or more than one dashboard?
If the report is on a dashbord, is it in the correct directory?

We anticipate that this will be repeated periodically, you can never be sure what creeps in and slips through the cracks, but at least the generation of the report can be automated and scheduled.

At a high level what we do is to combine the output of some Catalog reports output, and use NQCMD to generate logical queries and track the outputs.

The Catalog Reports

Firstly we will generate a Dashboard Report and then an Analysis Report, and we'll upload the data into some database tables and stitch the two together with an outer join so we can see those reports on dashboards as well as those without.

We call the Analysis Report and a Dashboard with a script like below; Note, your system will have different settings.

D:\OBI\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalogmanager\runcat.cmd -cmd report -excelFormat -distinct -offline D:\OBI\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog\samplesales -forceOutputFile D:\CatalogCapture\Analysis.txt -type "Analysis" "Name" "Path" "Table" "Column" "Subject Area"

D:\OBI\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalogmanager\runcat.cmd -cmd report -excelFormat -distinct -offline D:\OBI\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog\samplesales -forceOutputFile D:\CatalogCapture\Dashboard.txt -type "Dashboard" "Name" "Path" "Dashboard Style" "Dashboard Page Name" "Dashboard Page Path" "Dashboard Page Description" "Analysis Name" "Analysis Path"

Join the two outputs on "Path" and "Analysis Path" - think outer join here because we still want to process those Analysis not on dashboards.

Generating a Logical Query

What we are going to do is pass the each analysis into NQCMD using the current repository to test whether a valid Logical Query.

Create a text file called cred.txt and make the contents something like
login=weblogic
pwd=weblogic123

Now using the report output from part 1 we can run the following to extract the query from the catalog for the answer. The Query will be saved into a file called myquery.lsql
D:\OBI\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalogmanager\runcat.cmd -cmd report -online http://%SERVR%/analytics/saw.dll -credentials "D:\CatalogCapture\creds.txt" -forceOutputFile "myquery.lsql" -folder "/shared/...../myreport" -type "Analysis" "SQL"

One of two things happen here:
1. The file containing the query is generated, or
2. An "empty file" is generated.

If we have an empty file the Answer failed to generate a Query and should be flagged for investigation.

However, if we have a query in the file, we need to pass the contents through NQCMD to see if the Answer works.

Extending into NQCMD

We have previously used NQCMD to do impact analysis on repository changes and can use the functionality almost unchanged.

"D:\OBI\Oracle_BI1\bifoundation\server\bin\nqcmd.exe" -d coreapplication_OH140093583 -u weblogic -p weblogic123 -s "myquery.lsql" -o "output.lres"

Once again one of two things can happen here:
1. The logical query passed in returns a result, or
2. An error is generated.

The error may be something like

[10058][State: S1000] [NQODBC] [SQL_STATE: S1000] [nQSError: 10058] A general error has occurred.
[nQSError: 43113] Message returned from OBIS.
[nQSError: 43119] Query Failed: 
[nQSError: 17001] Oracle Error code: 942, message: ORA-00942: table or view does not exist
 at OCI call OCIStmtExecute.
[nQSError: 17010] SQL statement preparation failed.
Statement execute failed


Processed: 1 queries
Encountered 1  errors

Once again flag the error.

Wrapping things up and bringing it together

From each Answer we now know
1. Is it used on a dashboard?
2. Where is the Answer stored?
3. Does it generate a logical query?
4. Does the logical query work?

Now we can focus efforts on fixing, rewriting or deleting those Analysis on dashboards, then those that are not on a dashboard but do not work.
Your initial steps may just be to identify those Answers that don't generate a logical query for a maintenance routine?