1. Building Semantic Models in Oracle Analytics Server
  2. Create and Build Your Model
  3. Build a Semantic Model's Physical Layer
  4. About Physical Alias Tables

About Physical Alias Tables

An alias table is a physical table that uses an alternative name to references another physical table as its source. Creating alias tables lets you to reuse an existing table more than once so you don't have to import the table into the physical layer several times.

The primary reasons to use alias tables are:

  • To set up multiple tables, each with different keys, names, or joins, when a single data source table needs to serve in different semantic roles. Setting up alias tables in this case is a way to avoid triangular or circular joins.

    For example, suppose you have a fact table in which the order date and shipping date both point to the same column in the time dimension data source table. In this case you can alias the dimension table so that each role is presented as a separately labeled alias table with a join. These separate roles carry over into the business model, so that Order Date and Ship Date are part of two different logical hierarchies. If a single logical query contains both columns, the physical query uses aliases in the SQL statement so that it can include both of them.

    You can also use aliases to enable a data source table to play the role of both a fact table and a dimension table that joins to another fact table, often called a fan trap.

  • To include best practice naming conventions for physical table names. For example, you can prefix the alias table name with the table type such as fact, dimension, or bridge, and not change the original physical table names. Some organizations create alias tables for all physical tables to enforce best practice naming conventions. In this case, all mappings and joins are based on the alias tables rather than the original tables.

Alias table names appear in physical SQL queries. Using alias tables to provide meaningful table names can make SQL queries referencing those tables easier to read. For example: 

WITH
SAWITH0 AS (select sum(T835.Dollars) as c1
from
     FactsRevT835/*AllRevenue(Billed Time Join)*/)
select distinct 0 as c1,
     D1.c1 as c2
from
     SAWITH0 D1
order by c1

In this query, the meaningful alias table name A11 Revenue (Billed Time Join) has been applied to the terse original physical table name FactsRev. In this case, the alias table name provides information about which role the table was playing each time it appears in SQL queries.

Alias tables can have cache properties that differ from their original tables.

Because the alias table's columns are automatically synchronized with the original table, you can't add, delete, or modify columns in an alias table. Synchronization ensures that the original tables and their related alias tables have the same column definitions. For example, if you delete a column in the original table, the column is automatically removed from the alias table.

You can't delete an original table unless you delete all of its alias tables first. Alternatively, you can select the original table and all its alias tables and delete them at the same time.

You can change an alias table's original table if a new original table is a superset of the current original table. However, this could result in an inconsistent semantic model if changing the original table deletes columns that are being used. Running consistency check identifies orphaned aliases.

Alias tables inherit some properties from their original tables. Some of the properties that the alias table gets from the original table can't be changed in the alias table. Such properties are grayed out in the alias table properties. If the original table changes its value for a grayed out property, the same property change displays for the alias table.