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?

Validating the Catalog

Over time, inconsistencies can develop in the catalog as links are broken, users are deleted, or NFS file system issues are encountered. These inconsistencies can eventually lead to incorrect behavior, such as the inability to edit an agent's recipient list. You can periodically take the production system offline and validate the catalog, to be informed of and to take corrective action on inconsistencies.

Tasks in the Validation Process

The validation process performs the following tasks:

• Ensures that each object in the catalog is larger than zero bytes.
• Ensures that each item in the catalog has a valid corresponding .atr file.
• Ensures that each link in the catalog is valid.
• Ensures that the files in the account cache are valid.
• Ensures that all XML objects in the catalog pass schema validation.
• Attempts to repair object names that were damaged by ftp programs.

 Important Guidelines for Validating the Catalog

Before you validate the catalog, keep the following guidelines in mind:

• You must refresh the user GUIDs before validating the catalog. If you attempt to validate a catalog whose user GUIDs are not synchronized, then the validation process assumes that all accounts have been deleted and effectively removes all account information from the catalog.
• Use care when validating a catalog in a development environment, if that environment has a different security store from the production environment. If the validation is performed with a different security store, then many accounts might be removed from the catalog.
• When you turn on any validation of the catalog, all ACLs (that is, all privileges and every item's permissions) are "scrubbed," which means that dead accounts are removed from them and any changed items are written to disk. Therefore, even if you simply create a report without fixing any broken items automatically, you might find that the catalog is still extensively "changed."
• Before validating the catalog in a clustered environment, do one of the following:
• Shut down the Presentation Services cluster and run the validation directly against the cluster's catalog.
• Make a copy of the cluster's catalog and run the validation against that copy.
• Before using the 7-Zip utility to make a copy of a catalog, first shut down all nodes in the Presentation Services cluster or put all nodes in that cluster into Maintenance Mode (which is the recommended approach).
• Be aware that any changes that are made to the catalog online concurrently to the validation process are not included in the validation.

While backing up the catalog is always good practice, there is no practical difference between running validate against the catalog directly versus running the validation on a backup copy.

Updating a userid in the Web Catalog with runcat.cmd

Have you ever run into this error with OBIEE 11g?

The GUID (########################) for user ABC already exists in the catalog with username XYX. If this user was renamed in the backend, please use the catalog user management tools to rename this user in the catalog. If the username was not renamed, the catalog has to be cleaned of this GUID.[[
File:securityimpl.cpp
Line:610

This error occurs when an LDAP user is renamed, typically preventing the user from logging into the application. Oracle provides

documentation for updating the GUID, but there is little if any documentation to update a userid. Here are the steps to updating a userid in the OBIEE 11g Web Catalog.

Steps:

Log into the Oracle BI host.
Navigate to the Catalog Manager path:
<MiddlewareHome>\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalogmanager

Create a file called boot.txt in the same directory
Update the file with credentials able to use catalog manager
login=userid
pwd=password

Replace userid and password with those appropriate for your system (eg. weblogic....)

Save the file and close

Execute the runcat.cmd utility to update the userid
runcat.cmd -cmd renameAccounts -old OLDUSERID -new NEWUSERID -online http://IPAddress:PORT/analytics/saw.dll -credentials boot.txt

No output indicates that the user has been updated. Test by impersonating the user and logging into OBIEE.  You should now be able to login successfully under the new userid.

forums https://forums.oracle.com/forums/thread.jspa?threadID=2360823