In this
post I am sharing some of the best practices to follow while we are in OBIEE
solutions.
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 particularly useful
when there
is confusion between DATE and DATETIME.
3. 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.
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 statements) in Physical Layer.
6. Create Tables
(recommended) or Materialized views in data-warehouse instead
7.
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)
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 (Logical
Table Source) ‘Where’
clause content filter or as request filter in Reports
12.
Facts should not be joined . This will result in Cartesian Product
leading to double
counting and summing.
13. Use conforming
Dimensions instead
14. Connection Pool
considerations (15-18)
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 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 !
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. This will ensure
in finding
which entity was added in which release
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
