Thursday, March 15, 2012

Design Tips for the BMM Layer- OBIEE

                                        Design Tips for the BMM Layer
1)  Create the business model with one-to-many logical joins between logical
dimension tables and the fact tables wherever possible. The business model
should ideally resemble a simple star schema in which each fact table is joined
directly to its dimensions.
2)Every logical fact table must join to at least one logical dimension table. Note that
when the source is a fully denormalized table or flat file, you must map its
physical fact columns to one or more logical fact tables, and its physical dimension
columns to logical dimension tables.
3) Every logical dimension table should have a dimensional hierarchy associated
with it. This rule holds true even if the hierarchy has only one level, such as a
scenario dimension {actual, forecast, plan}.
4) When creating level-based measures, make sure that all appropriate fact sources
map to the appropriate level in the hierarchy using aggregation content. You set
up aggregation content in the Levels tab of the Logical Column dialog for the
measure. Note that this is different from the Content tab of the Logical Table
Source dialog, which is used to specify the grain of the source tables to which it
 You only need to set up aggregation content in the Levels tab of the Logical
Column dialog for level-based measures. For measures that are not level based,
leave the Logical Level field blank
5) Typically, logical fact tables should not contain any keys. The only exception is
when you need to send Logical SQL queries against the Oracle BI Server from a
client that requires keys. In this case, you need to expose those keys in both the
logical fact tables, and in the Presentation layer.
6) Normally, all columns in logical fact tables are aggregated measures, except for
keys required by external clients, or dummy columns used as a divider. Other
non-aggregated columns should instead exist in a logical dimension table.
7) In some situations, you might want to have multiple logical fact tables in a single
business model. For Logical SQL queries, the multiple logical fact tables behave as
if they are one table.
Reasons to have multiple logical fact tables include:
– To assign projects. See "Setting Up Projects" for more information.
– To automatically create small subject areas in the Presentation layer. See
"Automatically Creating Subject Areas Based on Logical Stars and Snowflakes"
for more information.
– For organization and simplicity of understanding.
Unlike relational fact tables, logical fact tables can contain measures of different
grains. Because of this, grain is not a reason to split up logical fact tables.
8) You can define calculations in either of the following ways:
– Before the aggregation, in the logical table source. For example:
sum(col_A *( col_B))
– After the aggregation, in a logical column derived from two other logical
columns. For example:
sum(col A) * sum(col B)
You can also define post-aggregation calculations in Answers or in Logical SQL
9) If you plan to use Oracle Scorecard and Strategy Management, it is a best practice
to implement at least one time dimension in the Oracle BI repository you are using
for your KPIs. This action is necessary because you use KPIs in scorecards to
measure progress and performance over time. Note that an individual scorecard
automatically picks up any dimension used by KPIs in that scorecard.
10) Aggregate sources should be created as separate logical table sources. For fact
aggregates, use the Content tab of the Logical Table Source dialog to assign the
correct logical level to each dimension.
11) Each dimension level in a hierarchy must have a unique level key. Also, each
logical dimension table must have a unique primary key. Normally, this key is also
used as the level key for the lowest hierarchy level.
12) Renaming columns in the Business Model and Mapping layer automatically
creates aliases (synonyms) for Presentation layer columns that have the property
Use Logical Column Name selected.
13)prevent problems with aggregate navigation, ensure that each logical level of a
dimension hierarchy contains the correct value in the field named Number of
elements at this level. Fact sources are selected on a combination of the fields
selected as well as the levels in the dimensions to which they map. By adjusting
these values, you can alter the fact source selected by the Oracle BI Server
Modeling Outer Joins

The following guidelines provide tips on how to model outer joins:
1) Due to the nature of outer joins, queries that use them are usually slower. Because
of this, define outer joins only when necessary. Where possible, use ETL
techniques to eliminate the need for outer joins in the reporting SQL.
2) Outer joins are always defined in the Business Model and Mapping layer. Physical
layer joins do not specify inner or outer.
3) You can define outer joins by using logical table joins, or in logical table sources.
Which type of outer join you use is determined by whether the physical join maps
to a business model join, or to a logical table source join.
4) Be aware that outer joins in logical table sources are always included in a query,
even if the none of the columns in one of the mapped physical tables are used.
For example, assume that a logical table source is mapped to physical tables A and
B. When no outer joins are defined, if physical table A is not required to satisfy a
request, it is not included in the physical query. However, if the logical table
source has an outer join defined to table A, table A is still included in
5) If you must define an outer join, try to create two separate dimensions, one that
uses the outer join and one that does not. Make sure to name the dimension with
the outer join in a way that clearly identifies it, so that client users can use it as
little as possible.

Reference : oracle repository building documentation


No comments:

Post a Comment