Repository Creation
Important Point
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