Saturday, February 2, 2008

OBIEE development street

If you are using a "development street" (different development, test, acceptance and production environment) you sometimes need to reconfigure the connection pool of your repository back and forth. On a recent project a customer of us wanted to have this automated for both convenience and security reasons.
Before I go explaining how we did two things:
  1. Credit where credit is due, the original idea is from my collegue Raymond de Vries based on this entry from Mark Rittman.
  2. This method isn't supported by Oracle. They have logged as a possible feature for coming realeses.
This method is based the fact that Oracle uses UDML as scripting language when updating the repository. You can check this by right clicking on the connection pool and pressing Ctrl + C. Paste the result in note pad.

In the UDML script you regonise the ID of the connection (in this case 22). What we did was storing this file 4 times as dbTst.txt, dbDev.txt, dbPrd.txt and dbAcc.txt. In each file we only changed the datasource to the apporated database.

Next we created 4 .cmd files (conToTst.cmd, conToDev.cmd, conToPrd.cmd and conToAcc.cmd). Each file looks like this:

:: conToTst.cmd
:: reconfigure OBIEE repository to TEST database
:: Author: John Minkjan Ciber
:: Raymond de Vries Ciber
:: stop the BI server

Net stop "Oracle BI Server"'

:: Backup the rpd

copy E:\OracleBI\server\Repository\trucks.rpd ..\OracleBI\server\Repository\trucks.rpd.bak

:: run the update script
:: info :>
:: nQUDMLExec [-U [userid]]
:: [-P [password]]
:: -I input_script_pathname
:: [-B base_repository_pathname]
:: -O output_repository_pathname
:: [-8]
:: -8 is for UTF-8
:: Eg 1: nQUDMLExec -I testudml.txt -O rp1.rpd:: create a new repository rp1
:: Eg 2: nQUDMLExec -U administrator -I testudml.txt -B rp1.rpd -O rp2.rpd
:: modify rp1 and write to rp2
:: Update the .rpd
E:\OracleBI\server\Bin\nqUDMLexec.exe -U Administrator -P Administrator -I E:\connect\dbTst.txt -B E:\OracleBI\server\Repository\Trucks.rpd -O E:\OracleBI\server\Repository\Trucks.rpd

:: start the BI server
Net start "Oracle BI Server"'



Till Next Time

No comments: