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
No comments:
Post a Comment