[OBIEE 10g & 11g] OBIEE Naming Conventions, Standards, And Tips

OBIEE Naming Conventions, Standards, and Tips

Before you start, make sure you are familiar with OBIEE modelling techniques. A good place to start is the Introduction chapter in the Admin Tool help. Modelling is done with Admin Tool, and the file modelled is name the Repository or RPD (its extension). The RPD can be opened in online mode when there few developers. Backup the RPD regularly. 

Best Practise

Oracle's BI Applications RPD file is available as a best practise reference. The conventions in this document follow many of the BI Applications modelling standards. 

BI Applications RPD 

General Conventions and Tips

• Do not tie any names to source system implementation
• The goal of the logical layer is to simplify a complex physical implementation: thus re-use existing logical tables, do not recreate a logical table for the same business concept
• In online mode, save backups of the online repository before and after every completed unit of work. If needed, use Copy As on the File menu to make an offline copy containing the changes.
• Use the Physical Diagrams in the Administration Tool to verify sources and joins.
• Use business readable names and descriptions in most places (apart from Physical Layer and Variables)
• Enable query logging to test the physical SQL generation

Physical Layer Tips

This is where access to actual resources are defined, be they databases, files, cubes or other. The main task here is to define databases, connections, physical tables, aliases, keys, and joins of an actual implementation.

• Use the Import Metadata feature to import tables to the physical layer
• Always work with aliases not physical objects. This allows for object reuse for certain dimensions (ie. the physical table DAY_D could be reused for multiple date dimensions). This allows you to:
• Eliminate all physical joins that cross dimensions (inter-dimensional circular joins) by using aliases
• Eliminate all circular joins (intra-dimensional circular joins) in a logical table source in the Physical Model by creating physical table aliases. For example, say you have a Customer table that can be used to look up ship-to addresses, and using a different join, to look up bill-to addresses. Avoid the circular joins by aliasing the table in the Physical layer so that there is one instance for each purpose, with separate joins.
•  Opaque views (a Physical layer table that consists of a SELECT statement) should be used only if there is no other solution to your modeling problem

• Name aliases Dim_ or Fact_ for example the alias Dim_TENANT_D would point to the physical table TENANT_D
• Create joins on the aliases - not the underlying

• Keys are created on dimensional aliases (these are done automatically when you create joins)

• Foreign keys are created on fact aliases (these are done automatically when you create joins)

 

Business Model and Mapping Layer Conventions and Tips

This layer is often termed the logical layer and is where the physical layer is simplified into a single business representation.

• The business model should ideally resemble a simple star schema in which each fact table is joined directly to its dimensions.

• Never use technical terms on this layer, all terms should be business orientated.

Work under single business model, eg. HNZC 

• Name logical tables Dim - [business name] or Fact - [business name]. For example Dim - Tenant, or Fact - Debt.

• Name logical dimensions the same as the logical table names.

• Use the Admin Tool->Tools-Utilities-Rename Wizard to rename en masse logical columns with the following rules:
• Change each occurence of '_' into a space
• All text lowercase
• First letter of each word capital

• Remove columns that are not needed
• Use the description field for logical tables columns, as this is made available to users through tooltips in the Analysis Editor

Tooltip

• 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.
• Every logical dimension table should have a dimensional hierarchy associated with it. This rule holds true even if the hierarchy has only one level

Set the Content tab for each logical table source to reflect the grain of each fact and dimension 

• 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.
• 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.
• 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
• 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
• 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
• To 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.
• Model outer joins only when necessary. Outer joins are always defined in the Business Model and Mapping layer. Physical layer joins do not specify inner or outer.

Presentation Layer Convention and Tips

The presentation layer is the exposed layer to ODBC query clients including the OBIEE presentation services component. This is the layer that users will see in the Analysis Editor when they build queries.

• Group subject areas around business relevant functions, such as Debt Management. These relate to logical stars, for analysis.

• Describe each subject area

• Name presentation folders the same as logical tables but remove the Dim - or Fact - prefix.
• In a subject area, sort presentation folders first by dimension then fact, then alphabetically

• So that it is displayed in the Select Subject Area drop down.

• Because there is no automatic way to synchronize all changes between the Business Model and Mapping layer and the Presentation layer, it is best to wait until the Business Model and Mapping layer is relatively stable before adding customizations in the Presentation layer.
• There are many ways to create subject areas, such as dragging and dropping the entire business model, dragging and dropping incremental pieces of the model, or automatically creating subject areas based on logical stars or snowflakes. 
• It is a best practice to rename objects in the Business Model and Mapping layer rather than the Presentation layer, for better maintainability. Giving user-friendly names to logical objects rather than presentation objects ensures that the names can be reused in multiple subject areas. Also, it ensures that the names persist even when you need to delete and re-create subject areas to incorporate changes to your business model.
• Be aware that members in a presentation hierarchy are not visible in the Presentation layer. Instead, you can see hierarchy members in Answers.
• You can use the Administration Tool to update Presentation layer metadata to give the appearance of nested folders in Answers.
• When setting up data access security for a large number of objects, consider setting object permissions by role rather than setting permissions for individual columns
• You can show fewer columns than exist in the Business Model and Mapping layer. For example, you can exclude the key columns because they have no business meaning. 
• You can set permissions to grant or deny users access to individual subject areas, tables, and columns.
• You can export logical keys to ODBC-based query and reporting tools.
• You can create multiple subject areas for a single business model.
• You can create a list of aliases (synonyms) for presentation objects that can be used in Logical SQL queries. This feature lets you change presentation column names without breaking existing reports.

Initialization Blocks and Variables

• Name all repository variables (static, dynamic, session) in upper case with _ instead of spaces. Ie. for example CURRENT_DATE.

• Name initialize blocks in plain text. Example: Authorization.