Introduction

This document is for Oracle Business Intelligence Enterprise Edition (OBIEE) Developers and Designers. These standard guidelines may vary from project to project. Below mentioned OBIEE Standards are more in generic form & developers can follow these guidelines to standardize their code & minimize post development defects.

Repository – Physical Layer best practices

• Database specific best practices

1. Proper naming convention of Database objects and Connection Pools.
2. Optimum number of connection pools should be used.
3. Enable connection pooling option with proper timeout details.

• Separate Connection Pools should be used for initializations blocks for better performance.
• Proper keys & Joining conditions should be defined in the Physical Layer.
• Foreign keys should NOT be imported from database.
• Number to Number join conditions works faster than Varchar to Varchar joins.
• For any table in the physical layer of OBIEE RPD, set the cache persistence time properly for better performance. Screen shot given below. This will depend on the actual table refresh time in the database.

• For self join, create Alias tables in the Physical Layer and rename the table name properly. Like W_DAY_D becomes W_DAY_D_transaction_date and W_DAY_D_order_date.
• Minimize the use of Opaque Views in the physical layer. Tables or Materialized views in Database will improve the performance.
• Same tables should only be imported once into the physical layer. Create multiple aliases for using the same table for different purposes.
• Fact to Fact join is not advised
• Circular joins should not be used by the use of alias tables.
• After creating the physical model, database connectivity should be checked & confirmed with Update All Rows Count option.

Repository- BMM Layer best practices

• Rename the columns in the BMM layer with removal of _ (under scores), also use Init-Caps naming convention for the columns and table names. So column CUSTOMER_ID becomes Customer ID.
• Avoid dimensional columns in Logical Fact tables & Measure column in Dimension tables. So split a table into a logical dimension and a logical fact if required.
• Related all attributes that describe a single entity should be combined into a single Logical Table with multiple logical table sources.
• Specific short forms and symbols used should be in CAPS in both column and Logical table name (Like FTE or RAM or ORG).
• Complex joins should be used in BMM Layer so that BI server can make the best decision about the physical SQL to be generated.
• The Content tab of each of the LTSs in Fact should be set to the related Dimension’s Logical Level.

• We can use the where clause LTS Content tab to limit the number of records returned.

Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud

Explore key considerations, integrating the cloud with legacy applications and challenges of current cloud implementations.

Get the Guide

• Proper Column Mappings of the Logical columns to the Physical Table should be done in LTS Column Mapping tab. One logical column MUST be mapped to at least one physical column or some formula expression.

• While creating a Logical Column in the BMM Layer expression builder, one needs to make sure that all the columns should be from same logical table or the logical tables MUST be directly related to each other.
• It’s a good practice to create Dimension Hierarchies for every dimension in the Business Model even if it is not used anywhere. Keys should be defined at each level of the hierarchy.
• “Number of elements at this level” Criteria should be defined properly at the each level of the Dimensional Hierarchy. This value should increase from 1 at Grand total to the subsequent levels.

Repository- Presentation Layer best practices

• Using of Parent & Sub-folders is recommended to group Fact & Dimensions.
• Presentation Columns should not have the same name as presentation Tables.
• To convey information about the objects in the Presentation Layer, fill the Object Description shown below so that whenever user hover the mouse in a Answers they will get information about the object.

• Whenever we are importing new table don’t bring columns in the Presentation layer which are not required. Keys, ETL Load Date, Update Date, INSERT Date etc.
• Logical grouping of the columns in the Presentation Layer table is important like we should always place the measure columns in the Fact table of the Presentation catalog folder.
• Alphabetical re-ordering should be done on the presentation layer columns present under one table. This can be done by click on the Name sections under Columns section of the presentation table.

OBIEE Report Design

• Prompt section should be placed in the top of the page grouped by function or by dimension.
• Consistent Color and Style coding should be maintained throughout the report.
• In the OBIEE Answers while creating report, create and set the No Results View. As an example, below mentioned definition can be used:

<b><font color=’Black’><h4>There is no data for the selected criteria. Please change your parameters and try again.</h4></font></b>

• Report Prompt data clear button should be present in the report.
• It’s a good practice that Report should contain the Title as Report Name and also Report Run Time and date, set the Started Time in the title section as Display date and time.
• Newly Created report present under the dashboard should have the Report Links as Refresh, Download and Printer Friendly (PDF).

• While placing a link in the existing dashboard, it’s a good practice to make the target set as New Window as report can open in new window after clicking the link.

• In report use of Filters with some default values will avoid high response while running the report from Dashboard for the first time.