Art of BI: Oracle Data Integrator Security Profile Switching for Oracle BI Applications

As a preferred implementer of Oracle BI Applications (OBIA) modules (Financial Analytics, HR Analytics, Procurement and Spend Analytics, etc.), our team has been implementing the combo Data Warehouse, ETL, and Analytic content solution from Oracle for years, and are always brining best practices and innovation to our clients.

Oracle BI Applications 11g has been out now for a couple of years, and Oracle customers are still getting accustomed to Oracle Data Integrator (ODI) which has replaced the incumbent Informatica PowerCenter as the ETL (okay, E-LT) tool of choice. As you might know ODI 11g is the only tool viable for integrating directly with the OBIA solution.

Having multiple ETL (i.e: ODI 11g) developers as well as multiple OBIEE developers is now becoming the norm. As ODI 11g integrates with OBIA 11g and core Fusion Middleware (WebLogic), the initial security profile for connecting to the ODI 11g repository is based on WebLogic’s default LDAP system security. Yes, eventually, the implementation might expand to use another LDAP system such as MSAD to provide ODI 11g security access and delineate developer profiles, but initially to ensure all is working properly after installation most customers stick with the default security system. Developers, particular the lead developer, will need to access multiple development environments to not only configure the OBIA system as it is being installed but also pre-configuration to review some initial data loads, issues, and or work on future custom development.

Although ODI 11g can be installed on a Windows or Linux machine, one does not really want to install the ODI Studio on each environment (such as DEV, TEST, QA, STAGE, PROD, etc.). Installing OBIA and ODI on Windows OS servers does not always translate to the enterprise architecture and man companies will require installation of OBIA on a Unix or Linux OS. Also, each developer will typically on need access to a single developer environment, whether that be on a workstation/laptop or on a jumpbox/sandbox server which are typically Windows OS centric. In order for each developer to develop and switch their ODI 11g security profile connection to the environment specific security connection, the developer needs to capture two main security files that are created in each environment during the OBIA installation process. Those files are then copied to each developer workstation/laptop machine where ODI 11g Studio is installed. And, then placed in a specific ODI folder which will be referenced at run-time. Each time the developer wants to switch between the OBIA DEV and TEST connections to monitor or develop ETL the security files need to be switch on the file system to reference the environment access desired. The developer needs to close ODI Studio, switch the security files, then reconnect to be able to connect to the desired environment.

Our engineers developed a straightforward script that we’ve open sourced which will allow other Oracle BI teams to remove this bit of complication from a trivial, albeit necessary, task.

Initial Setup

Follow the basic instructions for OBIA post-installation configuration per the Oracle OBI installation documents.

During the ODI Studio configuration process it tells you to capture the ODI Security files from the environment on which OBIA is being installed.

Per each individual OBIA environment you need access to, capture the security files, make the necessary changes to the files, create a new folder per each environment, and store the two required files into each directory. The main path for the ODI11g is similar to <fmw_home>Oracle_ODI1clientodibin.

As seen in the screenshots below, create the director per each environment the developer(s) need access to. For example if you had three environments, OBIADEV, WINDEV, and PROD, you would create subfolders of the directory above with arbitrary names that equate to a reference to those environments. After which you can download or copy and paste the script towards the bottom of this post into a “.bat” file after configuring it per your developer environment. Then launching the file and passing in the respective test of the environment you so choose to switch security profiles for can be achieved.

Then place the respective cwallet.sso and jps-config-jse.xml files into the folders.

ODI Security Profile Switch Script

The script below is based on a Windows OS where we believe most ODI 11g developers will be working. We’ve seen only a few Oracle customers that are allowing their developers to use a non-windows OS for development. Though they do exists, if any one would like to alter the script for ODI developers using a *Nix environment for ODI 11g studio, feel free to do so.

[code type=”cmd”]
@ECHO OFF
SETLOCAL ENABLEEXTENSIONS ENABLEDELAYEDEXPANSION
:: ODI Studio Switch Environment Connection
:: http://artofbi.com

set odiClientSecurityDir=C:OracleFMW11119Oracle_ODI1oraclediclientodibin

ECHO.
set /p devEnv=”Enter an environment for which to switch security profiles: [windev | obiadev] ”

IF [%devEnv%] == [] GOTO NoValue

GOTO SwitchDevEnv

:NoValue
ECHO “Value is Empty. Try Again.”
EXIT /B 1000

:SwitchDevEnv
ECHO “Using Value %devEnv%”

2>NUL CALL :CASE_%devEnv% # jump to :CASE_red, :CASE_blue, etc.
IF ERRORLEVEL 1 CALL :DEFAULT_CASE # if label doesn’t exist

EXIT /B 500

:CASE_windev
ECHO Beginning to Load Dev Environment for “%devEnv%”
ECHO Copying all files in %odiClientSecurityDir%odi_client_%devEnv% to %odiClientSecurityDir%
xcopy /s/y %odiClientSecurityDir%odi_client_%devEnv%* %odiClientSecurityDir%
GOTO END_CASE
:CASE_obiadev
ECHO Beginning to Load Dev Environment for “%devEnv%”
ECHO Copying all files in %odiClientSecurityDir%odi_client_%devEnv% to %odiClientSecurityDir%
xcopy /s/y %odiClientSecurityDir%odi_client_%devEnv%* %odiClientSecurityDir%
GOTO END_CASE
:DEFAULT_CASE
ECHO Unknown Dev Env “%devEnv%”
GOTO END_CASE
:END_CASE
VER > NUL # reset ERRORLEVEL
GOTO :EOF # return from CALL

EXIT /B 0

PAUSE

ENDLOCAL
[/code]

How does it Work?

The script if straightforward.
– Line #6, provides the full path for the ODI bin directory where the standard security profile files must reside. This is also the location for our security profile sub directories
– Line #10, provides a prompted input line when the script is run and assigns to the variable, devEnv. Here one would enter the name of an environment they have declared. In the script the development environment value to enter here is the suffix of the sub-directory structure for the security profile files. This is specifically used in the CASE statement starting around line #29.

Towards the bottom of the script, xcopy, is used to copy and replace the existing bin folder security profile files with the ones per the environment alias entered when the file is executed. These files become the live run-time files. Nothing is lost because each environment’s specific security files are stored in a respective sub-directory and merely get copied from the sub-directory to the main bin folder each time a developer runs the script.

We suggest creating a batch file called something like “__ODI11g_OBIA_Security_Profile_Switching.bat“, and always running the script from an open Command Prompt terminal window in order to see the output succeed or fail when executed; Versus just double-clicking on the batch file.

Lastly, we resulting batch file that you create can be placed anywhere on the developer machine as all references to file paths should be full file paths which make transporting the script from one developer environment to another much easier.

Where to Get It?

You can download the script from the Art of BI github.com repository, https://github.com/artofbi/oracle-bi-applications-11g, under the ODI11g Studio directory or clone the repository for other OBIA materials we’ll be adding at a later date.

Each customer we’ve worked with starts out with this developer and environment access issue as detailed above. Many do not desire to integrate ODI Studio with a separate LDAP system unless they are existing users of ODI 11g and are proficient and maintaining and supporting ODI 11g in the organization. As a new or existing OBIA customer, this blog post and the script we’ve put together for you, should help make your implementation of OBIA at least a little easier.