There have been cases in analytics repository migration where the entire repository (popularly known as rpd in the developers circle) files needs to be moved to other environments due to code merge issues or developers not adhering to coding standards or due to any unforeseen issues. In such cases where the repository has multiple physical databases configured, it becomes a tedious and time consuming process to update the connection pool credentials (UID, PWD Data source name each of the connection pools created) manually and is also prone to mistakes. These errors might lead to incorrect database being queried upon or account being locked. To avoid such issues, one can use the nqUDML executables to automate this entire process.
 
Process to update development rpd with QA credentials during the migration.
 
Open the existing QA repository using the Administration Tool. Copy + Paste the connection pool to a notepad file. One can see the UDML definition of the connection pool. This UDML definition gives the Datasource name, UID and the encrypted password. Save this notepad file (dev2qa.txt or any name which identifies it with what it contains)
 
By using nqUDMLExec command line utility provided with Oracle BI, one can update the connection pool. It requires and accepts the following arguments.
 
-U : Administrator
-P : Password of the base repository
-I  : input script (in this case is the dev2qa.txt file)
-B : Base repository (in this case is the development rpd)
-O : Output repository (the updated repository with development metadata and QA credentials)
 
Usage: 
:\>nqUDMLExec –U Administrator –P SADMIN –I dev2qa.txt –B dev.rpd –O qa.rpd
 
 
Advantages
 
·         Multiple connection pools can be updated at the same time without manual intervention.
·         After the first time setup of the passwords in the connection pool by the DBA/OBI Administrators, they are encrypted and cannot be misused by developers.
·         Static variables, used in DSN names and UIDs can also be updated using this method.
·         Local repository users and groups and also be administered by having a one-time setup patch file for each environment.