Creating a UDML file for OBIEE 11g RPD

The ability to use UDML for scripted OBIEE RPD changes has been around since OBIEE 10g. UDML files are very useful in order to do migrations, as they can contain the connection pool information and any variables which are environment specific. I am currently using UDML within our enivonrments to allow for migration of an RPD from Development through to Produciton.

Take a jump to view the steps to create UDML files for your environments, along with a sample UDML file.


If you are on Linux/Solaris then source the bi-init which sets up the environment variables for OBIEE commands

. {OBIEE11GInstance}/bifoundation/OracleBIApplication/coreapplication/setup/bi-init.sh

Run the nqUDMLgen command located in the Oracle_BI1 folder:

{OBIEE11GFMHome}/Oracle_BI1/bifoundation/server/bin/

The command to run, with parameters:

nqudmlgen –P <rpd Admin Password> -R <Path to RPD File>  -O <Path to Output UDML file>

This will create a large UDML file of your entire repository. You then need to fish out just the Connection Pools and Variables you care about which will change form environment to environment. Paste those components into a new file, and call it <ENVIRONMENT>.udml

Note: You can change most things from the UDML directly, like database schemas/sids, or variable values. The one thing you can is the connection pool passwords. Those you will need to first change in the RPD, generate the UDML, and then paste in. Once you have the UDML files, migrations are a snap, as you just apply the UDML file of the environment you are going to.

Here is a sample UDML file I created for a development environment which is using OBIA. Notice I only captured the Connection Pools and Variables I care about changing from environment to environment.

Click me to view DEV.UDML

DECLARE CONNECTION POOL "Oracle EBS OLTP"."Oracle EBS OLTP Connection Pool" AS "Oracle EBS OLTP Connection Pool" UPGRADE ID '80cefa95-0bd5-0000-714b-e31d00000000'
        DATA SOURCE  {VALUEOF(ORA_EBS_OLTP_DSN)}
        TIME OUT 300
        MAX CONNECTIONS 64
        TYPE 'Default'
        USER 'VALUEOF(ORA_EBS_OLTP_USER)'
        AES_PASSWORD '6C6F0BE01FC4111F3AC2236A364D912E82367C0723D9D9723721C4CB50D15EA005874D6559DF134D9F444104976710755A370704A09A9E272FE056558D48CA5B F6F02A8BE79927CC999036C463525CBDD5975D0E0EF0895CF1E6D1C4BA4262B9'
        SHARED LOGIN
        CONNECTIONS TO SAME URI 10
        OUTPUT TYPE XML
        ISOLATION LEVEL DIRTY READ
        BULK INSERT BUFFER SIZE 32768 TRANSACTION BOUNDARY 10
        TEMP TABLE PREFIX {TT}  OWNER {}
        POST CONNECT ('call /* valueof(NQ_SESSION.ACF) */ APP_SESSION.validate_icx_session(''valueof(NQ_SESSION.ICX_SESSION_COOKIE)'')')
        DESCRIPTION {User Name is VALUEOF(ORACLE_OLTP_USER)
Data source name: VALUEOF(ORA_EBS_OLTP_DSN)}
        PRIVILEGES ( READ);
DECLARE CONNECTION POOL "Oracle EBS OLTP"."Oracle EBS OLTP InitBlocks Connection Pool" AS "Oracle EBS OLTP InitBlocks Connection Pool" UPGRADE ID '80b43a95-0bd5-0000-714b-e31d00000000'
        DATA SOURCE  {VALUEOF(ORA_EBS_OLTP_DSN)}
        TIME OUT 300
        MAX CONNECTIONS 64
        TYPE 'Default'
        USER 'VALUEOF(ORA_EBS_OLTP_USER)'
        AES_PASSWORD '6C6F0BE01FC4111F3AC2236A364D912E82367C0723D9D9723721C4CB50D15EA005874D6559DF134D9F444104976710755A370704A09A9E272FE056558D48CA5B F6F02A8BE79927CC999036C463525CBDD5975D0E0EF0895CF1E6D1C4BA4262B9'
        SHARED LOGIN
        CONNECTIONS TO SAME URI 10
        OUTPUT TYPE XML
        ISOLATION LEVEL DIRTY READ
        BULK INSERT BUFFER SIZE 32768 TRANSACTION BOUNDARY 10
        TEMP TABLE PREFIX {TT}  OWNER {}
        POST CONNECT ('call /* valueof(NQ_SESSION.ACF) */ APP_SESSION.validate_icx_session(''valueof(NQ_SESSION.ICX_SESSION_COOKIE)'')')
        DESCRIPTION {User Name is VALUEOF(ORACLE_OLTP_USER)
Data source name: VALUEOF(ORA_EBS_OLTP_DSN)}
        PRIVILEGES ( READ);
