OBIEE11g Golden Rules: RPD-Physical Layer

First of al the original inspiration for these “Golden Rules” Series are based on the “20 GOLDEN RULES FOR REPOSITORY DESIGN” from the people at Peak Indicators. Kudos to them.

The series contains:

• RPD-Physical Layer
• RPD-Business Model Layer
• RPD-Presentation Layer
• Catalog Structure
• Report Building
• Dashboard Building

The “rules” is this article are somewhat in random order

This is always a “work in progress” and please feel free to make any suggestions!

Physical Layer

- Clear the cache check box

Using cache should be a last resource, reconsider your data model and ETL processes first!

 

 

 

- Always use “Foreign Key” joins, not “Complex Joins” on the Physical Layer

If your join looks like D_DATE = TRUNC(S_DATETIME) try add a extra column S_DATE in your DWH. Any matching processing done by the BI-server costs time and you often loose the advantage of an index in your DWH.

- Prefix your tables

When modelling a star-schema data-model, create aliases for all your physical tables (prefixed with either “Dim_”, “Fact_” or “Fact_Agg_” )

- Physical Display Folder

Use Physical Display Folder to organise your stars

- Call Interface

When possible, configure your connection pools to use a “native driver” to connect to your physical databases. For example, use OCI for connecting to an Oracle database rather than ODBC.

- Parameterize your data source name

This way you only have to change it in one place when moving from development to production.

- 3NF in DWH

Try to avoid doing 3NF to Star Schema Modelling in the Physical Layer. Flatten the table if possible during the ETL or in a view on the database.

- Connecting User

The User you use to connect to your data should by default not by the “owner” of the table, but should have only select rights trough a role.

Till Next Time