Wednesday, April 13, 2011

OBIEE Interview Question- Alias Table

What are the benefits of using Aliases in OBIEE?

It is a best practice to create aliases for all tables. Aliases are created in the Physical Layer of the repository. Some of the benefits of using aliases are given below:

·        You can create a join relationship which is different than the actual foreign key relationship in the database. This helps in defining relationships to meet business model criteria.
·         An alias is used to avoid circular joins and to configure self joins. (for more details on Fan Trap and Circular Joins check this blog.)
·         Aliases can function in numerous ways to define business model e.g. a table can act both as a dimension and a fact using aliases.
·         You can rename aliases to make physical and business model easier to understand. 

Click here to read more about aliases in Oracle Documentation. 
More information in this wiki/blog: http://gerardnico.com/wiki/dat/obiee/alias

Reference: Oracle® Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1 (11.1.1)

Cheers!
Deepak

2 comments:

  1. Hey Deepak,

    When we create the Alias A1 for a Dimention Table A, on what keys do we tables A and A1 in case there are many PKs in A? Can you please shed some light on this?

    ReplyDelete
  2. Hi Srikanth,

    It will depend on what you are trying to achieve. First of all there is only one Primary key per table, of course there can be multiple columns uniquely defining a row. As an example for your your situation, let’s say you have an Employee table which you want to act as both Employee and Supervisor table. You will create an alias of Employee table EMP (emp_id, emp_name, supervisor_id…) say SUPVSR ((emp_id, emp_name, supervisor_id…). Now join on EMP.emp_id = SUPVSR.supervisor_id to get Supervisor and Employees under her. Hope it helps.

    Cheers!
    Deepak

    ReplyDelete