Business Intelligence – Oracle

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.

······

······

······

About these ads

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

  1. [...] Here it is : http://oraclebizint.wordpress.com/2008/04/04/oracle-bi-ee-101332-udml-to-automate-repository-updates… [...]

  2. [...] utility. See this posting on this blog on UDML and repository migration and merging, and this posting by Venkat on automating changes to connection pool settings using UDML during a [...]

  3. Min-Yu Yang said

    Does anyone know how I can set the user password in a Connection Pool using UDML? As in

    DECLARE CONNECTION POOL “ORCL”.”Connection Pool” AS “Connection Pool” UPGRADE ID 831
    USER ’sh’
    PASSWORD ‘D7EDED84BC624A917F5B462A4DCA05CDCE256EEEEEDC97D54A286E822D97C35C7AD5C43AD4F2A09EAC4D07C3A079829F’

    The password is apparently encrypted. If I need to run an automatic script to periodically or on demand to update the connection pool password from some data source, how do I do that?

    Thanks!

    -Minyu

  4. [...] by Venkatakrishnan J on May 2, 2008 If you had gone through my blog entry here, i would have talked about using UDML as a way for automating migration from dev to test/prod [...]

  5. Dawn Schreiner said

    Venkatakrishnan J,
    Is there a parameter for the nqudmlgen utility that will include the internal numbers for a user that can be formulated into a “DELETE 1501:123454;” in a program such as awk? Or an alternative DELETE USER command that doesn’t require the internal number, like using Name?

    Thanks,
    Dawn

  6. Ayyappan said

    Do a grep of “DECLARE USER” on the generated UDML to get list of usernames.
    It will list lines like [DECLARE USER "Administrator"]. Change the DECLARE to DELETE, make it UDML like
    DELETE USER “Administrator”;

    run this udml against the rpd, it will delete the users, for example here Administrator will be deleted from .rpd

  7. [...] UDML to automate repository Updates – Migration of Repositories from Development to Test/Productio… [...]

  8. rnm1978 said

    @ Min-Yu Yang : use the undocumented AdminTool.exe /command feature – google it for more info

  9. Mehow said

    Hi Venkat,

    Thanks for the your useful information.Can you merge twi different repositories in an automated fashion?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 151 other followers

%d bloggers like this: