What has ODI encryption got to do with clearing the OBIEE cache???

by
April 22, 2010

Get our e-books Discover the Oracle Data Integrator 11g Repository Data Model and Oracle Data Integrator Snippets and Recipes
As part of a data mart refresh I am using nqcmd to clear the cache for the corresponding subject area in OBIEE. Thanks to John Minkjan for his post on properly documenting how the OBIEE cache can be cleared.
Basically I am executing nqcmd -d AnalyticsWeb -u Hans -p Zipfel -s d:obieescriptsPurge_XXX_Cache.txt in an ODI procedure with technology set to Operating System.
odi_encrypt1
I have currently installed an agent on the box for the BI server to launch nqcmd. However, I think that alternatively you can perform a custom install of the BI ODBC driver and the System Management component on the server where your ODI agent is running.
The major problem I have with the above solution is that the password for the Administrator password is sent across as clear text. Any ODI Operator module user would be able to view this. A gaping security hole. My first instinct when looking for a solution was to look for an encryption mechanism for this in OBIEE itself. Unfortunately no such thing is provided (as far as I know).
ODI to the rescue
I was rather frustated and ready to give up when I remembered that ODI has an encryption mechanism of its own.
Below are the steps I took to encrypt the password.
Right click on the procedure and select Encrypt. In the popup select Get a new encryption key
odi_encrypt2
Save the key in a secure location as you will need it should you ever wish to decrypt the procedure (yes it can be easily lost…).
odi_encrypt3
When you now execute the procedure only gobbledygook (what a great word) will show up in the Operator module.
In order to master scripting in ODI I recommend the following books.
Java BeanShell
Scripting in Java: Languages, Frameworks, and Patterns
Jython
The Definitive Guide to Jython: Python for the Java Platform.
Jython Essentials (O’Reilly Scripting)