OBIEE Presentation Layer Design Principles/Best Practices

It is a Last Chapter of OBIEE repository.

There are certain design principles or best practices oracle suggest in designing the OBIEE repository. If you open any standard/sample repository provided by oracle you will understand what exactly I am talking about

Today we will discuss the design principles for Presentation Layer.

Principle 1.Subject Areas

Do not include all presentation layer objects in a single subject area (Presentation Catalog). While it is possible but still not recommended approach.

Principle 2. Development with End Users in Mind

Always develop the presentation layer so that end users are able to understand and use it.

Principle 3. Role Based Subject Area.

It is always good idea to create presentation catalog to suit the needs of individual users or user types. For ex. create a separate presentation catalog for sales managers because they need to see only an overview of an organization.

Principle 4. Presentation Tables

Presentation Tables should consistent across presentation catalog.

• List Dimension tables first.
• Do not mix dimension and fact columns in the same table.
• Apply consistent ordering and naming conventions for tables and columns across catalogs.
• Include the words “measure” or “fact” in the names of the fact presentation tables.

Principle 5. Rule of Seven

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

Keep Presentation catalogs small and easy to understand by limiting the number of columns to seven.

Principle 6. Fact Tables

Try to limit the presentation catalogs so that there are no more than a couple of fact presentation tables in each catalog. It is important to avoid situation in which there are multiple join paths existing within one presentation catalog.

Principle 7. Implicit Fact Columns

If you have multiple fact presentation tables, it is always best practice to assign an implicit fact column. Implicit facts come into play when an Answers report contains columns from more than one logical dimension table and no explicit facts.

Principle 8. Canonical Time

Canonical time is a useful way for allowing users to report for a specific period of time across multiple star schema. If you use canonical time ,make sure that the corresponding time presentation table is given a very generic  name and that name is consistent across all the presentation catalogs.

It should be the first table in a catalog.

Principle 9. Secondary Time Dimension

This function enables users to build time reports for specific star schema.

Secondary time dimensions can be given their own presentation tables further down the list.We can place all secondary time dimension objects into a single presentation table.

Principle 10. Nesting Presentation Table

Prefix presentation table names with a hyphen to group common objects together into sub folders.

Principle 11. Presentation Object Names & Description

• Use the Alisa tab to keep track of prior names.

• Use the default option that synchronize the presentation column name with the underlying logical column name.

• Use only logical, business -oriented names ( rather than physical object names) in the presentation Layer.

Alias Tab of Presentation Layer Dialog Boxes

An Alias tab appears on the Presentation Catalog, Presentation Table, and Presentation Column dialog boxes. You can use this tab to specify or delete an alias for the Presentation layer objects.

To add an alias

1. Double-click a presentation catalog.
2. In the Presentation Layer dialog box, click the Aliases tab.
3. Click the new button, and then type the text string to use for the alias.
4. Click OK.

To delete an alias

1. Double-click a presentation catalog.
2. In the Presentation Layer dialog box, click the Aliases tab.
3. In the Aliases list, select the alias you want to delete.
4. Click the delete button, and then click OK. 

Presentation Columns

The presentation column names are, by default, identical to the logical column names in the Business Model and Mapping layer. However, you can present a different name by clearing both the Use Logical Column Name and the Display Custom Name check boxes in the Presentation Column dialog box.

To provide a convenient organization for your end users, you can drag and drop a column from a single logical table in the Business Model and Mapping layer onto multiple presentation tables. This allows you to create categories that make sense to the users. For example, you can create several presentation tables that contain different classes of measures—one containing volume measures, one containing share measures, one containing measures from a year ago, and so on.

The Presentation Column dialog box has the following tabs:

• General. Use this tab to create or edit presentation columns.
• Aliases. Use this tab to specify or delete an alias for a presentation column.

To create a presentation column

1. Right-click a presentation table in the Presentation layer, and then choose New Presentation Column.
2. In the Presentation Column dialog box, to use the name of the logical column for the presentation column, select the Use Logical Column check box.
   The name of the column and its associated path in the Business Model and Mapping layer appears in the Logical Column Name field.
3. To specify a name that is different from the Logical Column name, clear the Use Logical Column check box, and then type a name for the column.
4. To assign user or group permissions to the column, click Permissions.
5. In the Permissions dialog box, assign permissions, and then click OK.
   For more information about assigning permissions, refer to Setting Permissions for Repository Objects.
6. To select the logical column, click Browse.
7. In the Browse dialog box, select the column, and then click Select.
8. (Optional) Type a description of the presentation column.
9. To define any aliases for the logical column, click the Aliases tab.

