OBIEE Best Practices & Design Principles

OBIEE Design Principles

The following are some of the recommended best practices and design principles to be followed while building an OBIEE Repository.

I)                  Physical Layer

1.     Summary of Design Principles

·         Use the File > Import Option.

·         Create aliases for physical tables.

·         Create aliases to avoid circular joins.

·         Create canonical time.

·         Set the physical table caching property.

·         Set connection pool properties.

·         Create additional connection pools.

2.     Description

·         Use the File > Import Option

1.     This is the recommend method to create table objects in the Physical layer (as opposed to manual creation).

2.     Use the appropriate connection type (ODBC, OCI, and so on).

3.     Import only the tables needed to support the business model.

Note: One can always add more tables later as needed to expand the business model and implement additional requests or dashboards.

·         Create aliases for physical tables

1.     Create aliases for physical tables to leverage the following advantages:

·         Allows one to import key relationships in the physical tables & keep them in Physical layer for reference, while changing the join relationships in the aliases to meet the needs of the business model.

·         Allows for proper naming convention to be constructed for all the physical tables (aliases) used as sources in repository.

·         Enables easy understanding of SQL since they show up in SQL

·         Useful in cases where one table can act as both a fact and a dimension.

·         Create aliases to avoid circular joins

1.     Create additional aliases to avoid circular joins for dimension tables that are joined to more than one table.

·         Create canonical time

1.     Create a common time dimension alias table that joins to multiple fact tables. This enables reporting across multiple facts.

2.     Create secondary time dimension alias tables that join to one fact table on other time dimension columns.

·         Set the physical table caching property

1.     When a user runs a report, the results are cached by Oracle BI Server, so that subsequent requests use the cache results rather than requiring a return trip to the physical data source. It is, therefore, advisable to go to every physical table and ensure that the caching property is set appropriately.

2.     Do not set caching to “cache never expires,” since it implies relying on a third party to purge the cache, even if it is done through an extraction.

·         Set connection pool properties

1.     When possible, set the call interface to use native drivers rather that ODBC because native drivers are more efficient.

2.     Set the maximum connections to a reasonable level. 

Note: The recommendation is to set maximum connections based on the following formula:

0.1    * *

For example: You have 100 users. If only 10% are going to be logged on at any one time and running reports, and the maximum number of reports on any given dashboard is four, then the maximum connections should be set to 40.

3.     Enable connection pooling to avoid the overhead of having to reconnect to the database every time you run a query. If you use connection pooling and run a database query, that database connection essentially remains open for other sessions to use.

·         Create additional connection pools

1.     Create additional physical models and connection pools to control security and restrict access to write back and usage tracking.

II)               BMM Layer

a.      Summary of Design Principles

·         Use Multi-User Development Environment (MUDE).

·         Perform consistency check.

·         Separate business models.

·         Design logical tables.

·         Set time dimension logical levels.

·         Set time dimension logical structure.

·         Set logical dimension table columns.

·         Set logical fact table columns.

·         Define logical joins.

·         Define logical levels.

·         Use WHERE clause filters.

·         Avoid Snowflaking.

·         Dimension Hierarchies: General.

·         Dimension Hierarchies: Levels.

·         Dimension Hierarchies: Number of Elements.

·         Dimension Hierarchies: Drilldown.

·         About using Aggregates.

·         Rename logical columns.

b.     Description

·         Use Multi-User Development Environment (MUDE)

1.     Setup the multi-user development environment (MUDE) if there are multiple developers working on OBIEE Repository.

Note: This allows one to define a series of projects within the repository file (for example: Sales, Service, and so on), 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 modifications back into the master repository file.

2.     Do not allow multiple developers to connect “online” to the same repository.

·         Perform consistency check

1.     Run a global consistency check before releasing a repository. In some cases, consistency errors prevent Oracle BI Server from loading the repository.

2.     Use the Consistency Check Manager to identify and debug consistency check messages.

Note: It is bad practice to release a repository that still contains consistency check errors.

·         Separate business models

1.     Even if the data resides in one single physical source or schema, yet it is recommended to create separate business models if independent areas of functionality need to be represented.

·         Design Logical Tables

1.     Group facts and dimensions into logical tables according to functional areas, or according to how users build their queries.

Note: Having multiple logical fact tables also makes it easier to create well-defined projects for multi-user development

2.     Prefix logical table names with either Dim -, Fact -, or Fact Compound -. It also groups the tables in the business model, so that facts are groups with facts, dimensions with dimensions, and so on.

·         Set time dimension logical levels

1.     Ensure that the logical level of each time-logical table source is set correctly.

·         Set time dimension logical structure

1.     Ensure that all time-logical dimension tables contain the same columns and general structure for consistency.

Note: If the same columns are available in all time dimensions, users obtain more consistent query results.

·         Set logical dimension table columns

1.     Do not bring physical primary key columns into the BMM layer unless absolutely necessary.

2.     Assign a logical primary key that makes sense from a reporting perspective.

