Monday, 15 July 2013

Why are we creating Alias table?


The following points are the main reasons to create an alias table:
  • To reuse an existing table more than once in your physical layer instead of importing it several times.
  • 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 or will help us to avoid triangular or circular joins.
    For example, an order date and a shipping date in a fact table may both point to the same column in the time dimension data source table, but you should alias the dimension table so that each role is presented as a separately labeled alias table with a single join. These separate roles carry over into the business model, so that "Order Date" and "Ship Date" are part of two different logical dimensions. 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.
  • To help you design or model the repository with star or snowflake structures in the BMM layer.
  • Alias tables are critical in the process of converting ER Schemas to Dimensional Schemas.
  • By creating alias table, it will help us to follow the 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 leave the original physical table names unchanged. Some organizations alias all physical tables to enforce best practice naming conventions. In this case, as we mentioned in point 2, all mappings and joins are based on the alias tables rather than the original physical tables. 

1 comment:

  1. Everyone in bi knows this...

    ReplyDelete

Related Posts Plugin for WordPress, Blogger...

ShareThis