11 Creating and Maintaining the Presentation Layer

The Presentation layer provides a way to present customized, secure, role-based views of a business model to users. Role-based views provide object security and also provide a way to hide some of the complexity of the business model. The Presentation layer also provides some of the functionality of the metadata model, such as the ability to set an implicit fact column.

Presentation layer views are called subject areas (formerly called presentation catalogs). You can have a subject area that is identical to your business model, or you can provide smaller, role-based subject areas that show a single subject, or that support a particular business role. Create subject areas that help you organize your content in a way that makes sense for your users.

After you have created the Business Model and Mapping layer, you can drag and drop entire business models to the Presentation layer in the Administration Tool. Alternatively, you can create subject areas and other Presentation layer objects manually.

This chapter explains how to use the Administration Tool to create and edit objects in the Presentation layer of a repository.

This chapter contains the following topics:

Creating and Customizing the Presentation Layer

The Presentation layer provides a way to present customized views of a business model to users. Subject areas in the Presentation layer appear as catalogs to client tools that use the Oracle BI Server as an ODBC data source. Subject areas contain presentation tables, columns, hierarchies, and levels.

Even though the Logical SQL requests from Answers and other clients query the presentation tables and columns, the real logic for entities, relationships, joins, and so on is in the Business Model and Mapping layer. The primary function of the Presentation layer is to provide custom names, dictionary entries, organization, and security for different groups of users.

This section contains the following topics:

Creating Subject Areas

There are several ways to create subject areas 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.

You can also duplicate an existing subject area and its corresponding business model. See "Duplicating a Business Model and Subject Area" for more information.

Although each subject area must be populated with contents from a single business model, you can create multiple subject areas for one business model. For very large business models, you may want to do this to help users work with the content. Users in Oracle BI Answers can create queries that span multiple subject areas, as long as the subject areas correspond to the same business model.

There are many ways to create multiple subject areas from a single business model. One method is to drag a particular business model to the Presentation layer multiple times, then edit the properties or objects of the resulting subject areas as needed.

For example, if you have a business model called ABC that contains the Geography and Products dimensions, you can drag it to the Presentation layer twice. Two subject areas are created, with the default names ABC and ABC#1. You can then edit the subject areas as follows:

  • Rename the ABC subject area to DEF, then delete the Geography presentation hierarchy

  • Rename the ABC#1 subject area to XYZ, then delete the Products presentation hierarchy

Users in Oracle BI Answers can then run queries that span both the DEF subject area (containing the Products hierarchy), and the XYZ subject area (containing the Geography hierarchy).

Automatically Creating Subject Areas Based on Logical Stars and Snowflakes

You can automatically create one subject area for each logical star or logical snowflake in your business model. Logical stars and logical snowflakes are both composed of a centralized fact table connected to multiple dimension tables. This feature provides another way to create multiple subject areas from a single business model.

To create a subject area for each fact table that is part of a logical star or snowflake, right-click the business model and select Create Subject Areas for Logical Stars and Snowflakes. The new subject areas are automatically created, each containing a fact table and only the dimension tables with which it is associated. This option is available for any business model that contains logical stars or logical snowflakes.

For example, if you choose this option for the Sample App business model with nine fact tables, nine corresponding subject areas are created, each with one fact table and its associated dimension tables. Subject areas are also created for lookup tables. Figure 11-1 shows exactly how the logical fact tables and dimension tables are modeled in the Presentation layer.

Figure 11-1 Creating Subject Areas for Logical Stars and Snowflakes in Sample App

Description of Figure 11-1 follows
Description of "Figure 11-1 Creating Subject Areas for Logical Stars and Snowflakes in Sample App"

Removing 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

Renaming 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. It is recommended to keep presentation column names and their source logical column names synchronized to reduce maintenance. To do this, ensure that Use Logical Column Name is selected in the Presentation Column dialog.

In some cases, however, you may want a different presentation column name to be shown to users. To do this, change the name of the presentation column in the Presentation Column dialog.

When you change the name of a presentation column, an alias is automatically created for the old name, so compatibility to the old name remains. See "Creating Aliases (Synonyms) for Presentation Layer Objects" for more information about aliases.

Note that you cannot rename a Presentation layer object to a name that is already in use as an alias for an object of the same type.

Exporting Logical Keys in the Subject Area