To edit a presentation column

1. In the Business Model and Mapping layer, double-click the presentation column.
2. In the Presentation Column dialog box, click Edit.
3. In the Logical Column dialog box, make any changes or review the information, and then click OK.

To delete a presentation column

1. Right-click a presentation table in the Presentation layer, and then select Properties.
2. Click the Columns tab.
3. Select the column you want to delete.
4. Click Remove, or press the Delete key, and then click Yes.

To reorder a presentation column

1. Right-click a presentation table in the Presentation layer, and then select Properties.
2. Click the Columns tab.
3. Select the column you want to reorder.
4. Use drag-and-drop to reposition the column, or click the Up and Down buttons.
5. Click OK. 

Presentation Tables

You can use presentation tables to organize columns into categories that make sense to the user community. Presentation tables in the Presentation layer contain columns. A presentation table can contain columns from one or more logical tables. The names and object properties of the presentation tables are independent of the logical table properties.

The Presentation Tables dialog box has three tabs: General, Columns, and Aliases. The functionality provided in each tab is described in the following list:

Tab	Comment
General	Use this tab to create or edit a presentation table.
Columns	Use this tab to reorder or sort the Presentation layer columns in the Administration Tool workspace, and to delete columns. You can also use this tab to access the Presentation Column dialog box, where you can create and edit columns.
Aliases	Use this tab to specify or delete an alias for a presentation table.

To create a presentation table

1. Right-click a catalog folder in the Presentation layer, and then select New Presentation Table from the shortcut menu.
   The Presentation Table dialog box appears.
2. In the General tab, specify a name for the table.
3. Click the Permissions button to open the Permissions dialog box, where you can assign user or group permissions to the table.
   For more information about assigning permissions to a presentation table, refer to Setting Permissions for Repository Objects.
4. (Optional) Type a description of the table.
   NOTE:  To give the appearance of nested folders in Answers, prefix the name of the presentation folder to be nested with a hyphen and a space and place it after the folder in which it nests (- <folder name>). For example, to nest the Sales Facts folder in the Facts folder, place the Sales Facts folder directly after Facts in the metadata and change its name to - Sales Facts. When Answers displays the folder name in the left pane, it omits the hyphen and space from the folder name. To nest a second folder, for example Marketing Facts, in the Facts folder, change its name to - Marketing Facts and place it directly after Sales Facts. The standard preconfigured repositories provide additional examples for you to review.

To delete a presentation table

1. In the Presentation layer, right-click a catalog and select Properties.
2. In the Presentation Catalog dialog box, click the Presentation Tables tab.
3. In the Presentation Tables tab, select a table and click Remove.
   A confirmation message appears.
4. Click Yes to remove the table, or No to leave the table in the catalog.
5. Click OK.

To reorder a table or sort all tables in a presentation catalog

1. In the Presentation layer, right-click a catalog and select Properties.
2. In the Presentation Catalog dialog box, click the Presentation Tables tab.
3. To move a table, perform the following steps:
   1. In the Presentation Tables tab, in the Name list, select the table you want to reorder.
   2. Use drag-and-drop to reposition the table, or click the Up and Down buttons.
4. To sort all tables in alphanumeric order, click the Name column heading.
   This toggles the sort between ascending and descending alphanumeric order.

Presentation Catalogs

In the Presentation layer, presentation catalogs (subject areas) allow you to show different views of a business model to different sets of users. Presentation catalogs have to be populated with contents from a single business model. They cannot span business models.

When creating a presentation catalog, selecting the option Export logical keys causes any columns in the Presentation layer that are key columns in the Business Model and Mapping layer to be listed as key columns to any ODBC client. This is the default selection. In most situations, this option should be selected. Many client tools differentiate between key and nonkey columns, and the option Export logical keys provides client tools access to the key column metadata. Any join conditions the client tool adds to the query, however, are ignored; the Oracle BI Server uses the joins defined in the repository.

If you set an implicit fact column this column will be added to a query when it contains columns from two or more dimension tables and no measures. The column is not visible in the results. It is used to specify a default join path between dimension tables when there are several possible alternatives.

The Presentation Catalog dialog box has three tabs: General, Presentation Tables, and Aliases. The functionality provided in each tab is described in the following list:

Tab	Comment
General	Use this tab to create or edit a presentation catalog.
Presentation Table	Use this tab to reorder or sort the Presentation layer tables in the Administration Tool workspace, and to delete tables. You can also use this tab to access the Presentation Table dialog box, where you can create and edit tables.
Aliases	Use this tab to specify or delete an alias for a catalog folder.

