Best Practices for Designing Business Model Mapping Layer for Oracle BI

OBIEE BMM Layer Design Principles / Best Practices

There are some design principles(geeks call them best practices) suggested by oracle in designing the OBIEE repository. we can find these in if open any standard/sample repository provided by oracle.

Below are some design principles for BMM Layer.

1. Multi User Development Environment (MUD)

Use the Multi- User Development  facility if there are multiple developers. Multiple developers to connect “online” to the same repository file and making changes is not recommended.

Multi User Development allows user to define a series of projects within the repository file ,where each project is a subset of the entire repository .If developers want to make changes , they can check out a project to a local machine make and test the changes,and then check the modifications back into the master repository file.

2.  Always run Global Consistency Check before releasing a repository.

Whenever we make changes to a repository ,always be sure to run Global consistency check. It is bad practice to release a repository that still contains consistency check errors. In some cases, consistency errors prevent Oracle BI Server from loading the repository. Use the Consistency check manager to identify and debug check messages.

3.Separate Business Model

Even if you have only a single data source or schema in the physical layer, or you have only one physical data source for the repository, it is still good practice to break out the physical objects into multiple business models in the BMM layer to represent the independent areas of functionality.

4. Logical Tables

When building logical tables, do not merge multiple dimension tables into a single logical dimension table,and do not merge multiple fact tables into a single logical fact table.

Having multiple logical fact tables also makes it easier to create well defined projects for Multi User development.It is also a good practice to prefix logical table names with either Dim-, Fact- ,or Fact Compound .

This allows you to easily see how the tables are being used. It also groups the tables in the business model, so that facts are groups with facts, dimensions with dimensions  and so on.

5. Time Dimension

There are few things to keep in mind in time dimension factor.

Always must ensure that time Dimension hierarchy is built correctly and the logical level of each time- logical table source is set correctly
If there are multiple time dimensions within the business model, for consistency, make sure that all time dimension logical table contains the same columns and general structure. This is good for reporting purpose.

6. Logical Fact & Dimension table columns

Always assign a primary key for logical dimension tables. All logical dimension columns should be renamed in a way that is meaningful to users.
Bring only required columns in to the BMM layer for reporting.
Do not assign logical primary key for logical fact tables.
Create meaningful name for measures
Set aggregation rule for every logical fact columns.
Create “dummy” measures to group facts.

7. Logical Joins

Use only logical(complex ) joins in BMM layer. And always accept default properties when creating joins.

8. Calculated Measure

When building calculated measures try to be a bit cautious.

Use logical columns for calculations that require an aggregation rule that is applied before the calculation.
Use physical columns for calculations that require an aggregation rule to be applied after the calculation.

9. Aggregates

Few important things to keep in mind about Aggregates.

Try to ensure that each aggregate table has an effective summary ration with underlying detail.
Ensure that the logical level of every aggregate logical table source is set correctly.
Always test to ensure that aggregates tables are being used as expected.
If an aggregates is not used ,try changing the number of elements on one of the related logical dimension levels.

10. Dimension Hierarchies

In Dimension Hierarchies few things  are very important to keep in mind

It is best practice to create dimensional hierarchy for every logical dimension table in BMM layer.
All Dimension must have at least two levels : the total level and detail level.
If you are creating Dimensional Hierarchy manually, be sure to check Grand total level for the Total Level.
Use Update Row Counts or Estimate Levels to set the number of elements for every level of every Dimension Hierarchy.
Think about the experience of user when enabling drill down.

11. Avoid Snowflake schema

When there is Snowflaking in physical model,We should try to avoid Snowflaking in BMM layer and build models that use only star schema .

Use WHERE clause filters to help avoid using opaque views or complex joins in the physical layer.