OBIEE BEST PRACTICES

REPOSITORY:

1. create aliases for  all tables and prefix their names with a text that reflects the type of table

Ex: Dim_Product, Fact_Sales

2. Create physical diagram using aliases, not the Imported ones
3. Avoid Circular Joins

Circular join have big impact on the data integrity. All ways avoid circular joins by creating aliases

4. Connection Pool Configuration
1. Use native database drivers instead of ODBC
2. Set the max no of connection high enough. Recommendation is 10-20% concurrent users multiplied by no of queries per dashboard
3. Use separate connection pool for initialization blocks

5. Enable query limits to avoid the slow down the BI Server and the database with bad report that extracts millions of records.
6. In BMM layer create Logical star schemas only. No snow flake
7. Use separate logical dimension table for each dimension. Don’t merge tables. This is same for the fact table also
8. Assigning business column as dimension primary key
9. Rename the logical column names to business names
10. Remove un used columns from the BMM layer
11. Every fact logical should have aggregation rule set
12. The primary key of each level must be Unique
13. The primary key of the lowest level of the hierarchy must always be the primary key of the logical table
14. Always create a dimension hierarchy for all dimension tables, even if you have only one level in the dimension
15. Always specify the number of elements per level. BI Server is uses this for identifying the aggregate tables. It doesn’t be accurate.
16. Always specify the content level in all logical table sources, both in facts and dimensions. It will allow BI Server to select the most optimized LTS in queries.
17. Set up an implicit fact column for each presentation folder. It prevents users from getting wrong results if they create a report without fact column.
18. Maintain a common time dimension for enterprise level.