How to change the connection pool passwords with each migration?

I have been depending on my DBA to type the passwords in the connection pools every time we have an RPD that needs to be migrated to pre-production or Production. It is not much of an effort but it is just nuisance to bring him to my desk and let him type the password while I turn my head away.

I have been thinking it can be done by UDML and even heard from a colleague who has successfully done it but never really had all that time to try it myself. My colleague, Pradip, gave me some pointers on this and I thank him for that. Today, I got sometime to try it out for myself and I certify that it works best.

I had RPD with pre-production connections and new RPD with development database connections that need to be moved to pre-production. First, I copied the connection pool from my pre-production RPD and pasted in a notepad. Right click on the connection pool and click Copy and then Paste in a notepad. Just a reminder - Copy and Paste menu items in RPD are actually UDML generator and UDML executor respectively.

Once the UDML is pasted in notepad, you will see the password, user name and all the other connection pool properties. You can add all connection pools to notepad, if you have more than one connection pool that needs to be changed with every migration. Once done, run the below command at command line (you should have OracleBI client installed on the machine where you execute the command).

D:\OracleBI\server\Bin>nQUDMLExec.exe -U Administrator -P SADMIN -I D:\UDML_Stg.txt -B "D:\Repository\OracleBIAnalyticsApps_Dev.rpd" -O "D:\Repository\OracleBIAnalyticsApps_Stg.rpd"

Input RPD - D:\Repository\OracleBIAnalyticsApps_Dev.rpd
Output RPD - D:\Repository\OracleBIAnalyticsApps_Stg.rpd

Once the command is fully executed, you should find output RPD with changed connections. You can specify any path you may wish to have the RPD saved.

Contents of D:\UDML_Stg.txt is -
-------------------------------------------------------------------------------
DECLARE CONNECTION POOL "Oracle Data Warehouse"."Oracle Data Warehouse Connection Pool" AS "Oracle Data Warehouse Connection Pool" UPGRADE ID 2327604
DATA SOURCE {VALUEOF(OLAP_DSN)}
TIME OUT 600
MAX CONNECTIONS 100
TYPE 'Default'
USER 'VALUEOF(OLAP_USER)'
PASSWORD 'D7EDED84BC624A917F5B462A4DCA05CDCE256EEEEEDC97D54E960C7A19ECF4EFE0BDF057F3860BD2C8F3BEE6EAF9FC34'
SHARED LOGIN
ASYNCHRONOUS
CONNECTIONS TO SAME URI 10
OUTPUT TYPE XML
HEADER PATH {D:\\OracleBI\\server\\config\\NQSQueryHeader.xml}
TRAILER PATH {D:\\OracleBI\\server\\config\\NQSQueryTrailer.xml}
ISOLATION LEVEL DIRTY READ
BULK INSERT BUFFER SIZE 32768 TRANSACTION BOUNDARY 10
TEMP TABLE PREFIX {TT} OWNER {}
DESCRIPTION {User name is VALUEOF(OLAP_USER)
Data source name is VALUEOF(OLAP_DSN)}
PRIVILEGES ( READ);
-----------------------------------------------------------------------------