Monday, October 3, 2011

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

image

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_” )

image

- Physical Display Folder

Use Physical Display Folder to organise your stars

image

- 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.
image

- Parameterize your data source name

image

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

image

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

4 comments:

Anonymous said...

Hi John, another one that should be mentioned is setting up a separate connection pool to handle init blocks vs. one set up to query the data.

Anonymous said...

Hi John, your blog is awesome, and its really helpful for all who just started exploring OBIEE. I recently encountered with following error in OBIEE 10.1.3.4:

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 46115] No Unicode translation is available for some of the input characters for MultiByteWideChar(). (HY000)


The above error occured after selecting few columns in OBI Answers. Thanks for your help.

-Kiran

John Minkjan said...

@Kiran,

Are there specific type of columns when this is happening?

regards

John

physical therapist schools said...

I’ve learned a lot from your blog here, Keep on going, my friend, I will keep an eye on it,