Oracle Analytics Best Practices: Physical Table Alias

May 19, 2021 | 4 minute read
NICOLAS BARASZ
Director, Analytics Customer Excellence
Text Size 100%:
This blog is part of a series of “best practice” blogs for Oracle Analytics. 

In Oracle Analytics repository (RPD), you can create aliases of tables in the physical layer. This blog describes when to create tables aliases and why, with some common examples.

 

Best Practices

In Oracle Analytics, there are many situations where a single table has different roles. Sometimes a table is used as a dimension, sometimes as a fact table, sometimes to extend another dimension to retrieve a specific attribute, and sometimes as a helper table to join two other tables together.

Often, each role comes with a different set of physical joins. If you configure all the joins on a single instance of the table, it results in data integrity issues. You can avoid such issues by using aliases and following some basic rules.

 

  • Use a consistent naming convention for aliases.

The alias name should include both the name of the original table, and some indication of the role of the alias. This way on first sight, developers immediately know which table is being used and understands the purpose of the alias.

  • Don’t define any physical joins on the original table.

Start by creating an alias. Each physical table should always have at least one alias. Only the alias will be used, not the original table. This way if you need new instances of the same table for other roles in the future, it’s easy to identify the differences and roles of each alias.

 

 

 

 

 

  • Create additional aliases when you need different physical joins depending on the context in which a table is used.

Here are two common examples.

The first example shows an implementation of the Employee table. Table W_MARKET_D includes the key of the employee who is the Market Manager. Table W_PRODUCT_D includes the key of the employee who is the Product Manager. Without any alias, table W_EMPLOYEE_D joins to both W_MARKET_D and W_PRODUCT_D. If you create a report that selects the name of both the Market Manager and Product Manager, the where clause generated in the physical SQL would include the following statements:

W_MARKET_D.EMP_ID=W_EMPLOYEE_D.ID and W_PRODUT_D.EMP_ID=W_EMPLOYEE_D.ID

This means that the ID of the employee must at the same time equal the Market Manager ID and Product Manager ID. This isn’t possible because these managers are two different employees, so the query doesn’t return any records.

Instead, as described on the diagram above, the solution is to have two aliases of the employee table. One alias is joined with the Market table and the other is joined to the Product table. These two aliases are considered as if they are two different tables, completely independent from each other. By using two aliases, there is no conflict between the two joins.

Example 2

In the second example, there are three tables. Table W_ORDER_F is used as a fact table for order metrics, a dimension for order attributes, and it includes the Order Date. There is also calendar table W_DAY_D, and invoice table W_INVOICE_F that includes Order ID and Invoice Date. The Invoice table is joined to the Order table to retrieve Order attributes as a dimension for Invoice Fact metrics. Note that Oracle Analytics generates separate sub-queries for each fact table. Therefore, we must consider Order Fact star and Invoice Fact star separately, as shown in the diagram.

Without any aliases, the diagrams look like this:

This configuration causes similar data integrity issues to the Employee example, that is, the Order Date is not equal to Invoice Date but they are both joined to the same date column on the calendar table.

The solution is to create two aliases for the Order table, one alias for the fact and the second alias for the dimension. With aliases, the diagrams look like this:

Now there is no conflict between the joins, as the dimension alias of the Order table is not joined to the calendar dimension.

Also note that there’s no need to join Fact_W_ORDER_F with Dim_W_ORDER_D. Except for rare specific situations, you should never join two aliases of the same table together. While doing so doesn’t impact data integrity, it does impact performance and it’s useless.

Instead, create two logical table sources in the Order Dimension in the business model as shown here. Use one logical table source for the Invoice Fact star and the other for the Order Fact star.

 

 

 

 

 

 

Summary

In this blog you learned the following about table aliases:

  • Always create at least one alias for each physical table.
  • If needed, create additional aliases based on the different roles of the table in your model and the different types of joins you require.
  • Although there are exceptions, in most cases you shouldn’t join two aliases of the same table together.

NICOLAS BARASZ

Director, Analytics Customer Excellence


Previous Post

Meet the 100+ Oracle Analytics Leaders of 2021

Benjamin Arnulf | 6 min read

Next Post


Oracle Analytics Best Practices: Business Model Design

NICOLAS BARASZ | 3 min read