For each subject area in the Presentation layer, you can decide whether to export any logical keys as key columns to tools that access it. 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, make sure that 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 Subject Area dialog, 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 stops the tool from issuing parameterized queries.

Setting an Implicit Fact Column in the Subject Area

For each subject area in the Presentation layer, you can set an implicit fact column. The implicit fact column is 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 or contexts.

Maintaining the Presentation Layer

There is no automatic way to synchronize all changes between the Business Model and Mapping layer and the Presentation layer. For example, if you add logical columns to an existing logical table, or edit existing columns, you must manually update the corresponding Presentation layer objects.

However, the Administration Tool can automatically synchronize the name of presentation columns with their corresponding logical column names. To take advantage of this feature, ensure that Use Logical Column Name is selected in the Presentation Column dialog.

In some cases, if there are many changes to a logical table or even to an entire business model, it is easiest to delete the corresponding presentation table or subject area, and then and drag and drop the updated logical objects to the Presentation layer. For this reason, it is best to wait until the Business Model and Mapping layer is relatively stable before adding customizations in the Presentation layer.

Working with Subject Areas

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

Typically, subject areas are created automatically by dragging and dropping business models from the logical layer.

To edit the properties of a subject area:

  1. In the Presentation layer, double-click a subject area. The Subject Area dialog is displayed.

  2. In the General tab, you can change the name for the subject area. Note that aliases are created automatically whenever presentation objects are renamed, so that any queries using the original name do not break.

    Also, a subject area cannot have the same name as any of its child presentation tables. For example, you cannot have a subject area called Customer that has a Customer table within it.

  3. To set permissions for this subject area, click Permissions. See "Setting Permissions for Presentation Layer Objects" for more information.

  4. Select Custom display name or Custom description to dynamically display a custom name or custom description based on a session variable, typically for localization purposes. See "Localizing Oracle Business Intelligence Deployments" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information about localization.

  5. The Business model list displays the business model for this subject area.

  6. To expose the logical keys to other applications, select the option Export logical keys.

    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, because the Oracle BI Server uses the joins defined in the repository.

    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.
  7. Optionally, you can set an Implicit Fact Column. This column is 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 or contexts.

  8. Optionally, type a description. This description appears in a mouse-over tooltip for the subject area in Oracle BI Answers.

  9. In the Presentation Tables tab, you can add, remove, edit, or reorder the presentation tables for this subject area.

  10. Use the Aliases tab to specify or delete aliases for this subject area. See "Creating Aliases (Synonyms) for Presentation Layer Objects" for more information about aliases.

  11. Click OK.

Working with Presentation Tables and Columns

Presentation tables and presentation columns appear as folders and columns in Oracle BI Answers. You can customize presentation tables and presentation columns to help users craft queries based on their business needs.

This section contains the following topics:

Creating and Managing Presentation Tables

You can use presentation tables to organize columns into categories that make sense to the user community. 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.

Typically, presentation tables are created automatically by dragging and dropping logical tables from the logical layer.

To edit the properties of a presentation table:

  1. In the Presentation layer, double-click a presentation table. The Presentation Table dialog appears.

  2. In the General tab, you can change the name for the presentation table. Note that aliases are created automatically whenever presentation objects are renamed, so that any queries using the original name do not break.

    Also, a presentation table cannot have the same name as its parent subject area. For example, you cannot have a subject area called Customer that has a Customer table within it.

  3. To set permissions for this presentation table, click Permissions. See "Setting Permissions for Presentation Layer Objects" for more information.

  4. Select Custom display name or Custom description to dynamically display a custom name or custom description based on a session variable, typically for localization purposes. See "Localizing Oracle Business Intelligence Deployments" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information about localization.

  5. In the Columns tab, you can add, remove, edit, or reorder the presentation columns for this presentation table.

  6. In the Hierarchies tab, you can add, remove, edit, or reorder the presentation hierarchies for this presentation table.

  7. Use the Aliases tab to specify or delete aliases for this presentation table. See "Creating Aliases (Synonyms) for Presentation Layer Objects" for more information about aliases.

  8. Click OK.

To reorder a table or sort all tables in a subject area:

  1. In the Presentation layer, double-click a subject area.

  2. In the Subject Area dialog, click the Presentation Tables tab.

  3. To move a table, in the Name list, select the table you want to reorder. Then, 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.

Nesting Folders in Answers