DECLARE CONNECTION POOL "Oracle EBS OLTP"."Oracle EBS OLTP DbAuth Connection Pool" AS "Oracle EBS OLTP DbAuth Connection Pool" UPGRADE ID '80b43a97-0bd5-0000-714b-e31d00000000'
        DATA SOURCE  {VALUEOF(ORA_EBS_OLTP_DSN)}
        TIME OUT 300
        MAX CONNECTIONS 80
        TYPE 'Default'
        USER ':USER'
        AES_PASSWORD '6C6F0BE01FC4111F3AC2236A364D912E82367C0723D9D9723721C4CB50D15EA005874D6559DF134D9F444104976710755A370704A09A9E272FE056558D48CA5B F6F02A8BE79927CC999036C463525CBDD5975D0E0EF0895CF1E6D1C4BA4262B9'
        SHARED LOGIN
        CONNECTIONS TO SAME URI 10
        OUTPUT TYPE XML
        ISOLATION LEVEL DIRTY READ
        BULK INSERT BUFFER SIZE 32768 TRANSACTION BOUNDARY 10
        TEMP TABLE PREFIX {TT}  OWNER {}
        POST CONNECT ('call /* valueof(NQ_SESSION.ACF) */ APP_SESSION.validate_icx_session(''valueof(NQ_SESSION.ICX_SESSION_COOKIE)'')')
        DESCRIPTION {User name is :USER
Password field should contain :PASSWORD}
        PRIVILEGES ( READ);

DECLARE CONNECTION POOL "Oracle Data Warehouse"."Oracle Data Warehouse Connection Pool" AS "Oracle Data Warehouse Connection Pool" UPGRADE ID '00238434-0bd5-0000-714b-e31d00000000'
        DATA SOURCE  {VALUEOF(OLAP_DSN)}
        TIME OUT 300
        MAX CONNECTIONS 100
        TYPE 'Default'
        USER 'VALUEOF(OLAP_USER)'
        AES_PASSWORD '6C6F0BE01FC4111F3AC2236A364D912EA392739D8ABA5B5D77C02AA523C79850FA383C28CBE406264CBEADCF063BBA55 BB9F4B07EB3F9DB1F091E66E88ABFFEAF117D1D661F8060EF6214536FEBFC704'
        SHARED LOGIN
        CONNECTIONS TO SAME URI 10
        OUTPUT TYPE 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);
DECLARE CONNECTION POOL "Oracle Data Warehouse"."Oracle Data Warehouse Repository Initblocks Connection Pool" AS "Oracle Data Warehouse Repository Initblocks Connection Pool" UPGRADE ID '8058fb0a-0bd5-0000-714b-e31d00000000'
        DATA SOURCE  {VALUEOF(OLAP_DSN)}
        TIME OUT 300
        MAX CONNECTIONS 10
        TYPE 'Default'
        USER 'VALUEOF(OLAP_USER)'
        AES_PASSWORD '6C6F0BE01FC4111F3AC2236A364D912EA392739D8ABA5B5D77C02AA523C79850FA383C28CBE406264CBEADCF063BBA55 BB9F4B07EB3F9DB1F091E66E88ABFFEAF117D1D661F8060EF6214536FEBFC704'
        SHARED LOGIN
        CONNECTIONS TO SAME URI 10
        OUTPUT TYPE XML
        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);

