Business Intelligence – Oracle

Archive for April 4th, 2008

Oracle BI EE 10.1.3.3/2 – UDML to automate repository Updates – Migration of Repositories from Development to Test/Production Environment

Posted by Venkatakrishnan J on April 4, 2008

One of the very important features of BI EE is its ability to expose the entire repository in the form of UDML. There are 2 utilities that are available for any user to expose the repository as UDML. They are nqudmlgen.exe and nqudmlexec.exe. Though the use of UDML is not supported by Oracle support, this can come in very handly in many situations. Before proceeding further i would recommend everyone to go through Mark’s blog entry here first which has details on what these utilities can do. One of the major advantages of these utilities is to streamline the process of migrating from Development to Test or Production environments. For example, when we migrate from Development to Test or Production there would be changes in Variable default values, Users, Connection Pool entries etc. Typically the methodology to update these values is to open up the Admin tool(from a windows box) and make the changes manually. But this can become very cumbersome and error prone as and when the number of changes involved become more and more. In order to automate this process of repository migration, the above utiltity nqudmlexec.exe can be very handy. Lets look at an example of how to automate the process of repository migration.

Consider the following scenario,

······

As you see above, there are 2 instances of BI EE. I have oversimplified the differences in the repositories of the Dev and Test machines. But this should give you an idea of the changes that you might encounter while moving from a dev environment to a test environment. So the changes that we have to make on the Development Machine’s repository (to move it to a test instance) are as follows

1.···Change the default value of repository variable BI_EE_HOME.
2.···Remove users B and C.
3.···Update the tnsnames entry of the connection Pool from ORCL to SALES.

In order to achieve this lets start with the first one (to change the repositor variable BI_EE_HOME value. In order to do this, first generate the UDML of the development repository using nqUDMLgen.exe.

nqudmlgen -U Administrator -P Administrator -R D:\oracle\OracleBI\server\Sample\nqUDMLExec\Dev_Machine.rpd -O D:\oracle\OracleBI\server\Sample\nqUDMLExec\Dev_Machine.UDML

······

Open this UDML file and search for CREATE RP VARIABLE “BI_EE_HOME”. This will give you the line containing the command for updating/creating a repository variable. So your command would look like this

DECLARE RP VARIABLE “BI_EE_HOME” AS “BI_EE_HOME” UPGRADE ID 817 EXPRESSION {‘D:\\oracle\\OracleBI’} PRIVILEGES ( READ);

······

Change the above command to reflect the values in the test machine. i.e.

DECLARE RP VARIABLE “BI_EE_HOME” AS “BI_EE_HOME” UPGRADE ID 817 EXPRESSION {‘/u01/oracle/OracleBI’} PRIVILEGES ( READ);

Copy this command into a text editor. Now, open the development repository and click on Tools – Query Repository. Query all the users and find the ids of the users of B and C (which we do not need in the test repository).

······

Now, add the below 2 commands to the above DECLARE command in the text editor

DELETE 1501:1019;
DELETE 1501:1020;

Once this is done, open the UDML file generated in the first step and search for DECLARE CONNECTION POOL “ORCL”. This will give you the command for updating/creating the desired connection pool.

DECLARE CONNECTION POOL “ORCL”.”Connection Pool” AS “Connection Pool” UPGRADE ID 831
DATA SOURCE {ORCL}
TIME OUT 300
MAX CONNECTIONS 10
TYPE ‘OCI10G’
USER ‘sh’
PASSWORD ‘D7EDED84BC624A917F5B462A4DCA05CDCE256EEEEEDC97D54A286E822D97C35C7AD5C43AD4F2A09EAC4D07C3A079829F’
SHARED LOGIN
CONNECTIONS TO SAME URI 10
OUTPUT TYPE XML
HEADER PATH {D:\\oracle\\OracleBI\\server\\config\\NQSQueryHeader.xml}
TRAILER PATH {D:\\oracle\\OracleBI\\server\\config\\NQSQueryTrailer.xml}
BULK INSERT BUFFER SIZE 32768 TRANSACTION BOUNDARY 10
TEMP TABLE PREFIX {TT} OWNER {}
PRIVILEGES ( READ);

Change the above command according the values in the test machine and append it to the other commands that we used above. So, our command file would look like this

DECLARE RP VARIABLE “BI_EE_HOME” AS “BI_EE_HOME” UPGRADE ID 817 EXPRESSION {‘/u01/oracle/OracleBI’} PRIVILEGES ( READ);

DELETE 1501:1019;
DELETE 1501:1020;

DECLARE CONNECTION POOL “ORCL”.”Connection Pool” AS “Connection Pool” UPGRADE ID 831
DATA SOURCE {SALES}
TIME OUT 300
MAX CONNECTIONS 10
TYPE ‘OCI10G’
USER ‘sh’
PASSWORD ‘D7EDED84BC624A917F5B462A4DCA05CDCE256EEEEEDC97D54A286E822D97C35C7AD5C43AD4F2A09EAC4D07C3A079829F’
SHARED LOGIN
CONNECTIONS TO SAME URI 10
OUTPUT TYPE XML
HEADER PATH {D:\\oracle\\OracleBI\\server\\config\\NQSQueryHeader.xml}
TRAILER PATH {D:\\oracle\\OracleBI\\server\\config\\NQSQueryTrailer.xml}
BULK INSERT BUFFER SIZE 32768 TRANSACTION BOUNDARY 10
TEMP TABLE PREFIX {TT} OWNER {}
PRIVILEGES ( READ);

Save this file. Now, invoke the nqudmlexec to update the Development repository with the UDML command file.

nqudmlexec -U Administrator -P Administrator -I D:\oracle\OracleBI\server\Sample\nqUDMLExec\Migration_UDML.UDML -B D:\oracle\OracleBI\server\Sample\nqUDMLExec\Dev_Machine.rpd -O D:\oracle\OracleBI\server\Sample\nqUDMLExec\Test_Machine.rpd

······

Now, after the execution of the above command, you would see a new repository called as Test_Repository which would contain the changes.

······

······

······

Posted in All Posts, OBI EE Plus | 9 Comments »