3.     Create meaningful logical column names.

4.     Bring in only the columns you need from the Physical layer.

·         Set logical fact table columns

1.     Do not assign logical primary keys for logical fact tables.

2.     Create meaningful names for measures.

3.     Set aggregation rule for every logical fact column.

4.     Create “dummy” measures to group facts.

·         Define logical Joins

1.     Use only logical joins in the BMM layer.

2.     Accept defaults when creating joins.

·         Define logical levels

1.     Explicitly define logical levels for every fact and dimension logical table source.

Note: This is true even if the logical table source is at the base or detail level. The only time you should leave the logical level blank is if no logical relationship exists in the business model.

2.     Always assign aggregation content at the logical level, not the column level.

·         Use WHERE clause filters

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

Note: Doing this avoids the use of complex joins and opaque views in the physical layer.

·         Avoid Snowflaking

1.     Add tables to the logical table source to avoid snowflaking.

Note: Even when there is snowflaking in the physical model, one should try to avoid snow flaking in the BMM layer and build models that use only star schemas.

·         Dimension Hierarchies: General

1.     Create a dimension hierarchy for every logical dimension table in the business model.  This enables one to accurately specify the aggregation content of sources using dimension levels.

·         Dimension Hierarchies: Levels

1.     Check Grand total level for the Total level

2.     Create key columns for each level except the Total level.

3.     Drag in columns that are applicable to a particular level.

4.     Create identical detail levels if there are multiple drill paths.

5.     Set preferred drill path if more than one drill path exists.

·         Dimension Hierarchies: Number of Elements

1.     Use Update Row Counts or Estimate Levels to set the number of elements for every level of every dimension hierarchy.

Note: Numbers do not have to be exact as long as ratios between levels are accurate.

·         Dimension Hierarchies: Drilldown

1.     Think about the experience of the user when enabling drilldown.

2.     Do not enable automatically.

·         About using Aggregates

1.     Ensure that each aggregate table has an effective summary ratio with the underlying detail table.

2.     Ensure that the logical level of every aggregate logical table source is set correctly.

3.     Always test to ensure that aggregate tables are being used as expected. (Check the log to see the Physical SQL produced.)

·         Rename logical columns

1.     Use names acceptable to the organization and understood by users.

2.     Use short names to minimize space on reports.

3.     Create unique names for all columns.

4.     Avoid using the same name as a logical table or business model.

Note: Do not delete logical columns that define the contents of logical table sources.

III)            Presentation layer

a.      Summary of Design Principles

·         Organize subject area contents.

·         Develop with end users in mind.

·         Configure role based subject areas.

·         About creating presentation tables.

·         Follow the rule of seven.

·         Limit fact tables in presentation catalog.

·         Set Implicit fact columns.

·         About using secondary time dimensions.

·         About using nested presentation tables.

·         About using presentation object names.

·         About using presentation object descriptions.

b.     Description

·         Organize subject area contents

1.     Do not include all Presentation layer objects in a single presentation catalog (subject area).

·         Develop with end users in mind

1.     Always develop the Presentation layer so that end users are able to understand and use it. Even if there is no current requirement for users to run their own reports, users may be asked to do so in the future.

·         Configure role based subject areas

1.     Configure multiple presentation catalogs that are specific to each type of user.

·         About creating presentation tables

1.     List dimension tables first.

2.     Do not mix dimension and fact columns in the same table.

3.     Apply consistent ordering and naming conventions for tables and columns across catalogs.

4.     Include the words “measures” or “facts” in the names of the fact presentation tables.

·         Follow the rule of seven

1.     Keep presentation catalogs small and easy to understand by limiting the number of tables to seven.

2.     Keep presentation tables small and easy to understand by limiting the number of columns to seven.

·         Limit fact tables in Presentation Catalog

1.     Limit the number of fact tables in each presentation catalog wherever possible. This helps to eliminate multiple join paths in the catalog.

·         Set Implicit fact columns

1.     Set an implicit fact column if there are multiple fact tables in a presentation catalog. This helps Oracle BI Server identify the join path that should be used in dimension-only requests.

·         About using secondary time dimensions

1.     Use secondary time dimensions to build time reports for specific star schemas.

2.     This should be listed below the canonical time dimension.

Note: The secondary time dimensions can be given their own presentation tables further down the list. Alternatively, one can place all secondary time dimension objects into a single presentation

·         About using nested presentation tables

1.     Prefix presentation table names with a hyphen to group common objects together into subfolders. Nesting currently goes to only one level in Oracle BI.

·         About using presentation object names

1.     Use the Aliases tab to keep track of prior names. Aliases are used to maintain compatibility with previously written queries after an object has been modified.

2.     Use the default option that synchronizes the presentation column name with the underlying logical column name.

3.     Use only logical, business-oriented names (rather than physical object names) in the Presentation layer.

4.     Avoid use of double quotation marks (“) in names. The names cannot contain single quotation marks (‘); the Administration Tool prevents it.

