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