Best Practices

A single table often has multiple 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, if you use table aliases and follow 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.

  
  
  
  Description of the illustration sm_design_alias_table.png
  
  

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

  Here are two common examples

  Example 1

  
  Description of the illustration ceal_table_alias_example1.jpg

  Example 1 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

  Example 2 shows 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:

  
  Description of the illustration ceal_table_alias_example2.jpg

  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:

  
  Description of the illustration ceal_table_alias_example2_with.jpg

  Now there's 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. Use one logical table source for the Invoice Fact Star and the other for the Order Fact Star.

  
  Description of the illustration ceal_table_alias_logical_tables.jpg

Summary

• 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.