“We use Oracle Business Suite as our primary ERP. We have acquired BICG product through Oracle, with plug in adapters, and thought we could do it on our own and after 3 years were unsuccessful. We contacted BICG … within a week we had working instance of the BI Aps. We decided we had to work with BICG. It has been 3years now, we have had BICG do training, had consultants onsite. It has been a great relationship. We are a reference often for Oracle, and when we are asked I tell them that they need to do business with a company like BICG. They have done wonderful work for us. They are the first ones that I would recommend to do the work.”

Ware Hartwell - Los Alamos National Laboratory

“We recently implemented Financial Analytics Module, we are now working on integrated some retail water billing data form a CIS system. The system has been well adopted by the users. It has become a fundamental enterprise system to the company.”

Keith Niesner - LCRA
Functional Analyst for Data Warehouse and OBIEE platform

Search the Blog

OracleBI Blog

Entries in Repository Migration (2)


Simplifying Migration Process – Changing Environment Specific Variables in RPD

When it comes to migrating repository file between environments (Dev - TEST-Prod), one of the common questions from OBIEE environment administrators is if there is a way to change the connection information without having to change them manually in all the places.

Creating repository variables for DSN and DSN Username solves the issue to an extent. However, imagine having to deal with changing multiple DSNs and their respective usernames. Moreover, there is the setting password to the DSN username. One way to automate the setting of the variables is to store the values in a file and set them via admin tool command line mode.

First, define repository variables that can be used in the connection pool.


Reference the DSN information in the connection pool.


Now create a control file SetVariables.txt with the environment specific values.


'To Open rpd - Open <rpdname> <Administrator User> <administrator password>

Open YourRpd.rpd Administrator SADMIN

'Setting OLAP DSN variable

SetProperty "Variable" "OLAP_DSN" Initializer " 'QA_DSN' "

' Setting OLAP DSN Username variable

SetProperty "Variable" "OLAP_DSN_USER" Initializer " 'QA_User' "

'Setting OLAP DSN User QA database password

SetProperty "Connection Pool" "AppDW"."Connection Pool" "Password" "QAPassword"





Create one per environment.


In the command line run the following command:

AdminTool.exe /command SetVariables_QA.txt


And the repository is now prepped for the other environment (QA in this case).



UDML Techniques for OBI

UDML is a text based non-public API that may be used to manipulate RPD

This may be used for faster manipulation to metadata objects and create a new instance of RPD especially for creating instances of rpd during migration processes

There are two ways of using UDML

1. Command line utilities
2. Text editor

Using Command Line Utilities

There are two command line utilities that are available to use which are available as a part of installed OBI server component. These are located in Root:\OracleBI\server\Bin directory

This is used to generate UDML from an RPD

The following is a example of passing parameters to use this utility

D:\OracleBI\server\Bin>nQUDMLGen.exe -h
nQUDMLGen -U userid [-P [password]] -R repository_pathname -O output_script_pathname[-8] [-N] [-Q] [-S]
-h Display this usage information and exit.
-8 is for UTF-8
-N is for not generating upgrade id
-Q is for generating script without security objects
-S is for generating script for only security objects
Q and S override each other if both are present

2. nQUDMLExec.exe

This is used to execute UDML

The following is a example of passing parameters to use this utility

D:\OracleBI\server\Bin>nQUDMLExec.exe -h
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 -I sampletestudml.txt -O rp1.rpd create a new repository rp1
Eg 2: nQUDMLExec -U administrator -I sampletestudml.txt -B rp1.rpd -O rp2.rpd
modify rp1 and write to rp2

-h Display this usage information and exit

Copying UDML to a Text Editor

1. Right click on a selected RPD object, Copy and Paste to a notepad

2. Make the required changes and save as .udml in Root:\OracleBI\server\Scripts directory

Let's see a simple UDML to change connection pool settings

The UDML file named test.udml has the following content # TEST.udml - Hashed PASSWORD derived by manually entering the # appropriate PASSWORD through Admin Tool and extracting UDML code DECLARE CONNECTION POOL "Oracle Database"."Connection Pool" AS "Connection Pool" UPGRADE ID 2150315609 DATA SOURCE {TEST_DSN} TIME OUT 300 MAX CONNECTIONS 10 TYPE 'Default' USER 'TEST_USER' PASSWORD 'D7EDED84BC624A917F5B462A4DCA05CDCE256EEEEEDC97D5D1CC182AFED63DC0C01716548C936275' SHARED LOGIN CONNECTIONS TO SAME URI 10 OUTPUT TYPE XML HEADER PATH {d:\\OracleBI\\server\\config\\NQSQueryHeader.xml} TRAILER PATH {d:\\OracleBI\\server\\config\\NQSQueryTrailer.xml} BULK INSERT BUFFER SIZE 32768 TRANSACTION BOUNDARY 10 TEMP TABLE PREFIX {TT} OWNER {} PRIVILEGES ( READ);

The following may be used to execute the UDML that updates Connection Pool from DEV.rpd and create TEST.rpd

nQUDMLExec.exe -U Administrator -P SADMIN -I D:\OracleBI\server\scripts\TEST.udml-B D:\OracleBI\server\Repository\DEV.rpd -O D:\OracleBI\server\Repository\TEST.rpd

Bookmark and Share