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

Author: Daan Bakboord

I am an Oracle Big Data Analytics Consultant with great interest in anything closely related to the Oracle Big Data Analytics (OBIEE, BICS, OAC, Big Data, Data Integration, Data Visualization, Data Management, Data Architecture).

Leave a Reply

Please log in using one of these methods to post your comment:

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