OBIEE Best Practices in BMM Layer

1. Minimize the use of Snow-Flakes. Always go for Star Schema's.

2. Always use Complex joins here. It allows OBI Server to make best decision about the exact physical SQL to be generated based on Logical query Path. In contrast to a Physical FK join, these forces a single join path between tables. If joined tables were dragged from Physical Layer, replace FK Joins with complex Joins.

3. Create Dimension Hierarchies for every Dimension in the Business Model

4. Even if a meaningful hierarchy definition cannot be thought of, just create one with the Grand Total Level and Detail Level.

5. For Dimension Hierarchies the ‘Number of Elements at this level’ should increase        from 1 at Grand Total to the corresponding distinct values at each level. This can be approximate values; need not be the exact ones.

6. Define Keys at each level of the Hierarchy.

7. The Content tab of each of the LTSs in Fact should be set to the related Dimension’s Logical Level.

8. Combine all attributes that describe a single entity into a single Logical table.

9. Never delete logical columns that map to keys of Physical dimension tables.

10. Don’t keep unwanted Physical columns in the Logical Layer.

11. Give Meaningful Names to the Logical Columns. Avoid assigning a logical column the same name as a logical table or Business Model object.

12. Make proper use of the where clause Content filter of the LTS to minimize number of records returned.

13. Minimize the use of Conditional Checks and ‘CASE WHEN’ usage in the formula of Logical Columns. This will affect performance. Instead make proper use of the where clause Content filter of the LTS if the condition applies to all the columns/measures in the logical table

14. When Creating a logical column based on other logical columns , make sure all the columns in the expression is from the Same logical table, same Logical Table Source.

15. Make proper distinction between Count and Count Distinct. If you are counting on a unique value column don’t use Count Distinct. This will affect performance

16. Minimize the use of Outer joins within LTS. This is resource consuming. Use default zero ROW_WID records at the database instead.

17. Make sure a particular Report only refers one LTS in a Logical Table. Or the different LTSs should be at the same level

18. Avoid dimensions in Fact tables and avoid measures in Dimension Tables

19. Create Display folders to group tables according to STAR or Releases

20. When using Out-of-the -Box Vanilla RPD, remove unwanted Logical Tables and Hierarchies. This will minimize the time needed for Consistency Check

21. Specify the most Economical Source when there are multiple LTSs for a Dimension

22. Whenever you do Consistency Check, Right Click the Changed Business Model Object and go for Check Consistency rather than using the Global Consistency Check. This will minimize the time needed for Consistency Check

23. Arrange the logical columns alphabetically. This will save time when you revisit.

24. Fix the warnings if any, don’t ignore it

Happy Blogging,

Bose