OBIEE Modeling Best Practices


Some more points are going to be added to my good book of Best Practices as below : This could be considered as amendment on existing Best practice that I already had posted few weeks back .See here

Physical Layer
• Eliminate all Circular Joins that either cross dimensions or are contained within a single dimension. Use Physical Layer table Aliases to duplicate certain tables and modify joins so that the Circular Join is removed. (Should have 1 less join than there are tables in physical SQL generated)
• Do not include filters in the Physical layer when creating joins – instead build them into the Business Model on the Logical Tables Sources
• Never model Fact-to-Fact Joins. The proper modeling technique is to let analytics choose more than one fact table in a sub query, and let the Analytics Server or the Database join the result set. (Drill Across scenarios)
• Cross database joins should not exist. (Performance Issues)
• Make all tables “cacheable.” Set cache persistence time as “infinite” and utilize polling tables for refreshes

Business Model Layer
• Only use 1:M complex joins between Logical Dimension Tables and the Facts.FK Joins limits the flexibility and power of the SQL generation engine.
• Identify the base granularity of a logical table, both Dimension and Fact. Only add physical tables to the logical table source (LTS) that are at the same or higher levels of granularity
• Ensure that all levels of a hierarchy have an appropriate value for the Number of elements field. This will enable the Analytics server to select the most optimal pre-aggregated fact tables to use
• Avoid outer joins within LTS as they are always included in the query, even if it is not used. If necessary, create one LTS without the Outer Join and another with the Outer Join. Order the Outer Join LTS after the Non Outer Join so that it will be used only when necessary.
• Always verify the SQL generated is correct.
Presentation Layer
• Ensure that aliases for Presentation layer columns and tables are not used.Aliases are useful when making changes to a production system, but for a first time deployment should be removed.
• Structure Presentation Catalog around the Facts. Make sure that only the dimensions supported by the Facts are part of the Presentation Catalog
• Avoid technical labels in the presentation Layer.

General
• Minimize number of Initialization blocks. Combine where possible
• Only use Logging level > 0 during development and debugging
• Move as much of the query logic to the ETL as possible to improve system response time. Pre-calculation of additive metrics and attributes will reduce query complexity and therefore response time
• Denormalize dimensions and Fact table FKs as much as possible to reduce joins
• Try to eliminate all outer joins in a Data Warehouse by doing ETL lookups and replacing with default (commonly ‘Unknown’) parents. This will simplify the Business Layer, ensure a consistent record set, and improve performance
• Design Business Model Layer to support ad-hoc reporting instead of catering to specific reports. This will enable end users to perform their own analysis and create their own reports as needed