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.