OBIEE11G Scripting – generate XUDML and change the password of the connection pools

Imagine that you want to migrate your RPD to different environments.  By using scripting utilities you can generate a new RPD and make changes for the new RPD in the generated file.  The  original RPD will be exported to  UDML or XUDML , in the generated file you can make changes for future RPD.  For this scenario we are going to use XUDML, the focus is on changing the password of the connection pools by using scripting commands.

If you want to export to UDML than you can use the commands: nqudmlexec.exe and  nqudmlgen.exe.
If you want to export to XUDML than you can use the commands: biserverxmlgen.exe and  <biserverxmlexec.exe
These commands can be found in this location: C:\oracle\middleware\Oracle_BI1\bifoundation\server\bin

Lets start with an example to export an RPD to XUDML.

1. Make a copy of your original RPD and put in a temporary folder , eg c:\testconnpool.
2. Generate the XUDML by executing the command biserverxmlgen
3. Go to the folder C:\oracle\middleware\Oracle_BI1\bifoundation\server\bin and type this command

biserverxmlgen -R C:\testconnpool\base.rpd -P Admin123 -O c:\testconnpool\test.xml -8

The parameters for the biserverxmlgen command are as follows:

- P repository_password

• R repository_pathname
• O output_script
-8 UTF-8


4. When this step is successful the XUDML is generated. Open the XUDML file named “test.xml” and check the content of the file.
   <?xml version="1.0" encoding="UTF-8" ?>

As this post is about changing the connection pool we are going to look in the XUDML file and look for the tag <Connection Pool>

<ConnectionPool name="Master" parentName="&quot;Master&quot;" parentId="3023:5" parentUid="8" id="3029:7" uid="10" user="obiee" password="6C6F0BE01FC4111F3AC2236A364D912E9B70195D55A2B76F203F3DDAF9C76F072606B70FAE3A2C3FC913
843F794309A82CF208A690AD0045D3DCE7CCEEA3B72F 137949B8C699489A5D548D7D5F4828ADB4071CCB22E7FEECBCE71592C1AC13E3" timeout="300" maxConnDiff="10" maxConn="10" dataSource="master" type="Default" reqQualifedTableName="false" isSharedLogin="true" isConcurrentQueriesInConnection="false" isCloseAfterEveryRequest="false" outputType="xml" bulkInsertBufferSize="131072" tempTablePrefix="TT" transactionBoundary="1000" xmlaUseSession="false" isSiebelJDBSecured="false"><Description></Description></ConnectionPool>

So we were able to export the RPD to XUDML and to find the connection pool in this file. Now we want to change the password of this connection pool but this password is encrypted. When you try to change the encrypted password to  the new password  this will not work. Some extra steps are needed to change the password of the connection pool.

5. Go back to the  original copy of the RPD, open it and select Manage > Variables from the application menu.

6. Now we are going to create two static repository variables:

puser ‘obiee’
ppass ‘mastertest’

7. Next, go the connection pool in the rpd and replace the user and the password with the static variables. Replace the user name with valueof(puser) and replace the password with valueof(ppass) and save the RPD.

8. Now rerun the XUDML command
   biserverxmlgen -R C:\testconnpool\base.rpd -P Admin123 -O c:\testconnpool\test2.xml -8

9. Open the XUDML file "test2.xml" and look for the variable tag ppass
   <Variable name="puser" id="3031:65" uid="126">
<Description></Description>
<Expr><![CDATA['obiee']]></Expr>
</Variable>
<Variable name="ppass" id="3031:66" uid="128">
<Description></Description>
<Expr><![CDATA['mastertest']]></Expr>
</Variable>

Change the content of this variable with the desired new password; in this case you need to replace 'mastertest' by the new password and save the changes.

Now generate the new RPD by using the command biserverxmlexec. Go to the folder C:\oracle\middleware\Oracle_BI1\bifoundation\server\bin and execute this command:

biserverxmlexec -I c:\testconnpool\test2.xml -O base2.rpd

The tool will prompt for the repository password.

The parameters for the biserverxmlexec command are as follows:

-P      : Password for repository
-I      : Input script file name
-B      : Base repository name
-O      : Output repository name

A new RPD is generated and the password for the connection pool has been changed.