OBIEE11g Golden Rules: RPD-Business Model 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
• Report Building
• Dashboard Building
• Catalog Management

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!

Business Model Layer

- Prefix Logical Tables

All Logical Tables should be prefixed. There are several naming convention's in use:

• “Dim – “, “Fact – “ or “Fact Compound –“
• “D## name”, “F## name” or “FC## name”

- No “physical” column names

No “physical” column names should ever be seen on the Business Model layer. All naming conventions should be “business oriented”. For example use “$ Revenue” rather than “DOLLARS” .

- No Primary or Surrogate Physical Keys

Physical Primary Keys or Surrogate Keys should not be present on the Business Model layer (unless, for example, you have a Primary Key such as Order Id which will be displayed on reports)

- Logical Keys

Dimension Logical Tables must always have a Logical Key assigned. The Logical Key should be something “business oriented” such as “Employee Login” rather than “EMPLOYEE_PK”

- No Facts in dimensions

Dimension Logical Tables must only contain dimension attributes, they should never contain any measure columns (which have an Aggregate Rule)

- No Logical Keys on facts

Fact Logical Tables should not have a Logical Key assigned.

[UPDATE: ] As far as I know this is because the OBIEE optimizer uses the logical key to determine the "driving" table. No logical keys on fact tables should ensure the "correct" optimizer path. Please correct me if I’m misinformed.

- Aggregation Rules on Facts

Every Logical Column within a Fact Logical Table must be a measure column, and therefore have an Aggregation Rule assigned.

- Only Complex Joins

When defining Logical Joins between Logical Tables, only use “Complex Joins” (and use the default settings – you only ever specify a “Driving Table” when dealing with cross-database joins)

- No Snowflakes

The Business Model should only consist of logical star-schemas, there should not be any snow-flaking

- Hierarchies on Dimensions

Every Dimension Logical Table should have a corresponding Dimension Hierarchy (with “Total” as a Grand Total level, and “Detail” at the lowest level)

- Number of Elements

Each level of a Dimension Hierarchy should have its “Number of Elements” appropriately set (there is a utility in Tools that can do this automatically).

 

- Content Levels

Every Logical Table Source within every dimension and fact Logical Table should have its “Content Levels” appropriately set. The only time the “Content Level” is not set for a particular dimension is when there is no logical relationship existing

- Multiple facts tables

Do not merge all your measures into a single Fact Logical Table. For example, you should split “Forecast Sales” and “Actual Sales” measures into two Logical Tables e.g. “Fact – Sales” and “Fact – Forecast”

- Description fields

All available description field should have meaningful descriptions with non technical users.

Till Next Time