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 ...........
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 Cancelled Invoice 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 which is available to be used but not deployed in the database.
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 Cancelled Invoice 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 which is available to be used but not deployed in the database.
No comments:
Post a Comment