Alias vs Duplicate

A point of confusion sometimes arises over the difference between a Table Alias and a Duplicate Table.

The Alias Table

A Table Alias is created from a physical table, in the physical model of the repository, and any changes in the physical are immediately reflected in the alias. It is good practice to only use alias tables to build the Business model in the repository. You can rename your alias to be something more meaningful that will help other users understand it's purpose. Another use for alias tables is to resolve circular joins.

Creating an alias creates a copy of the table in metadata that will be referenced in SQL with that alias name. It will have its own ID, distinct from the parent table.

For example:
Parent Table Invoice -- id 1000
Alias Table CancelledInvoice -- id 2021

when the OBIEE engine writes the physical query it resolves the true table names, but looking at the query generated you will see:
.......
FROM INVOICE T1000 ,
INVOICE T2021 /*   CancelledInvoice  */
WHERE ...........

The Duplicate Table

Duplicate Tables on the other hand allow us to create the structure or template of a table, but at the database level you will find no physical structure. When creating an opaque view where the structure is the same as a table in the physical layer then we duplicate the table.

Using the Invoice example from above we can create a duplicate table in the physical layer. We will change the name of the table to CancelledInvoice and then the table type is set to "SELECT", and the details as "select * from Invoice where canc = 'Y'". This gives us an opaque view whic is available to be used but not deployed in the database.

What is the Purpose of an Alias Table?

An Alias table (Alias) is a physical table with the type of Alias. Physical aliases can be mapped to physical tables, stored procedures, and select statements. An alias table can be a reference to any of these table source types.


Alias Tables can be an important part of designing a physical layer. Here are some of the main reasons to create an alias table:
-To rename physical tables allowing them to be easily identified in the business model layer.
-By renaming you can impose a form of ordering on the tables shown in the physical layer.
-To reuse an existing table more than once in your physical layer (without having to import it several times)
-To set up multiple alias tables, each with different keys, names, or joins
-To help you design sophisticated star or snowflake structures in the business model layer. Alias tables are critical in the process of converting ER Schemas to Dimensional Schemas.

You need to create aliases to
-Eliminate physical joins that cross dimensions.
-Eliminate circular joins.