OBIEE Best Practices

Post date: 07-Oct-2010 12:43:48

OBI Physical Layer

1. "Try to always import tables and columns into Physical layer rather than creating it manually.

2. This will ensure correct data types are set for each column. This is particulary useful when there is confusion between DATE and DATETIME"

3. "For each Physical table there should be a Primary Key and only one.

4. If only composite key is present create a single Physical key and add all the composite key columns in it."

5. "Minimize Opaque Views (Select) in Physical Layer.

6. Create Materialized views in database instead."

7. Always use Foreign Key Joins in the Physical layer

8. Always try to use Number-Number join. This will work faster than a varchar-varchar join.

9. Avoid using CAST functions in the join expression. This will destroy the usability of the Database indexes created on that column.

10. "Avoid any filter conditions in the Join.

11. These filter conditions can in turn be added in the LTS Where clause content filter or as request filter in Reports."

12. "Facts should not be joined together.

13. Use conforming Dimensions instead."

14. "Connection Pool:

15. ""Require fully qualified table names"" should be unchecked

16. ""Enable Connection Pooling"" should be checked

17. ""Execute queries asynchronously"" should be checked"

18. Create a separate Connection Pool for Initialization Blocks

19. Keep Cache persistence time of all tables as Infinite

20. The columns used in Joins should be set to "NOT NULL"

21. "The database Features tab should be set correctly with the Parameters supported by your backend database.

22. If both are not in-sync then lot of processing will be done in the OBI 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."

23. 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

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

25. "Set Different Icons on objects for each Release of the Code.

26. 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"

OBI BMM Layer

1. Minimize the use of Snow-Flakes. Always go for Star Schemas.

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, which 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 LTS

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

OBI Presentation Layer

1. Catalog should map to one BMM Object only

2. Use Parent Folders and Sub folders to group Facts and similar Dimensions together

3. Avoid the use of Aliases when a new Presentation Column is created

4. The Presentation Columns in a table should be sorted alphabetically if no specific order is asked by the customer

5. Get Customer Sign-off of the Presentation layer structure before building reports. This will avoid later replacements of columns which affects the reports constructed.

6. Make proper use of the Permissions in this layer

7. Don't use Double quotes (") in Column name, though its permitted

8. Presentation columns should not have the same name as Presentation Table.

9. Eliminate unneeded objects to reduce user confusion

10. Limit # of objects in folder to 7-12

11. Use Object  description field to convey information to users

12. Keep names short to have space on reports

Datawarehouse

1. Siebel Recommends STAR schema and data warehouse should have it though more effort has to be put in creating those.

2. De-normalize and De-normalize. This would be better rather than more joins happening when a report is run

3. Make sure that indexes are created on the Primary keys, Foreign Keys and the commonly used columns for filtering

4. Create LOV Tables for commonly used columns in Dash. Prompts

5. Create Mini-Dimensions for commonly used columns in Dash. Prompts if LOV is not suitable

6. Complex metrics should be pre calculated in ETL and made readily available as columns

7. Since OLAP involves large data read operations, the Block sizes and Buffer Sizes in database should be set accordingly.