Best Practices in Physical Layer(OBIEE)

In this post I am sharing some of the best practices to follow while we are in OBIEE solutions.

•  Try to always import tables and columns into Physical layer rather than creating it manually.
•  This will ensure correct data types are set for each column. This is particularly useful when there is confusion between DATE and DATETIME.
• For each Physical Dimension table there should be a Primary Key and only one. For Fact Tables, there is no need to create a Primary Key.
• If only composite key is present create a single Physical key and add all the composite key columns in it.
• Minimize Opaque Views (SELECT statements) in Physical Layer.
• Create Tables (recommended) or  Materialized views in data-warehouse instead.
• Always use Foreign Key Joins in the Physical layer. Avoid using complex joins with conditions.  Complex joins are not good for performance and should be avoided. (there are a few exceptions for this case when we work with Type 2 SCD).
• Always try to use Number-Number join. This will work faster than a varchar-varchar join.
• Avoid using CAST functions in the join expression. This will destroy the usability of the Database indexes created on that column.
• Avoid any filter conditions in the Join.
• These filter conditions can in turn be added in the LTS (Logical Table Source) ‘Where’ clause content filter or as request filter in Reports.
• Facts should not be joined . This will result in Cartesian Product leading to double counting and summing.
• Use conforming Dimensions instead.
• Connection Pool considerations (15-18).
• Require fully qualified table names should be unchecked.
• Enable Connection Pooling should be checked.
• Execute queries asynchronously should be checked.
• Create a separate Connection Pool for Initialization Blocks.
• Keep Cache persistence time of all tables as Infinite.
• The columns used in Joins should be set to “NOT NULL”.
• The database Features tab should be set correctly with the Parameters supported by your backend database.
• If both are not in-sync then lot of processing will be done in the BI Server instead of the Database. This affects Performance. Pay particular attention to Locale. (They are case-sensitive).Mismatch of Locale can cause the sorting to be done in OBI Server instead of DB and performance take a bad hit!
• DERIVED_TABLES_SUPPORTED in database features tab should be  checked for Oracle Databases. This will ensure that Proper function shipping will happen to the DB in case of TOP(N) and Rank functions.
• Create Display folders to group tables according to STAR or Releases.
• Set Different Icons on objects for each Release of the Code. This will ensure in finding which entity was added in which release.
• Don’t Leave the Description field empty. Write some meaningful descriptions of the Object. This will help a lot in later trouble-shooting and Impact Analysis.