DECLARE CONNECTION POOL "Oracle Analytics Usage"."Usage Tracking Connection Pool" AS "Usage Tracking Connection Pool" UPGRADE ID '00238436-0bd5-0000-714b-e31d00000000'
        DATA SOURCE  {VALUEOF(OLAP_DSN)}
        TIME OUT 300
        MAX CONNECTIONS 100
        TYPE 'OCI10G'
        USER 'DEV_BIPLATFORM'
        AES_PASSWORD '6C6F0BE01FC4111F3AC2236A364D912E6FE385B517924A7854D8ADE26FFF384EF7C27CB462973FCBE472088DA0566674A6EF3442FE326E7D98406D5E4DE2C90D 9A32196C9984E27780843E928862E888427B6059D88AE69E9CE6849A02B18340'
        SHARED LOGIN
        CONNECTIONS TO SAME URI 10
        OUTPUT TYPE XML
        ISOLATION LEVEL DIRTY READ
        BULK INSERT BUFFER SIZE 32768 TRANSACTION BOUNDARY 10
        TEMP TABLE PREFIX {TT}  OWNER {}
        DESCRIPTION {User name is DEV_BIPLATFORM
Data source name: VALUEOF(OLAP_DSN)}
        PRIVILEGES ( READ);
DECLARE CONNECTION POOL "Oracle Analytics Usage"."Usage Tracking Writer Connection Pool" AS "Usage Tracking Writer Connection Pool" UPGRADE ID '002b2498-0bd5-0000-714b-e31d00000000'
        DATA SOURCE  {VALUEOF(OLAP_DSN)}
        TIME OUT 300
        MAX CONNECTIONS 100
        TYPE 'OCI10G'
        USER 'DEV_BIPLATFORM'
        AES_PASSWORD '6C6F0BE01FC4111F3AC2236A364D912E6FE385B517924A7854D8ADE26FFF384EF7C27CB462973FCBE472088DA0566674A6EF3442FE326E7D98406D5E4DE2C90D 9A32196C9984E27780843E928862E888427B6059D88AE69E9CE6849A02B18340'
        SHARED LOGIN
        CONNECTIONS TO SAME URI 10
        OUTPUT TYPE XML
        ISOLATION LEVEL DIRTY READ
        BULK INSERT BUFFER SIZE 32768 TRANSACTION BOUNDARY 10
        TEMP TABLE PREFIX {TT}  OWNER {}
        DESCRIPTION {User name is DEV_BIPLATFORM
Data source name: VALUEOF(OLAP_DSN)}
        PRIVILEGES ( READ);

DECLARE RP VARIABLE "OLAPTBO" AS "OLAPTBO" UPGRADE ID '00238487-0bd7-0000-714b-e31d00000000' EXPRESSION {'OBIBAW'}
        DESCRIPTION {Data Warehouse table owner}
        PRIVILEGES ( READ);
DECLARE RP VARIABLE "OLAP_USER" AS "OLAP_USER" UPGRADE ID '0023844e-0bd7-0000-714b-e31d00000000' EXPRESSION {'OBIBAW'}
        DESCRIPTION {Database user used to connect to the data warehouse}
        PRIVILEGES ( READ);
DECLARE RP VARIABLE "OLAP_DSN" AS "OLAP_DSN" UPGRADE ID '0023844f-0bd7-0000-714b-e31d00000000' EXPRESSION {'OBID'}
        DESCRIPTION {Data Source Name of the Data Warehouse.   It is used in the Connection pool definitions, to normalize this information in the repository.}
        PRIVILEGES ( READ);
DECLARE RP VARIABLE "OLTP_USER" AS "OLTP_USER" UPGRADE ID '0023844d-0bd7-0000-714b-e31d00000000' EXPRESSION {'APPS'}
        DESCRIPTION {Database user used to connect to the transaction database. }
        PRIVILEGES ( READ);
DECLARE RP VARIABLE "OLTP_DSN" AS "OLTP_DSN" UPGRADE ID '00238450-0bd7-0000-714b-e31d00000000' EXPRESSION {'DEV2'}
        DESCRIPTION {Data Source Name of the Transaction Database.}
        PRIVILEGES ( READ);
DECLARE RP VARIABLE "ORA_EBS_OLTP_USER" AS "ORA_EBS_OLTP_USER" UPGRADE ID '80b43a91-0bd7-0000-714b-e31d00000000' EXPRESSION {'APPS'}
        PRIVILEGES ( READ);
DECLARE RP VARIABLE "ORA_EBS_OLTP_DSN" AS "ORA_EBS_OLTP_DSN" UPGRADE ID '80b43a9c-0bd7-0000-714b-e31d00000000' EXPRESSION {'DEV2'}
        PRIVILEGES ( READ);