UDML – Scripting

One of my clients wants to add loads of Security Groups into the repoitory. Of course this can be done manually, but in this case it would be better to script these groups into the database. I knew that UDML is an undocumneted feature in Oracle BI 10g. If you perform a search on Google, you could find enough information. I used the information provided by Venkat and Andreas.

There was one thing I couldn’t find out directly. I was looking for a possibility to nest Security Groups. It was not clear to me what the syntax should be. Again UDML to the rescue. You can use to script something into the repository. The other way around works as well. What I did was, I created a nested Security Group manually.

No I was able to extract the UDML-syntax from the repository;

G:\Oracle\10g\OracleBI\server\Bin\nQUDMLGen.exe -U Administrator -P Administrator -R "Y:\webLog\OBIBB\OBIBB - UDML\groupImport.rpd" -O "Y:\webLog\OBIBB\OBIBB - UDML\securityUDML.txt"  -S

 The ‘-S’  is for generating script for only security objects.

Output for ‘securityUDML.txt’ is as follows;

DECLARE REPOSITORY PROPERTIES (
 'CustomPresentationLayer' = '01',
 'PersistedNextUpgradeID' = '0A000000');
VERSION 1.1.184;
DECLARE SECURITY ROLE "Administrators" AS "Administrators" UPGRADE ID 2
 HAS USERS (
    "Administrator" )
 PRIVILEGES ( READ);
DECLARE SECURITY ROLE "Group01a" AS "Group01a" UPGRADE ID 4
 INHERITS FROM (
    "ManualGroup" )
 PRIVILEGES ( READ);
DECLARE SECURITY ROLE "Group01b" AS "Group01b" UPGRADE ID 6
 INHERITS FROM (
    "ManualGroup" )
 PRIVILEGES ( READ);
DECLARE SECURITY ROLE "ManualGroup" AS "ManualGroup" UPGRADE ID 9
 PROPAGATES TO (
    "Group01a",
    "Group01b" )
 PRIVILEGES ( READ);
DECLARE USER "Administrator" AS "Administrator" UPGRADE ID 3 FULL NAME {} PASSWORD 'D7EDED84BC624A917F5B462A4DCA05CDCE256EEEEEDC97D5213DF9555A8D6E566A4A72028AAD1FC28AA7433B66F722D0CEE88C996D2D894F' NEVER EXPIRES
 HAS ROLES (
    "Administrators" )
 PRIVILEGES ( READ);

Looking add this output, you see that the subgroup ‘INHERITS FROM’ the parentgroup. The parentgroup ‘PROPAGATES TO’ the subgroup.

Now using the following script I should be able to import subgroups and parentgroups into the repository:

DECLARE SECURITY ROLE "Group01a" AS "Group01a"
 PRIVILEGES ( READ);
DECLARE SECURITY ROLE "Group01b" AS "Group01b"
 PRIVILEGES ( READ); 
DECLARE SECURITY ROLE "Group02a" AS "Group02a"
 PRIVILEGES ( READ);
DECLARE SECURITY ROLE "Group02b" AS "Group02b"
 PRIVILEGES ( READ);
DECLARE SECURITY ROLE "Group01" AS "Group01"
PROPAGATES TO ("Group01a", "Group01b")
PRIVILEGES ( READ);
DECLARE SECURITY ROLE "Group02" AS "Group02"
PROPAGATES TO ("Group02a", "Group02b")
PRIVILEGES ( READ)
;

By using the nqudmlexec-executable, you should be able to import the parentgroups (“Group01” , “Group02”) and subgroups(“Group01a”, “Group01b”, “Group02a”, “Group02b”) into the repository.

I hope the same is possible for Catalog Groups. More to come.

Change a Physical table to an Alias of a different Physical table

Sometimes you have to make some changes in your physical model, because of changes in the underlying datamodel. This could be a lot of work. In this case UDML could come in handy. I am sure Andreas Nobbmann will agree.

Picture the following;

You have created a physical table with relationships.

When you want to change the actual source of your  physical table you realize that you should have used an alias instead of the actual physical table.

What to do?

With the following scripting you can change the physical table to an alias of a different physical table. This could also be used to change the source of an already existing alias or multiple aliases, or add columns etc.

For an example and reference of how to use udml to change a repository variable script-wise, please check Venkat’s ‘old’ blog.

Step 1.

Produce a UDML script of your source repository:

C:\oracle\bise1\bi\server\Bin>nqudmlgen -U Administrator -P Administrator -R C:\oracle\bise1\bi\server\Repository\OBIBB.rpd -O C:\temp\udml\source.udml

Look for the definition of your physical table in source.udml. The definition runs from “DECLARE TABLE” until “PRIVILEGE ( READ);”

Step 2.

Copy the statement to a seperate text-file:

Step 3.

Modify the statement and add new source to physical table:

 

In essence the physical table “DIM_TABLE_CONF_1” will become an alias which references a different physical table “DIM_TABLE_CONF_3”.

Save as update.udml

Step 4.

Execute update:

nqudmlexec -U Administrator -P Administrator -I C:\temp\udml\update_source.udml -B C:\oracle\bise1\bi\server\Repository\OBIBB.rpd -O C:\oracle\bise1\bi\server\Repository\updated_OBIBB.rpd

Step 5.

Check the result in the updated repository, in this case; updated_OBIBB.rpd

Bookmark and Share