5.     Keep presentation object names unique - Naming presentation columns the same as presentation tables can lead to inaccurate results.

6.     Keep names short to save space.

·         About using presentation object descriptions

1.     Add descriptions to presentation catalogs to explain the purpose of each subject area in Oracle BI Answers.

2.     Add descriptions to presentation tables to appear with mouse roll-over in Answers.

3.     Add descriptions with examples to presentation columns to explain the data content with mouse roll-over in Answers.

     

OBIEE Performance Tuning Techniques

The following are some of the recommended techniques and practices that could be used for OBIEE Performance Tuning.

a.      Summary of techniques

·         Use aggregates.

·         Use aggregate navigation.

·         Constraint results using a WHERE clause.

·         Perform caching.

·         Limit the number of initialization blocks.

·         Limit the select table types.

·         Model dimension hierarchies correctly.

·         Turn off logging.

·         Set query limits.

·         Push calculations to the database.

·         Use database hints.

·         Set the NQSConfig.ini parameters correctly.

b.     Description

·         Use aggregates

1.     This improves performance by enabling Oracle BI Server to generate queries against smaller, summarized tables.

2.     They are summaries of the detailed fact tables at higher levels along the dimension hierarchies, aggregate tables have fewer rows than the base tables, resulting in improved query performance when Oracle BI Server uses the aggregate (smaller) table to satisfy the query.

·         Use aggregate navigation

1.     Improves performance by allowing Oracle BI Server to transparently intercept queries and rewrite them to optimized data sources.

2.     Every fact logical table source needs to be defined with aggregation content to ensure that Oracle BI Server chooses the most economical source when there are several sources to choose from.

3.     If a logical table source does not contain aggregation content, Oracle BI Server assumes that logical table source is at the lowest (detailed) level.

·         Constraint results using a WHERE clause

1.     This improves performance by limiting the rows returned from a data source.

2.     Since the constraints in WHERE clause are made on the physical tables, it is helpful if indexes are defined on the columns that are used as constraints in the WHERE clause.

·         Perform caching

1.     This improves performance by fulfilling a query from a local cache as opposed to processing the query through a data source.

2.     For best performance, the cache storage directories should reside on the local disk or dedicated drives.

3.     Set the maximum number of rows for any cache entry (MAX_ROWS_PER_CACHE_ENTRY) and the maximum number of cache entries (MAX_CACHE_ENTRIES) parameters to avoid using up the cache space with runaway queries that return large number of rows.

4.     The cache management strategies should be in place, including prepopulating the cache using cache seeding techniques such as iBots provided by the Oracle BI Server and keeping the cache up-to-date using cache purging techniques.

·         Limit the number of initialization blocks

1.     This improves performance because initialization block queries are executed when Oracle BI Server is started and when users log in to the server.

Note: Initialization blocks are the only means to initialize dynamic repository, system session, and non-system session variables, but care should be taken not to create too many of them.

·         Limit the select table types

1.     This reduces the number of SELECT statements executed by Oracle BI Server.

2.     May avoid lengthy SQL queries.

·         Model dimension hierarchies correctly

1.     This Improves performance by ensuring that Oracle BI Server chooses the most economical source.

2.     The number of elements for each level must be specified. The number does not have to be exact, but ratios of number from one parent to child logical level should be accurate.

3.     All levels except the Grand Total level need to be defined with at least one column from the dimension table.

4.     Columns that are not associated with a logical level are automatically associated with the lowest level in the dimension that corresponds to that dimension table.

·         Turn off logging

1.     This improves performance in a production environment because Oracle BI Server does not generate log files.

2.     If you decide to enable logging, create a separate Administrator group user with a logging level of 2, which allows the user to debug using the query log and should provide enough logging information in most cases.

·         Set query limits

1.     This improves performance by enabling Oracle BI Server to track and cancel runaway queries.

2.     For each user or group, it is possible to limit queries by varying conditions:

a.     maximum number of rows

b.     maximum time a query can run on a database,

c.     restricting access to a database

3.     Push calculations to the database.

4.     Use database hints.

5.     Set the NQSConfig.ini parameters correctly.

·         Push calculations to the database

1.     This improves performance by automatically pushing certain operations to the database based on database feature entries.

2.     By modeling the repository accurately and adjusting the default database feature table entries for a database, you should be able to achieve a combined optimal performance of both Oracle BI Server and the database.

·         Use database hints

1.     Improves performance because hints force the database query optimizer to execute the statement in a more efficient way

·         Set the NQSConfig.ini parameters correctly

1.     The NQSConfig.ini config file includes parameters that can be set to better Oracle BI performance:

§  SORT_MEMORY_SIZE - Specifies the maximum amount of memory to be used for each sort operation.

§  SORT_BUFFER_INCREMENT_SIZE - Specifies the increment by which the sort memory size is increased as more memory is needed.

§  VIRTUAL_TABLE_PAGE_SIZE - Specifies the size of a memory page for Oracle BI Server internal processing.