You can use the Administration Tool to update Presentation layer metadata to give the appearance of nested folders in Answers. To do this, open the Properties dialog for the presentation table that corresponds to the folder you want to nest and add -> to the beginning of the Description field.

For example, to nest the Sales Facts folder in the Facts folder in Answers, place the Sales Facts presentation table directly after the Facts presentation table in the metadata and add -> to the Description field in the Presentation Table - Sales Facts dialog. To nest a second folder called Marketing Facts in the Facts folder, add -> to the Description field for the Marketing Facts presentation table and place it directly after Sales Facts. Only two levels of nesting are supported.

Alternatively, you can prefix the name of the presentation table to be nested with a hyphen and a space and then place the table after the presentation table in which it nests. For example, to nest Sales Facts within Facts, place Sales Facts directly after Facts 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. However, the hyphen and space are visible to ODBC clients.

Note that these techniques do not provide true folder nesting. If you move the parent presentation table, the "child" presentation tables do not move with it. These techniques only provide the appearance of nesting in Answers.

Creating and Managing 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 Custom display name options in the Presentation Column dialog.

To provide a convenient organization for your 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 lets you 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.

Caution:

When you drag columns to presentation tables, make sure that columns with the same name or an alias of the same name do not already exist.

Typically, presentation columns are created automatically by dragging and dropping logical columns from the logical layer.

To edit the properties of a presentation column:

  1. In the Presentation layer, double-click a presentation column to display the Presentation Column dialog.

  2. In the General tab, to specify a name that is different from the Logical Column name, clear Use Logical Column Name, and then type a name for the column. Note that aliases are created automatically whenever presentation objects are renamed, so that any queries using the original name do not break.

  3. To set permissions for this presentation column, click Permissions. See "Setting Permissions for Presentation Layer Objects" for more information.

  4. Select Custom display name or Custom description to dynamically display a custom name or custom description based on a session variable, typically for localization purposes. See "Localizing Oracle Business Intelligence Deployments" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information about localization.

    Note:

    You can also use the Custom display name and Custom description fields to propagate UI hints (labels and tooltips) from an ADF data source to display in Oracle BI Answers. See "Propagating Labels and Tooltips from an Oracle Application Development Framework Data Source to Display When Working with Analyses" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information about how to set up ADF data source UI hints.
  5. The Logical Column field displays the name of the logical column for this presentation column. Click Edit to make any changes to the logical column object.

  6. Use the Aliases tab to specify or delete aliases for this presentation column. See "Creating Aliases (Synonyms) for Presentation Layer Objects" for more information about aliases.

To reorder a presentation column:

  1. In the Presentation layer, right-click a presentation table and 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.

Working with Presentation Hierarchies and Levels

Presentation hierarchies and presentation levels provide an explicit way to expose the multidimensional model in Oracle BI Answers. When presentation hierarchies and levels are defined in the Presentation layer, roll-up information is displayed in the Oracle BI Answers navigation pane, providing users with important contextual information.

Be aware that members in a presentation hierarchy are not visible in the Presentation layer. Instead, you can see hierarchy members in Answers.

Most importantly, users can create hierarchy-based queries using these objects. Presentation hierarchies expose analytic functionality such as member selection, custom member groups, and asymmetric queries.

As with other Presentation layer objects, you can also provide localization information and apply fine-grained access control to presentation hierarchies and levels.

If you have a repository from a previous release, note that presentation hierarchies do not appear in the Presentation layer automatically as part of the RPD upgrade process. You must manually create these objects by dragging logical dimensions from the Business Model and Mapping layer to the appropriate presentation tables.

This section contains the following topics:

Creating and Managing Presentation Hierarchies

To create a presentation hierarchy, you can drag a logical dimension hierarchy from the Business Model and Mapping layer to a table in the Presentation layer. The presentation hierarchy object must be located within a presentation table, unlike in the Business Model and Mapping layer, where logical dimensions are peer objects of tables. Presentation hierarchies are also displayed within their associated tables in Oracle BI Answers, providing a conceptually simpler model.

If a logical dimension spans multiple logical tables in the Business Model and Mapping layer, it is a best practice to model the separate logical tables as a single presentation table in the Presentation layer.