To create a presentation catalog

1. In the Presentation layer, right-click and select New Presentation Catalog.
2. In the Presentation Catalog dialog box, in the General tab, type a name for the presentation catalog and click Permissions.
3. In the Permissions dialog box, assign user or group permissions to the catalog folder, and then click OK.
   For more information about assigning permissions to a presentation catalog, refer to Setting Permissions for Repository Objects.
4. In the Presentation Catalog dialog box, from the Business Model drop-down list, select a business model.
   After you add columns to the presentation catalog, the drop-down list becomes inactive because you can add columns from only one business model in each presentation catalog.
5. To expose the logical keys to other applications, select the option Export logical keys.
   NOTE:  If you are using a tool that issues parameterized SQL queries, such as Microsoft Access, do not select the Export logical keys option. Not exporting logical keys stops the tool from issuing parameterized queries.
6. (Optional) Type a description of the catalog folder.
   This description will appear in a mouse-over ToolTip for the presentation column in Oracle Business Intelligence Answers.
   CAUTION:  When you move columns into presentation catalog folders, be sure columns with the same name or an alias of the same name do not already exist in the catalog.
7. Set an Implicit Fact Column.
8. Click OK. 

Presentation Layer Objects

The Presentation layer adds a level of abstraction over the Business Model and Mapping layer. It is the view of the data seen by client tools and applications.

The Presentation layer provides a means to further simplify or customize the Business Model and Mapping layer for end users. For example, you can hide key columns or present the schema as a single table. Simplifying the view of the data for users makes it easier to craft queries based on users' business needs.

The section provides instructions for using the Administration Tool's Presentation layer dialog boxes to create and edit repository objects.

This section includes the following topics:

• Working with Presentation Catalogs
• Working with Presentation Tables
• Working with Presentation Columns
• Using the Alias Tab of Presentation Layer Dialog Boxes

Presentation Layer

The Presentation layer provides a way to present customized views of a business model to users. Presentation Catalogs in the Presentation layer (called Subject Area in Oracle Answers) are seen as business models by Oracle BI Presentation Services users. They appear as catalogs to client tools that use the Oracle BI Server as an ODBC data source. The following topics describe the Process of creating the Presentation layer.

NOTE:  In offline editing, remember to save your repository from time to time. You can save a repository in offline mode even though the business models may be inconsistent.

Copy Business Models to Publish to Users

There are several ways to create a Presentation Catalog in the Presentation layer. The recommended method is to drag and drop a business model from the Business Model and Mapping layer to the Presentation layer, and then modify the Presentation layer based on what you want users to see. You can move columns between presentation tables, remove columns that do not need to be seen by the users, or even present all of the data in a single presentation table. You can create presentation tables to organize and categorize measures in a way that makes sense to your users.

Remove Any Unneeded or Unwanted Columns

One important reason to use a custom Presentation layer is to make the schema as easy to use and understand as possible. Therefore, users should not be able to view columns that have no meaning to them. The following columns are examples of columns that you might want to remove from the Presentation layer:

• Key columns that have no business meaning.
• Columns that users do not need to view (for example, codes, when text descriptions exist).
• Columns that users are not authorized to see.

NOTE:  You can also restrict access to tables or columns in the security layer. For more information, refer to Security in Oracle BI.

Rename Presentation Columns to User-Friendly Names

By default, presentation columns have the same name as the corresponding logical column in the Business Model and Mapping layer. However, you can specify a different name to be shown to users by changing the name in the Presentation Column dialog box. Whenever you change the name of a presentation column, an alias is automatically created for the old name, so compatibility to the old name remains.

Export Logical Keys in the Presentation Catalog

For each presentation catalog in the Presentation layer, decide whether to export any logical keys as key columns to tools that access it in the Presentation Catalog dialog box. Exporting logical keys is irrelevant to users of Oracle BI Presentation Services, but it may be advantageous for some query and reporting tools. If you decide to export logical keys, be sure the logical key columns exist in the table folders. In this situation, your business model should use logical key/foreign key joins.

When you select the option Export logical keys in the Presentation Catalog dialog box, any columns in the Presentation layer that are key columns in the Business Model and Mapping layer are listed as key columns to any ODBC client. This is the default selection. In most situations, this option should be selected.

NOTE:  If you are using a tool that issues parameterized SQL queries, such as Microsoft Access, do not select the option Export logical keys. This will stop the tool from issuing parameterized queries.