Thursday, October 6, 2011

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:

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”

image

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

image

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

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

image

- 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

image

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

image

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

 image

- 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

image

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

image

- Description fields

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

Till Next Time

7 comments:

David Andersen said...

Thanks for the list John. A few questions:

1. Fact Logical Tables should not have a Logical Key assigned.

Why is this?

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

Ideally. There are definitely valid exceptions to this rule, such as when you have attributes who can have multiple values of another attribute and your goal is to be able to either (A) list all the possibilities (without metrics) or (B) select a different one of the the multiple attributes values based on some criteria. To do so means all of the possibilities need to be snowflaked with a join to the primary dim.

John Minkjan said...

Hi David,

Thanks for your input:
1. As far as I know this is because the OBIEE optimizer uses the logical to determine the "driving" table. No logical keys on fact tables should ensure the "correct" optimizer path.

2.Your right, altough in 11g you could use lookup tables for this purpose.

regards

John

David Andersen said...

Thanks John. Good to know about 11g lookup tables; haven't come up to speed on it yet but this is great news because just went through a long, messy model design with many snowflakes.

Anonymous said...

Hi John - one question on the keys for a logical dimension table. We have dimension tables which are nothing more than groups of demographics, i.e. sex, age, race, etc. If I don't bring over the natural surrogate key, the only true logical key would be each of the columns on the table. Does that sound right?

In the past, I've always just brought over the surrogate key in this case, and then made sure it didn't show up in the presentation layer.

What would you do in this case?

Thanks,
Scott Powell

Anonymous said...

p.s. to add on to what I just wrote, I'd often also want to "combine" an employee detail physical dimension (employee id, name, etc.) with a associated demographcs dim (sex, race, age) into a single "Employee" dimension on the business layer. But then the only true key would be the surrogate key of the detail dim along with the surrogate key of the demographic dim. Again in the past I've brought over the surrogates and then just not displayed them.

Thx,
Scott

Anonymous said...

Sorry, last comment on this post...the point about "not merging all measures into a single logical fact table" is a bit unclear. What are the underlying guidelines this comment is trying to address? Don't combine measures that have different dimensionality? Don't combine measures that come from different physical tables? I wasn't quite able to figure out exactly what this suggestion was attempting to show.

Thanks!
Scott Powell
SPowell@columbus.rr.com

Dan O'Brien said...

I always recommend closing the loop at actually observing the Physical SQLs generated - as described:
http://www.obieeabc.com/2013/02/obiee-rpd-modelling-best-practise.html