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