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
