Migration Design of RPD From One Environment to the Next

Migration of OBIEE RPD from one environment to the next can be simple, just copy the RPD.  But then one would have to manually open up the RPD afterwards in the target environment to change connection pool information, maybe AD server configuration and the list can go on.  Pretty much each environment may necessitate changes to the RPD for that specific environment.  I  want to reduce these manual changes in the migration project and somehow automate them.  So I came up with a  design that I have effectively deployed that reduces manual intervention,  automates the process and is more secure than most design strategies I have seen, since the connection pool passwords are all encrypted.  I will share this design in hopes that it will help others or give them ideas on their solution to their migration process.

The design requires a repository variable, UDML script, a service account to an external table and a stand alone RPD.  These will all be explained in detail later.  The basic flow is that when a migration is requested to lets say DEV to QA we would just run a script.  This script will take DEV’s RPD and run the UDML script against it to generate the new target QA RPD and move this new RPD over to the QA environment.  The UDML script is used to update the one repository variable (lets call it RPD_ENV) and the service account’s connection pool password.  The service account is used to get the other connection pool passwords for the RPD based on the environment the RPD is in, hence the RPD_ENV repository variable.  An example of the UDML script is as follows:

DECLARE CONNECTION POOL “SampleSales”.”Connection Pool” AS “Connection Pool” UPGRADE ID 2150568163
DATA SOURCE {SAMPLESALES.WORLD}
TIME OUT 300
MAX CONNECTIONS 10
TYPE ‘OCI10G’
USER ‘SADMIN’
PASSWORD ‘D7EDED84BC624A917F5B462A4DCA05CDCE256EEEEEDC97D51B4C9E811F09E97D0E252362E1DD04A6AC4D07C3A079829F’
REQUIRE QUALIFIED TABLE NAME
SHARED LOGIN
CONNECTIONS TO SAME URI 10
OUTPUT TYPE XML
HEADER PATH {E:\\OracleBI\\server\\config\\NQSQueryHeader.xml}
TRAILER PATH {E:\\OracleBI\\server\\config\\NQSQueryTrailer.xml}
BULK INSERT BUFFER SIZE 32768 TRANSACTION BOUNDARY 10
TEMP TABLE PREFIX {TT} OWNER {}
PRIVILEGES ( READ);

DECLARE RP VARIABLE “RPD_ENV” AS “QA” UPGRADE ID 2150568180 EXPRESSION {‘SIEBEL’}
PRIVILEGES ( READ);

As you can see the connection pool password is encrypted and the RPD_ENV repository variable is being set to its target environment.  For QAT to PROD script, this UDML script would be modified so that the service accounts password would reflect the production services password and RPD_ENV value to “PROD”. ( This is taking into account that the service account uses the same user id for logging in, if not this could also be changed in the line above the password. )  Now the service account is pulling the passwords from an external table in a database.  The table that I used is simple and the DDL (assuming using Oracle for the external table) is:

CREATE TABLE OBIEE_CONN_POOL(
“CONN_POOL” AS VARCHAR2(50),
“ENV” AS VARCHAR2(5),
“LOGIN” AS VARCHAR2(255)
)

CONN_POOL is the connection pool, ENV is for the environment (values would be ‘DEV,’QA’,'PROD’ or any/all environments) and ‘LOGIN’ is for the password for the connection pool.  I named the password field something different as a lot of DBA’s or compliance people are touchy on naming fields that would be obvious in case of the db being compromised.  This is just a simplistic version of the table and can be modified to satisfy the projects needs.

So I have explained the repository variable, the UDML script, with example, and the service account’s use.  So what is the stand alone RPD mentioned used for?  Well I created an RPD that is bare bones for the purpose of the DBA(s) to enter the password for the service account.  So the DBA would enter the service account’s password within this RPD.  When finished we would just have to copy the connection pool object and paste within notepad.  The result is the UDML for that connection with the password encrypted.  This way the DBA(s) are the only ones that know the service account password and thus how to access that external table.  So all connection pool password(s) would be secure and guarded by the DBA(s).  We only have the encrypted password, which does not help us use it, unless of course we know the encryption scheme OBIEE uses!:)  In my case, the service account password changes once a year.  So the DBA needs to update the password in this stand alone RPD and then I need to get the encrypted password in the process just discussed and replace the service accounts password string within the UDML script.  Also, the DBA(s) are the administrators of that external table and keeping the table’s passwords in synch when they change them.

A quick note on this approach.  In this approach, we have all the connection pool passwords located on an external database.  We use initialization blocks to set dynamic repository variables with the passwords to be used in the connection pools.  So that the connection pools passwords get there value from their respective repository variables, via VALUEOF(‘XXXX’) function.   Since all connection pool passwords, except for the service account, are now being retrieved and stored in repository variables there is a security risk that the OBIEE admin or users can get the passwords through the same VALUEOF() function (that is if they know the repository variables.)  There is no way, at least that I know, which allows me to add security and permissions on variables within the RPD.  Since this is a security risk this needs to be looked at by the OBIEE architects to assess how much of a risk this is.  A more secure approach then would be to house all connection pools in that stand alone RPD where the DBA(s) set the password for the service account.  In this alternate approach, the DBA(s) will set all the passwords in this RPD and then your UDML script will need to be modified to add each connection pool in it along with the encrypted password retrieved from this stand along RPD with the same copy and paste approach.  This way the passwords are 100% secure, to the best of OBIEE’s abilities.  So when the DB passwords change, the DBA(s) would need to modify and enter the new password in the stand alone RPD and the UDML script will need to be changed each time.  This may and will add more administration, but is more secure.

Now the main script used to run the migration can be in whatever scripting language you choose.  You may want to do pre and post work it is all up to the project requirements.  But to run the UDML script above use the following command nqUDMLexec. This tool takes the source/input RPD, runs the specified UDML script against it and results in a modified RPD specified from the output repository location and name.  The syntax of this command line is:

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 -U Administrator -P Administrator -I testudml.txt -B DEV.rpd -O QA.rpd

Any questions, comments or suggestions please let me know and I will post.  That way the OBIEE community can share from each other.

-Frank