There are several ways to create presentation hierarchies:

  • When you drag an entire business model to the Presentation layer, the presentation hierarchies and constituent levels appear automatically, along with other presentation objects.

  • When you drag a logical dimension table to the Presentation layer, presentation hierarchies and levels based on those dimensions are created automatically.

  • You can also drag individual logical dimensions to the appropriate presentation tables to create corresponding presentation hierarchies within those tables.

  • As with most other objects in the Administration Tool, you can right-click a presentation table and select New Object > Presentation Hierarchy to manually define the object.

You can also drag an individual logical level from the Business Model and Mapping layer to a presentation table to create a presentation hierarchy that is a subset of the logical dimension hierarchy.

For example, suppose a logical dimension has the levels All Markets, Total US, Region, District, Market, and Market Key. Dragging and dropping the entire logical dimension to the corresponding presentation table appears as follows:

Description of p_hier_total.gif follows
Description of the illustration p_hier_total.gif

However, dragging and dropping the Region level to the same presentation table appears as follows:

Description of p_hier_sub.gif follows
Description of the illustration p_hier_sub.gif

Modeling Dimensions with Multiple Hierarchies in the Presentation Layer

For logical dimensions that contain multiple logical hierarchies, multiple separate presentation hierarchies are created. For example, the following logical dimension called Product contains the two hierarchies Category and Country:

Description of p_hier_peer1.gif follows
Description of the illustration p_hier_peer1.gif

In the Business Model and Mapping layer, this logical dimension is modeled as a single dimension object that contains multiple hierarchies. In contrast, the Presentation layer models this dimension as two separate objects: one that displays the drill path through the Category level, and another that shows the drill path through the Country level, as follows:

Description of p_hier_peer2.gif follows
Description of the illustration p_hier_peer2.gif

Editing Presentation Hierarchy Objects

You can edit presentation hierarchy properties, including setting permissions to apply role-based access control, setting a custom display name for localization purposes, and changing the levels in a hierarchy.

To edit the properties of a presentation hierarchy:

  1. In the Presentation layer, double-click a presentation hierarchy to display the Presentation Hierarchy dialog.

  2. In the General tab, you can change the following:

  3. The Levels tab lists the levels within the hierarchy and their order. This tab is not available for parent-child hierarchies. You can add, delete, or reorder levels. You can also click the Edit button to edit properties for a particular level. See "Creating and Managing Presentation Levels" for information about level properties.

  4. The Display Columns tab is only available for parent-child hierarchies. Because parent-child hierarchies do not contain levels, display columns are defined for the presentation hierarchy object as a whole. Use the Display Columns tab to define which columns should be used for display for this parent-child hierarchy.

    You can add, delete, or reorder display columns. You can also click the Edit button to edit properties for a particular column.

  5. Use the Aliases tab to specify or delete aliases for this presentation hierarchy. See "Creating Aliases (Synonyms) for Presentation Layer Objects" for more information about aliases.

Creating and Managing Presentation Levels

Presentation levels are displayed within hierarchical columns in Oracle BI Answers. Presentation levels are typically created automatically when presentation hierarchies are created.

To edit the properties of a presentation level:

  1. In the Presentation layer, double-click a presentation level to display the Presentation level dialog.

  2. In the General tab, you can change the following:

  3. The Drill To Levels and Drill From Levels tabs are reserved for a future release and are not currently used. An additional option called Generate Drill Graph, available as a right-click option for any Presentation layer object, is also reserved for a future release.

  4. Use the Display Columns tab to define which columns should be used for display for that level (on drill-down). For example, if two columns called "Name" and "ID" exist at the same level, you can choose to display "Name" because it is the more user-friendly option.

    You can add, delete, or reorder display columns. You can also click the Edit button to edit properties for a particular column.

    As an alternative to defining display columns in this tab, you can drag a presentation column directly onto the presentation level in the Presentation layer of the Administration Tool. Doing this automatically adds the column as a display column for the presentation level.

    Note that the display columns that appear by default when a presentation level is created are based on which key columns for the corresponding logical level have the Use for display option selected.

  5. Use the Aliases tab to specify or delete aliases for a presentation level. See "Creating Aliases (Synonyms) for Presentation Layer Objects" for more information about aliases.

Setting Permissions for Presentation Layer Objects

You can apply access control to restrict which individual users or application roles (groups) can access particular presentation layer objects. For example, you can provide read-only access to a set of presentation tables for a particular application role, read-write access for a second application role, and no access for a third application role.

