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

November 7th, 2011 by

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  
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.

Comments

  1. Jerry Says:

    I’ve run nqudmlgen successfully on OBIEE version 10. But, when I try to run biserverxmlgen and nqudmlgen on OBIEE 11g I get this error: Xml File Not Found error number -9, /oracle/home/bifoundation/server/locale/NQLocale.xml.

    Why is the utility looking for this in /oracle/home? The NQLocale.xml file is actually in MIDDLEWARE_HOME/Oracle_BI1/bifoundation/server/locale.

    I don’t have any environment variables set to /oracle/home. That is not a valid directory on my server.

    BTW, I’m running OBIEE 11g (11.1.1.5) on AIX 6.1.

    Thanks.

    Jerry

  2. Sunil E Says:

    Excellent. Couple comments, I would add…
    1. We could use the -S option to generate security objects only
    2. To avoid storing passwords in the RPD as suggested, we could basically create and store XUDML from Target and Source RPD’s and apply as needed using the biserverxmlexec

  3. Harvard Says:

    Jerry, in 11g, you are required to run bi-init.sh (UNIX) or bi-init.bat before launching any command line utilities. This sets the environment variables like ORACLE_HOME and ORACLE_INSTANCE correctly. The reason it is looking for the locale xml files in the wrong place is because it is using the wrong ORACLE_HOME.

  4. Lady Gigi Says:

    Koen,
    Interesting article to know how to use the export tool, but maybe not such a good case, If you have to change the password anyway (test2.xml), then i guess you can quickly open the rpd online and change it, instead of the hassle to export + change + regenerate rpd.

    Gigi

  5. Anil Says:

    How can you enter the valueof(ppass) in the password field of connection pool? And I think it wont possible to get the password value from a variable.

    Thanks,
    Anil

  6. Selva Says:

    Why the password was encrypted in th initial biserverxmlgen command
    What is the purpose of static varables -puser and ppass
    If we use the static rep variables then all our default values are not encrypted

  7. Ashish Says:

    Hi, I need to write some kind of unix shell script to modify connection pool settings across different domains and I do not have access to Admin tool.

    I was following this blog but for the password, Do I need to use something like – valueof(ppass) ?
    I tried this but it is not working for me

Website Design & Build: tymedia.co.uk