You can also use the Security Manager to set up privileges and permissions. The Security Manager is useful for setting permissions for individual application roles to many objects at once, unlike permissions in the Presentation layer, which you can only set for one object at a time. See "Setting Up Object Permissions" for information about setting up object permissions in the Security Manager. For a full description of data access security in Oracle Business Intelligence, see Chapter 13.

To set permissions for presentation layer objects:

  1. In the Presentation layer, double-click a presentation object, such as a subject area, table, column, or hierarchy.

  2. In the General tab, click Permissions.

  3. In the Permissions dialog, any users or application roles with the Default permission do not appear in the User/Application Roles list. Select Show all users/application roles to see all users and application roles.

  4. For each user and application role, you can allow or disallow access privileges for this presentation object by selecting one of the following options:

    • Read. Only allows read access to this object.

    • Read/Write. Provides both read and write access to this object.

    • No access. Explicitly denies all access to this object.

    • Default. No object-specific access rules apply. The default access control for this user or application role, as defined in the Security Manager, controls what the user or application role can do with this object.

    Note that any permissions applied to a presentation object also apply to its child objects. For example, permissions applied to a presentation table also apply to any presentation columns, hierarchies, and levels in that table.

  5. Click OK.

  6. Click OK in the Properties dialog for this presentation object.

Generating a Permission Report for Presentation Layer Objects

You can generate a permission report for individual presentation layer objects to see a summary of how permissions have been applied for that object. To do this, right-click any presentation object and select Permission Report. The Permission Report dialog displays the name and a description of the presentation object, along with a list of users/application roles and their permissions.

Sorting Columns in the Permissions Dialog

There are six ways that you can sort the types and User/Application Role names in the Permissions dialog. To change the sort, click the heading of the first or second column. The first column has no heading and contains an icon that represents the type of user or application role. The second column contains the name of the User/Application Role object. Note that you cannot sort on the columns for individual object permissions (like Read, Read/Write, and so on).

There are three ways to sort by type, and two ways to sort the list of user and application role names. This results in a total of six possible sort results (3 x 2 = 6). The following list shows the sort results available by clicking the type column:

  • Everyone, Application Roles, Users (ascending by name of type)

  • Users, Application Roles, Everyone (descending by name of type)

  • Type column is in no particular order (Type value is ignored, as all names in User/Application Role column are sorted in ascending order by value in User/Application Role column)

The following list shows the sort results available by clicking the User/Application Role column:

  • Ascending within the type

  • Descending within the type

Creating Aliases (Synonyms) for Presentation Layer Objects

Each presentation object can have a list of aliases (synonyms) for its name that can be used in Logical SQL queries. To create the list of aliases, use the Alias tab in the Properties dialog for the appropriate presentation object (subject area, presentation table, presentation hierarchy, presentation level, or presentation column).

Because Presentation layer objects are often deleted and then re-created during the repository development process, it is best to wait until your logical business model is relatively stable before creating aliases for presentation objects.

You can use this feature to rename presentation objects without breaking references that any existing requests have to the old names, including requests from Answers, Oracle BI Publisher, or other Logical SQL clients. If you are still developing a new repository, you might want to wait until the repository is stable before renaming objects.

For example, consider a subject area called "Sample Sales Reduced" that contains a presentation table called "Facts Other." If you rename the presentation column called "# of Customers" to "Number of Customers," any requests that use "# of Customers" fail. However, if you add "# of Customers" to the list of synonyms in the Alias tab for the "Number of Customers" column, then queries containing both "# of Customers" and "Number of Customers" succeed and return the same results.

Note the following:

  • Aliases for presentation objects do not appear in Answers or other query clients when creating new queries. Only the primary names of subject areas, hierarchies, levels, tables, and columns appear.

  • This feature works in a different way from SQL aliases or the alias feature in the Physical layer. It simply provides synonyms for object names, much like "synonyms" in SQL.

  • Aliases are created automatically when you rename presentation objects. For example, if you change Catalog to Catalog1, the original name Catalog is added to the Aliases list.

  • You cannot rename a Presentation layer object to a name that is already in use as an alias for an object of the same type.

To add or delete an alias for a presentation object:

  1. In the Presentation layer, double-click a presentation object, such as a subject area, table, column, or hierarchy.

  2. Click the Aliases tab.

  3. To add an alias, click the New button, and then type the text string to use for the alias.

  4. To delete an alias, select the alias you want to delete from the Aliases list, then click the Delete button.

  5. Click OK.