8 Working with Logical Tables, Joins, and Columns

The Business Model and Mapping layer of the Oracle BI repository defines the business, or logical, model of the data and specifies the mapping between the business model and the Physical layer schemas. Business models are always dimensional, unlike objects in the Physical layer, which reflect the organization of the data sources. The Business Model and Mapping layer can contain one or more business models. Each business model contains logical tables, columns, and joins.

Even though similar terminology is used for logical table and physical table objects, such as the concept of keys, logical tables and joins in the Business Model and Mapping layer have their own set of rules that differ from those of relational models. For example, logical fact tables are not required to have keys, and logical joins can represent many possible physical joins.

Logical tables, joins, mappings, and other objects in the Business Model and Mapping layer are typically created automatically when you drag and drop objects from the Physical layer to a particular business model. After these objects have been created, you can perform tasks like creating additional logical joins, performing calculations and transformations on columns, and adding and removing keys from dimension and fact tables.

This chapter contains the following sections:

Creating the Business Model and Mapping Layer

After creating all of the elements of the Physical layer, you can drag tables or columns from the Physical layer to a business model in the Business Model and Mapping layer to create logical objects in the metadata.

This section contains the following topics:

Creating Business Models

The Business Model and Mapping layer of the Administration Tool can contain one or more business models. A business model contains the business model definitions and the mappings from logical to physical tables for the business model.

When you work in a repository in offline mode, 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.

To create a business model:

  1. In the Administration Tool, right-click in the Business Model and Mapping layer below any existing objects.

  2. Select the option New Business Model from the shortcut menu.

  3. Specify a name for the business model.

  4. New business models are disabled by default. If you want to make the corresponding Presentation layer available for queries, deselect Disabled.

    Note:

    The business model should be consistent before you deselect this option.

  5. Optionally, type a description of the business model.

  6. Click OK.

After you create a business model, you can create business model objects by dragging and dropping objects from the Physical layer. See the next section for more information.

Automatically Creating Business Model Objects

To automatically map objects in the Business Model and Mapping layer to sources in the Physical layer, you can drag and drop Physical layer objects to a particular business model in the logical layer. When you drag a physical table to the Business Model and Mapping layer, a corresponding logical table is created. For each physical column in the table, a corresponding logical column is created. If you drag multiple tables at once, a logical join is created for each physical join, but only the first time the tables are dragged onto a new business model.

Automatically Creating Business Model Objects for Multidimensional Data Sources

Setting up objects in the Business Model and Mapping layer for multidimensional data sources is similar to setting up logical layer objects for a relational data source. To create the business model layer, you can drag and drop the Physical layer cube to the logical layer. Oracle Business Intelligence automatically creates a fully configured and consistent business model that retains metrics, attributes and dimensions.

Note:

For Essbase data sources, it is recommended that you create a separate business model for each Essbase cube. To do this, drag each cube individually to the Business Model and Mapping layer.

Duplicating a Business Model and Subject Area

This feature lets you select a business model and its corresponding subject area (or a subject area and its corresponding business model), make a copy, and assign new names to the duplicates. Note that aliases are not copied.

To copy a business model and subject area:

  1. Perform one of the following steps:

    • In the Business Model and Mapping layer of the Administration Tool, right-click a business model and select Duplicate with Subject Area.

    • In the Presentation layer of the Administration Tool, right-click a subject area and select Duplicate with Business Model.

  2. In the Copy Business Model and Subject Area dialog, select the business model and corresponding subject area you want to copy.

  3. Specify new names for the business model and subject area in the appropriate name fields, and then click OK.

    The copied business model appears in the Business Model and Mapping layer, and the copied subject area appears in the Presentation layer.

Working with the Business Model Diagram

In addition to working with Business Model and Mapping layer objects in the middle pane of the Administration Tool, you can open the Business Model Diagram to see a graphical model of logical tables and joins.

To access the Business Model Diagram, right-click an object in the Business Model and Mapping layer (such as a dimension or fact table) and select Business Model Diagram. Then, select one of the following options:

  • Whole Diagram. Displays all logical tables and joins in the business model.

  • Selected Tables Only. Displays only the selected logical tables. Logical joins appear only if they exist between the objects that you selected. This option is only available when you select one or more logical tables.

  • Selected Tables and Direct Joins. Displays the selected logical tables and any logical tables that join to the tables that you selected. This option is only available when you select one or more logical tables.

  • Selected Fact Tables and Dimensions. Displays the selected logical tables and their associated logical dimensions. This option is only available when your selection includes at least one fact table.

To add additional tables to the Business Model Diagram, leave the Business Model Diagram window open and then right-click the table or tables you want to add. Then, select Business Model Diagram and choose one of the display options.

Additional options are available in the right-click menu for the graphical tables and joins displayed in the Business Model Diagram. For example, you can right-click an object and choose Hide to hide particular objects in the diagram, you can delete objects or view their properties, or you can add additional related objects using the right-click options Add Direct Joins, Add Tables Joined to Whole Selection, and Add All Joins. You can also select Find in Tree View to locate a particular object in the Business Model and Mapping layer view in the middle pane, or check out objects in online mode.

Creating and Managing Logical Tables

Logical tables exist in the Business Model and Mapping layer. The logical schema defined in each business model must contain at least two logical tables, and you must define relationships between them.

Each logical table has one or more logical columns and one or more logical table sources associated with it. You can change the logical table name, reorder the logical table sources, and configure the logical keys, both primary and foreign.

This section contains the following topics:

Creating Logical Tables

Typically, you create logical tables by dragging and dropping a physical table from the Physical layer to a business model in the Business Model and Mapping layer. If a table does not exist in your physical schema, you need to create the logical table manually.

Drag and drop operations are usually the fastest method for creating objects in the Business Model and Mapping layer. If you drag and drop physical tables from the Physical layer to the Business Model and Mapping layer, the columns belonging to the table are also copied. After you drag and drop objects into the Business Model and Mapping layer, you can modify them in any way necessary without affecting the objects in the Physical layer.

When you drag physical tables (with key and foreign key relationships defined) to a business model, logical keys and joins are created that mirror the keys and joins in the Physical layer. This occurs only if the tables that you drag include the table with the foreign keys. Additionally, if you create new tables or subsequently drag additional tables from the Physical layer to the Business Model and Mapping layer, the logical mappings between the new or newly dragged tables and the previously dragged tables must be created manually.

See "Defining Logical Joins with the Joins Manager" and "Defining Logical Joins with the Business Model Diagram" for more information about joins.

To create a logical table by dragging and dropping:

  1. In the Administration Tool, select one or more table objects in the Physical layer.

    You must include the table with the foreign keys if you want to preserve the keys and joins from the Physical layer.

  2. Drag and drop the table objects to a business model in the Business Model and Mapping layer.

    When you drop them, the table objects, including the physical source mappings, are created automatically in the Business Model and Mapping layer.

To create a logical table manually:

  1. In the Business Model and Mapping layer of the Administration Tool, right-click the business model in which you want to create the table and select New Object > Logical Table.

    The Logical Table dialog appears.

  2. In the General tab, type a name for the logical table.

  3. If this is a lookup table, select the option Lookup table. A lookup table stores multilingual data corresponding to rows in the base tables. 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 and lookup tables.

  4. Optionally, type a description of the table.

  5. Click OK.

After creating a logical table manually, you must create all keys and joins manually.

Creating and Managing Logical Table Sources

You can add a new logical table source, edit or delete an existing table source, create or change mappings to the table source, and define when to use logical tables sources and how content is aggregated. See Chapter 10, "Managing Logical Table Sources (Mappings)" for instructions about how to perform these tasks.

Specifying a Primary Key in a Logical Table

After creating tables in the Business Model and Mapping layer, you specify a primary key for each dimension table. Logical dimension tables must have a logical primary key. Logical keys can be composed of one or more logical columns.

Note:

It is recommended that you do not specify logical keys for logical fact tables.

To specify a primary key in a logical table:

  1. In the Business Model and Mapping layer of the Administration Tool, double-click a table.

  2. In the Logical Table dialog, select the Keys tab and then click New.

  3. In the Logical Key dialog, type a name for the key and select the column that defines the key of the logical table.

  4. Click OK.

Reviewing Foreign Keys for a Logical Table

It is recommended that you do not use foreign key joins in logical tables. If you must create these joins, you must first enable the option Allow logical foreign key join creation in the Options dialog. See "Creating Logical Foreign Key Joins with the Joins Manager" for more information.

The Foreign Keys tab of the Logical Table dialog exists so that you can view logical foreign keys you might have had in a previous release of Oracle Business Intelligence.

Defining Logical Joins

Relationships between logical tables are expressed by logical joins. Logical joins are conceptual, rather than physical, joins. In other words, they do not join to particular keys or columns. A single logical join can correspond to many possible physical joins.

A key property of a logical join is cardinality. Cardinality expresses how rows in one table are related to rows in the table to which it is joined. A one-to-many cardinality means that for every row in the first logical dimension table, there are 0, 1, or many rows in the second logical table. The Administration Tool considers a table to be a logical fact table if it is at the Many end of all logical joins that connect it to other logical tables.

Specifying the logical table joins is required so that the Oracle BI Server can have the necessary metadata to translate a logical request against the business model to SQL queries against the physical data sources. The logical join information provides the Oracle BI Server with the many-to-one relationships between the logical tables. This logical join information is used when the Oracle BI Server generates queries against the underlying databases.

You do not need to create logical joins in the Business Model and Mapping layer if both of the following statements are true:

  • You create the logical tables by simultaneously dragging and dropping all required physical tables to the Business Model and Mapping layer.

  • The logical joins are the same as the joins in the Physical layer.

However, you will probably have to create some logical joins in the Business Model and Mapping layer, because you will rarely drag and drop all physical tables simultaneously except in very simple models.

You can create logical joins using either the Joins Manager or the Business Model Diagram. When you create a complex join in the Physical layer, you can specify expressions and the specific columns on which to create the join. When you create a logical join in the Business Model and Mapping layer, you cannot specify expressions or columns on which to create the join. The existence of a join in the Physical layer does not require a matching join in the Business Model and Mapping layer.

Note:

It is recommended that you do not have foreign keys for logical tables. However, for backward compatibility, you can create logical foreign key joins using the Joins Manager if you select Allow logical foreign key join creation in the Options dialog.

A logical key for a fact table must be made up of the key columns that join to the attribute tables. Logical foreign key joins may be needed if the Oracle BI Server is to be used as an ODBC data source for certain third-party query and reporting tools.

This section contains the following topics:

Defining Logical Joins with the Business Model Diagram

The Business Model Diagram shows logical tables and any defined joins between them. You can use the Business Model Diagram to define logical joins between tables.

To define a logical join with the Business Model Diagram:

  1. In the Administration Tool, right-click a business model and select Business Model Diagram, then select Whole Diagram.

  2. Click the New Join button in the toolbar.

  3. In the business model diagram, move the cursor to the first table in the join (the one of the one-to-many join).

  4. Left-click and move the cursor to the table to which you want to make the join (the many of the one-to-many join), and then left-click the second table. The Logical Join dialog appears.

  5. (Optional) To specify a driving table for the key, select a table from the Driving list, and an applicable cardinality.

    This option is useful for optimizing the manner in which the Oracle BI Server processes multi-database inner joins when one table is very small and the other table is very large. Do not select a driving table unless multi-database joins are going to occur. See "Specifying a Driving Table" for more information about driving tables.

    Caution:

    Use extreme caution in deciding whether to specify a driving table. Driving tables are used for query optimization only under rare circumstances and when the driving table is extremely small (fewer than 1000 rows). Choosing a driving table incorrectly can lead to severe performance degradation.

  6. Select the join type from the Type list, or keep the default value.

  7. Set the Cardinality for each side of the join, or keep the default values.

  8. Click OK to save your work.

Defining Logical Joins with the Joins Manager

You can use the Joins Manager to view logical join relationships and to create logical joins. You can also use the Joins Manager to create logical foreign key joins if you select Allow logical foreign key join creation in the Options dialog, although this is not recommended.

This section contains the following topics:

Creating Logical Joins with the Joins Manager

Logical joins are recommended over logical foreign key joins in the Business Model and Mapping layer.

To create a logical join with the Joins Manager:

  1. In the Administration Tool, select Manage, then select Joins.

    The Joins Manager dialog appears.

  2. Select Action > New > Logical Join.

    The Logical Join dialog appears.

  3. Type a name for the logical join.

  4. In the Table lists on the left and right side of the dialog, select the tables that the logical join references.

  5. (Optional) To specify a driving table for the key, select a table from the Driving list, and an applicable cardinality.

    This option is useful for optimizing the manner in which the Oracle BI Server processes multi-database inner joins when one table is very small and the other table is very large. Do not select a driving table unless multi-database joins are going to occur. See "Specifying a Driving Table" for more information about driving tables.

    Caution:

    Use extreme caution in deciding whether to specify a driving table. Driving tables are used for query optimization only under rare circumstances and when the driving table is extremely small, that is, less than 1000 rows. Choosing a driving table incorrectly can lead to severe performance degradation.

  6. Select the join type from the Type list, or keep the default value.

  7. Set the Cardinality for each side of the join, or keep the default values.

  8. Click OK.

Creating Logical Foreign Key Joins with the Joins Manager

Logical foreign key joins might be needed if the Oracle BI Server is to be used as an ODBC data source for certain third-party query and reporting tools. Typically, you should not create logical foreign keys. This capability is in the Administration Tool to provide compatibility with previous releases.

To create a logical foreign key join with the Joins Manager:

  1. In the Administration Tool, select Tools, then select Options.

  2. In the General tab of the Options dialog, select Allow logical foreign key join creation.

  3. Click OK.

  4. Select Manage, then select Joins to display the Joins Manager.

  5. Select Action > New > Logical Foreign Key.

  6. In the Browse dialog, double-click a table to display the Logical Foreign Key dialog.

  7. Type a name for the foreign key.

  8. In the Table list on the left side of the dialog, select the table that the foreign key references.

  9. Select the columns in the left table that the foreign key references.

  10. Select the columns in the right table that make up the foreign key columns.

  11. (Optional) To specify a driving table for the key, select a table from the Driving list, and an applicable cardinality.

    This option is useful for optimizing the manner in which the Oracle BI Server processes multi-database inner joins when one table is very small and the other table is very large. Do not select a driving table unless multi-database joins are going to occur. See "Specifying a Driving Table" for more information about driving tables.

    Caution:

    Use extreme caution in deciding whether to specify a driving table. Driving tables are used for query optimization only under rare circumstances and when the driving table is extremely small, that is, less than 1000 rows. Choosing a driving table incorrectly can lead to severe performance degradation.

  12. Select the join type from the Type list, or keep the default value.

  13. Set the Cardinality for each side of the join, or keep the default values.

  14. Enter an expression for the join, or click the Expression Builder button to define the expression in Expression Builder.

  15. Click OK to save your work.

Specifying a Driving Table

You can specify a driving table for logical joins from the Logical Joins window. Driving tables are useful for optimizing the manner in which the Oracle BI Server processes cross-database joins when one table is very small and the other table is very large. Specifying driving tables leads to query optimization only when the number of rows being selected from the driving table is much smaller than the number of rows in the table to which it is being joined.

Caution:

To avoid problems, only specify driving tables when the driving table is extremely small - less than 1000 rows.

When you specify a driving table, the Oracle BI Server uses it if the query plan determines that its use will optimize query processing. The small table (the driving table) is scanned, and parameterized queries are issued to the large table to select matching rows. The other tables, including other driving tables, are then joined together.

Caution:

If large numbers of rows are being selected from the driving table, specifying a driving table could lead to significant performance degradation or, if the MAX_QUERIES_PER_DRIVE_JOIN limit is exceeded, the query terminates.

In general, driving tables can be used with inner joins, and for outer joins when the driving table is the left table for a left outer join, or the right table for a right outer join. Driving tables are not used for full outer joins. See "Defining Logical Joins" for instructions on specifying a driving table.

There are two entries in the database features table that control and tune driving table performance.

  • MAX_PARAMETERS_PER_DRIVE_JOIN

    This is a performance tuning parameter. In general, the larger its value, the fewer parameterized queries need to be generated. Values that are too large can result in parameterized queries that fail due to back-end database limitations. Setting the value to 0 (zero) turns off drive table joins.

  • MAX_QUERIES_PER_DRIVE_JOIN

    This is used to prevent runaway drive table joins. If the number of parameterized queries exceeds its value, the query is terminated and an error message is returned to the user.

Identifying Physical Tables That Map to Logical Objects

The Physical Diagram shows the physical tables that map to the selected logical object and the physical joins between each table.

One of the joins options, Object(s) and Direct Joins within Business Model, is unique to the logical layer. It creates a physical diagram of the tables that meet both of the following conditions:

  • Tables in the selected objects and tables that join directly

  • Tables that are mapped (exist in logical table sources in the business model) in the business model

To open the Physical Diagram for a logical object:

  1. In the Business Model and Mapping layer of the Administration Tool, right-click a business model, logical table, or logical table source.

  2. Select Physical Diagram and then one of the joins options.

  3. Click and drag any object to more clearly view the relationship lines, such as one-to-many.

Creating and Managing Logical Columns

Many logical columns are automatically created by dragging tables from the Physical layer to the Business Model and Mapping layer. Other logical columns, especially ones that involve calculations based on other logical columns, can be created later.

Logical columns are displayed in a tree structure expanded out from the logical table to which they belong. If the column is a primary key column or participates in a primary key, the column is displayed with a key icon. If the column has an aggregation rule, it is displayed with a ruler icon. You can also reorder logical columns in the Business Model and Mapping layer.

This section contains the following topics:

Creating Logical Columns

The following procedure explains how to create logical columns in the Business Model and Mapping layer.

To create a logical column:

  1. In the Business Model and Mapping layer, right-click a logical table.

  2. From the shortcut menu, select New Object, then select Logical Column.

  3. In the General tab, type a name for the logical column.

    The name of the business model and the associated logical table appear in the Belongs to Table field.

  4. Select Writeable to enable write back for this column. See "Enabling Write Back On Columns" for more information.

  5. Optionally, you can assign a different column on which to base the sort order for a column. See "Basing the Sort for a Logical Column on a Different Column" for details.

  6. Optionally, you can assign a descriptor ID column for this column. See "Assigning a Descriptor ID Column to a Logical Column" for details.

  7. Optionally, on the Column Source tab, you can specify that this logical column is derived from other logical columns. See "Creating Derived Columns" for details.

  8. Optionally, on the Aggregation tab, you can set column aggregation. See "Setting Default Levels of Aggregation for Measure Columns" for details.

  9. Optionally, on the Levels tab, you can associate attributes with a logical level. Measures can be associated with levels from multiple dimensions and always aggregate to the levels specified. See "Associating an Attribute with a Logical Level in Dimension Tables" for details.

  10. Click OK.

Basing the Sort for a Logical Column on a Different Column

For a logical column, you can specify a different column on which to base the sort. This changes the sort order of a column when you do not want to order the values lexicographically. Lexicographical sort arranges the results in alphabetic order such as in a dictionary. In this type of sort, numbers are ordered by their alphabetic spelling and not divided into a separate group.

For example, if you sorted on month (using a column such as MONTH_NAME), the results would be returned as February, January, March, and so on, in lexicographical sort order. However, you might want months to be sorted in chronological order. Therefore, your table should have a month key (such as MONTH_KEY) with values of 1 (January), 2 (February), 3 (March), and so on. To achieve the desired sort, you set the Sort order column field for the MONTH_NAME column to be MONTH_KEY. Then, a request to order by MONTH_NAME would return January, February, March, and so on.

To assign a different column on which to base the sort order for a column:

  1. In the Logical Column dialog, in the General tab, click Set next to the Sort order column field.

  2. In the Browse dialog, select a column.

  3. To view the column details, click View to open the Logical Column dialog for that column, and then click Cancel.

    You can make some changes in this dialog. If you make changes, click OK to accept the changes instead of Cancel.

  4. In the Browse dialog, click OK.

Assigning a Descriptor ID Column to a Logical Column

When multilingual columns are based on a lookup function, it is common to specify the non-translated lookup key column as the descriptor ID column of the translated column. Assigning a descriptor ID column enables Double Column Support, a feature which helps in defining language-independent filters. For example, in Answers, users see the display column, but the query filters on the hidden descriptor ID column.

For more information, see "Supporting Multilingual Data" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

To assign a Descriptor ID column to a display column:

  1. In the Logical Column dialog, in the General tab, click Set next to the Descriptor ID column field.

  2. In the Browse dialog, select a key column.

  3. To view the column details, click View to open the Logical Column dialog for that column, and then click Cancel.

    You can make some changes in this dialog. If you make changes, click OK to accept the changes instead of Cancel.

  4. In the Browse dialog, click OK.

Creating Derived Columns

Some columns are derived from other logical columns as a way to apply post-aggregation calculations to measures. To do this, you specify the derived column expression in the Column Source tab of the Logical Column dialog.

You can also create a set of derived columns using the Calculation Wizard. See "Using the Calculation Wizard" for more information.

Note that if the parameter PREVENT_DIVIDE_BY_ZERO is set to YES in NQSConfig.INI, the Oracle BI Server prevents errors in divide-by-zero situations, even for Answers column calculations. The Oracle BI Server creates a divide-by-zero prevention expression using nullif() or a similar function when it writes the physical SQL. Because of this, you do not have to use CASE statements to avoid divide-by-zero errors, as long as PREVENT_DIVIDE_BY_ZERO is set to YES (the default value).See Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information about NQSConfig.INI settings.

You can also apply calculations pre-aggregation. See "Defining Physical to Logical Table Source Mappings and Creating Calculated Items" for more information.

To specify a derived column:

  1. In the Logical Column dialog, select the Column Source tab.

  2. Select the option Derived from existing columns using an expression.

  3. Click the Expression Builder button to open Expression Builder.

  4. In the Expression Builder - Derived logical column dialog, specify the expression from which the logical column should be derived.

    Note:

    To optimize performance, do not define aggregations in Expression Builder. Instead, use the Aggregation tab of the Logical Column dialog. See "Setting Default Levels of Aggregation for Measure Columns" for more information.

  5. Click OK.

Note that you can display data from multilingual database schemas by using Expression Builder to create a lookup function. For more information, see "Supporting Multilingual Data" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

Configuring Logical Columns for Multicurrency Support

You can configure logical columns so that Oracle Business Intelligence users can select the currency in which they prefer to view currency columns in analyses and dashboards. You can set up this feature so that all users see the same static list of currency options, or you can provide a dynamic list of currency options that changes based on a Logical SQL statement you specify.

To configure logical columns for multicurrency support:

  1. Create a session variable named PREFERRED_CURRENCY, along with an initialization block to use in the variable. Make sure to select Enable any user to set the value when you create the session variable. Note that when you use session variables in an expression for Oracle BI Presentation Services, you must preface their names with NQ_SESSION.

    See "Creating Session Variables" and "Creating Initialization Blocks" for detailed information about setting up session variables and initialization blocks.

  2. Edit any logical columns that display currency values to use the appropriate conversion factor using the PREFERRED_CURRENCY session variable. To do this, double-click the appropriate logical column in the Business Model and Mapping layer, select the Column Source tab, and create a derived expression that uses the PREFERRED_CURRENCY variable.

    For example, the following logical column expression uses the value of the NQ_SESSION.PREFERRED_CURRENCY variable to switch between different currency columns. Note that the currency columns are expected to have the appropriate converted values.

    INDEXCOL( CASE VALUEOF(NQ_SESSION.PREFERRED_CURRENCY) WHEN 'gc1' THEN 0
WHEN 'gc2' THEN 1 WHEN 'orgc' THEN 2 WHEN 'lc1' THEN 3 ELSE 4 END,
"Paint"."Sales Facts"."USDCurrency",
"Paint"."Sales Facts"."DEMCurrency" ,
"Paint"."Sales Facts"."EuroCurrency" ,
"Paint"."Sales Facts"."JapCurrency" ,
"Paint"."Sales Facts"."USDCurrency" )

  3. If you want to provide a dynamic list of currency options, create a table in your data source that provides the entries you want to display for the user-preferred currency. This table must include the following columns:

    • The first column contains the values used to set the session variable PREFERRED_CURRENCY. Each value in this column is a string that uniquely identifies the currency (for example, gc2).

    • The second column contains currency tags from the file currencies.xml. The displayMessage values for each tag are used to populate the Currency box and currency prompts (for example, int:euro-1). The currencies.xml file is located in ORACLE_HOME\bifoundation\web\display.

    • You can optionally provide a third column that contains the values used to set the presentation variable currency.userPreference. Each value in this column is a string that identifies the currency (for example, Global Currency 2). If you omit this column, then the values for the displayMessage attributes for the corresponding currency tags in the currencies.xml file are used.

    Table 8-1 shows a sample table with user-preferred currency entries.

    Table 8-1 Sample Table for Dynamically Displaying the Preferred Currency

    UserPreference CurrencyTag UserPreferenceName

    char

    char

    char

    orgc1

    loc:en-BZ

    Org currency

    gc2

    int:euro-1

    Global currency 2

    lc1

    int:DEM

    Ledger currency

    gc1

    int:USD

    Global Currency 1

Additional configuration is required in Oracle BI Presentation Services to enable this feature. For full information about the Oracle BI Presentation Services configuration, see "Defining User-Preferred Currency Options" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

Setting Default Levels of Aggregation for Measure Columns

You need to specify aggregation rules for mapped logical columns that are measures. Aggregation should only be performed on measure columns, with the possible exception of the aggregation COUNT and COUNTDISTINCT. Measure columns should exist only in logical fact tables.

You can optionally select different aggregation rules for different dimensions that are associated with this logical column. For example, if someone queries the aggregate column along with one dimension, you may want to use one type of aggregation rule, whereas with another dimension, you may want to use a different aggregation rule.

When the default aggregation rule is Count Distinct, you can optionally specify an override aggregation expression for specific logical table sources. For example, you may want to specify override aggregation expressions when you are querying different aggregate table sources that already contain some level of aggregation. If you do not specify any override, then the default rule prevails.

You can choose the aggregation rule Evaluate_Aggr to enable queries to call custom functions in the data source. For information about this function and other aggregation rules, see Appendix C. See also "Defining Aggregation Rules for Essbase and Other Multidimensional Data Sources" for additional information about setting aggregation for multidimensional sources.

By default, data is considered sparse. However, on rare occasions you might have a logical table source with dense data. A logical table source is considered to have dense data if it has a row for every combination of its associated dimension levels. When setting up aggregate rules for a measure column, you can specify that data is dense only if all the logical table sources to which it is mapped are dense.

To specify a default aggregation rule for a measure column:

  1. In the Business Model and Mapping layer, double-click a logical column.

  2. In the Logical Column dialog, click the Aggregation tab.

  3. In the Aggregation tab, choose one of the following options:

    • For measures in which the additivity is the same in all dimensions (in other words, for fully-additive or non-additive measures), select one of the aggregate functions from the Default Aggregation Rule list.

      The function you select is always applied when a user or an application requests the column in a query, unless an override aggregation expression has been specified.

      When you select Count Distinct as the default aggregation rule, you can specify an override aggregation expression for specific logical table sources. Choose this option when you have more than one logical table source mapped to a logical column and you want to apply a different aggregation rule to each source.

      Click the Add button to select logical table sources for which you want to specify individual aggregation rules. In the Browse dialog, select the logical table source you want to add, and click OK. Then, in the Formula list for that logical table source, select the aggregation rule you want to use.

    • Select Based on dimensions if your measure has different additivity for different dimensions (in other words, for semi-additive measures). For example, select this option for inventory units that sum in all dimensions except time. See "Setting Up Dimension-Specific Aggregate Rules for Logical Columns" for more information about this feature.

      Click the Add button to select additional dimensions for which you want to specify aggregation rules. In the Browse dialog, select the dimension you want to add, and then click OK. Then, in the Formula list for that dimension, select the aggregation rule you want to use, or click the Expression Builder button to build the aggregation rule using Expression Builder.

      The Data is dense option appears when you select Based on dimensions. Select this option only if all the logical table sources to which this column is mapped are dense.

      Caution:

      Selecting Data is dense indicates that all sources to which this column is mapped have a row for every combination of dimension levels that they represent. Selecting this option when any table source that is used by this column does not contain dense data will return incorrect results.

  4. Click OK.

Setting Up Dimension-Specific Aggregate Rules for Logical Columns

The majority of measures have the same aggregation rule for each dimension. However, some measures can have different aggregation rules for different dimensions. For example, bank balances might be averaged over time but summed over the individual accounts. The Oracle BI Server lets you configure dimension-specific aggregation rules. You can specify one aggregation rule for a given dimension and specify other rules to apply to other dimensions.

You need to configure dimensions in the Business Model and Mapping layer to set up dimension-specific aggregation. For more information about setting up aggregate navigation, see Chapter 10.

To specify dimension-specific aggregation rules for a single logical column:

  1. In the Business Model and Mapping layer, double-click a logical column.

  2. In the Logical Column dialog, click the Aggregation tab.

  3. In the Aggregation tab, select Based on dimensions.

  4. In the Browse dialog, select a dimension over which you want to aggregate, and then click OK.

  5. In the Aggregation tab, from the Formula list, select a rule.

    After selecting rules for specified dimensions, set the aggregation rule for any remaining dimensions by using the dimension labeled Other.

  6. If you need to create more complex formulas, click the Expression Builder button to the right of the Formula column to open Expression Builder.

  7. If you have multiple dimensions, you can click Up or Down to change the order in which the dimension-specific rules are performed.

    When calculating the measure, aggregation rules are applied in the order (top to bottom) established in the dialog.

  8. Click OK.

To specify dimension-specific aggregation rules for multiple logical fact columns:

  1. In the Business Model and Mapping layer, select multiple logical fact columns.

  2. Right-click and select Set Aggregation.

    You must select more than one column to see the Set Aggregation menu item. Also note that Set Aggregation does not appear if one or more of the columns you select is a derived column.

  3. In the Aggregation dialog, select or clear All columns the same.

    This option is selected by default. When selected, you can set aggregation rules that apply to all selected columns. If you clear this option, you can set aggregation rules separately for each selected column.

  4. In the Aggregation tab, select Based on dimensions.

  5. In the Browse dialog, select a dimension over which you want to perform aggregation, and then click OK.

    After setting up the rule for a dimension, specify aggregation rules for any other dimensions in the entry labeled Other.

  6. Click the Expression Builder button to the right of the Formula column.

  7. In the Expression Builder - Aggregate dialog, from the Formula list, select the aggregation to perform over the dimension.

  8. To change the order in which the dimension-specific rules are performed, click Up or Down, and then click OK.

    When calculating the measure, aggregation rules are applied in the order (top to bottom) established in the dialog.

Defining Aggregation Rules for Essbase and Other Multidimensional Data Sources

This section describes best practices for defining aggregation rules for logical measures sourced from Essbase and other multidimensional data sources, like MSAS and SAP/BW.

By default, when you import Essbase and some other multidimensional cubes into the Physical layer, Oracle Business Intelligence cannot read the aggregation rules set within the data source. Because of this, the measures are imported automatically with the default aggregation rule of External Aggregation. Note that this rule is only available for multidimensional data sources.

External Aggregation means that the Oracle BI Server is not aware of the underlying aggregation rule for the specific measure and will not compute it internally. Instead, the Oracle BI Server will always ship the query to the underlying multidimensional data source for aggregation.

Because the underlying data sources are extremely efficient, pushing the aggregation rules down to the data source ensures that the Oracle BI Server returns the results without adding any additional overhead in processing. However, it is recommended that you update the aggregation rule for each measure in Oracle Business Intelligence with the corresponding aggregation rule defined in the data source. Doing so ensures that the Oracle BI Server can do additional computations when needed. There is no query performance impact, since the Oracle BI Server still pushes down optimized queries wherever possible.

Note:

If the Oracle BI Server needs to do additional aggregation for a particular query, and the aggregation rule is set to the default of External Aggregation, the server returns the following error:

An external aggregate is found in an outer query block.

This error occurs because the Oracle BI Server cannot read the aggregation rule in the underlying data source. To ensure that correct results are returned for these queries, you should change the aggregation rules set in the Oracle BI repository to match the aggregation rules set in the underlying data source.

You must ensure that the aggregation rule defined in Oracle Business Intelligence matches the rule in the underlying data source. Also, you must set the appropriate aggregation rule in both the Physical layer and Business Model and Mapping layer, as shown in Figure 8-1.

Figure 8-1 Setting Aggregation Rules in the Physical and Business Model and Mapping Layers for Multidimensional Sources

Description of Figure 8-1 follows
Description of "Figure 8-1 Setting Aggregation Rules in the Physical and Business Model and Mapping Layers for Multidimensional Sources"

For custom aggregations or aggregations which do not have a corresponding function within the Oracle BI Server, it is recommended to leave the aggregation as External Aggregation for both the physical measure column and its corresponding logical measure column.

Associating an Attribute with a Logical Level in Dimension Tables

Attributes can be associated with a logical level by selecting the dimensional level on the Levels tab. Measures can be associated with levels from multiple dimensions and always aggregate to the levels specified.

Dimensions appear in the Dimensions list. If this attribute is associated with a logical level, the level appears in the Levels list.

Another way to associate a measure with a level in a dimension is to expand the dimension tree in the Business Model and Mapping layer, and then use drag-and-drop to drop the column on the target level. For more information about level-based measures, see Example 9-1.

To associate a measure with a logical level in a dimension:

  1. In the Business Model and Mapping layer of the Administration Tool, double-click a logical column.

  2. In the Logical Column dialog, click the Levels tab.

  3. In the Levels tab, click the Logical Level field for the dimension from which you want to select a logical level.

    In the Levels tab, in the levels list, you can sort the rows (toggle between ascending order and descending order) by clicking a column heading.

  4. In the Logical Level list, select the level.

  5. Repeat this process to associate this measure with other logical levels in other dimensions.

To remove the association between a dimension and a measure:

  1. In the Business Model and Mapping layer of the Administration Tool, double-click a logical column.

  2. In the Logical Column dialog, click the Levels tab.

  3. In the Levels tab, select the row for the association you want to remove and click Delete.

  4. Click OK.

Moving or Copying Logical Columns

By default, dragging and dropping a logical column from one table to another moves the logical column. If a column with the same name already exists, the new column is renamed (for example, mycolumn#1).

You can also choose the option Prompt when moving logical columns in the Options dialog to cause the Sources for moved columns dialog to be displayed when you drag and drop a logical column. This dialog gives you options about the drag and drop behavior.

See "Setting Preferences" for more information about selecting the Prompt when moving logical columns option.

To move or copy logical columns using the Sources for moved columns dialog:

  1. In the Business Model and Mapping layer, drag and drop a logical column to a different logical table. You can select multiple columns to move.

  2. In the Sources for moved columns dialog, in the Action area, select an action.

  3. If you select Ignore, no logical source is added in the Sources folder of the destination table.

  4. If you select Create new, a copy of the logical source associated with the logical column is created in the Sources folder of the destination table.

  5. If you select Use existing, in the Use existing list, you must select a logical source from the Sources folder of the destination table.

    The column that you moved or copied is associated with this logical source.

Enabling Write Back On Columns

You can configure individual logical columns so that users in Oracle BI Presentation Services can update column data and write the changes back to the data source. To enable write back on a particular column, you must select the Writeable option for the logical column, and enable the Read/Write permission for the corresponding presentation column. You must also disable caching on the corresponding physical table.

Additional tasks to enable write back need to be performed in Oracle BI Presentation Services. See "Configuring for Write Back in Dashboards and Analyses" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for full information.

To enable write back for a particular column:

  1. In the Administration Tool, in the Physical layer, double-click the physical table that contains the column for which you want to enable write back.

  2. On the General tab of the Physical Table dialog, ensure that Cacheable is not selected. Deselecting this option ensures that Oracle BI Presentation Services users can see updates immediately.

  3. In the Business Model and Mapping layer, double-click the corresponding logical column. The Logical Column dialog opens.

    Figure 8-2 shows the Logical Column dialog.

    Figure 8-2 Logical Column Dialog with Writeable Option Selected

    Description of Figure 8-2 follows
    Description of "Figure 8-2 Logical Column Dialog with Writeable Option Selected"

  4. Select Writeable, then click OK.

  5. In the Presentation layer, double-click the column that corresponds to the logical column for which you enabled write back. The Presentation Column dialog opens.

  6. Click Permissions.

  7. Select the Read/Write permission for the appropriate users and application roles.

    Figure 8-3 shows the Permissions dialog.

    Figure 8-3 Permissions Dialog for Presentation Layer Column with Read/Write Option Selected

    Description of Figure 8-3 follows
    Description of "Figure 8-3 Permissions Dialog for Presentation Layer Column with Read/Write Option Selected"

  8. Click OK in the Permissions dialog.

  9. Click OK in the Presentation Column dialog.

Setting Up Display Folders in the Business Model and Mapping Layer

You can create display folders to organize objects in the Business Model and Mapping layer. They have no effect on query processing. After you create a display folder, the selected tables and dimensions appear in the folder as a shortcut and in the business model tree as the object. You can hide the objects so that you only view the shortcuts in the display folder. See the information about the Repository tab of the Options dialog in "Setting Preferences" for more information about hiding these objects.

Note:

Deleting a table in a display folder deletes only the shortcut to that object. When you delete a column in a display folder, however, the column is actually deleted.

To set up a logical display folder:

  1. In the Business Model and Mapping layer of the Administration Tool, right-click a business model and select New Object, then select Logical Display Folder.

  2. In the Logical Display Folder dialog, in the Tables tab, type a name for the folder.

  3. To add tables to the display folder, click Add. In the Browse dialog, select the fact or dimension tables you want to add to the folder and click Select.

    Alternatively, you can drag one or more logical tables to the display folder after you close the dialog.

  4. To add dimensions to the display folder, click the Dimensions tab and click Add. In the Browse dialog, select the dimensions that you want to add to the folder and click Select.

    Alternatively, you can drag one or more dimensions to the display folder after you close the dialog.

  5. Click OK.

Modeling Bridge Tables

A bridge table enables you to resolve many-to-many relationships between tables. For example, you might hold information about employees in an Employees table, and information about the jobs they do in a Jobs table. However, an organization's employees can have multiple jobs, and the same job can be performed by multiple employees. This situation would result in a many-to-many relationship between the Employees table and the Jobs table.

To resolve the many-to-many relationship, you can create a bridge table (or intermediate table) called Assignments. Each row in the Assignments table is unique, representing one employee doing one job. If an employee has several jobs, there are several rows in the Assignments table for that employee. If a job is done by several employees, there are several rows in the Assignments table for that job. The primary key of the Assignments table is a composite key, made up of a column containing the employee ID and a column containing the job ID.

By acting as a bridge table between the Job and Employee tables, the Assignments table enables you to resolve the many-to-many relationship between Employees and Jobs into:

  • A one-to-many relationship between Employees and Assignments

  • A one-to-many relationship between Assignments and Jobs

Figure 8-4 shows a Physical layer view of the example bridge and associated dimension tables described in the preceding paragraphs.

Figure 8-4 Example Bridge and Associated Tables in the Physical Layer

Description of Figure 8-4 follows
Description of "Figure 8-4 Example Bridge and Associated Tables in the Physical Layer"

Note that "Weight Factor" should be included as an additional column in the bridge table and calculated during ETL for efficient query processing.

The following sections explain how to model bridge tables in the Physical and Business Model and Mapping layers:

Creating Joins in the Physical Layer for Bridge and Associated Dimension Tables

To model bridge tables in the Physical layer, create joins between the bridge table and the associated dimension tables.

To create physical joins for a bridge table and its associated tables:

  1. In the Administration Tool, in the Physical layer, select the fact, bridge, and associated dimension tables. Then, right-click the objects and select Physical Diagram, and then choose Selected Object(s) Only.

  2. With the Physical Diagram displayed, click New Join on the toolbar. Then, select one of the dimension tables, and then select the bridge table.

  3. Click OK in the Physical Foreign Key dialog.

  4. Repeat steps 2 and 3 for the other associated dimension table.

  5. Ensure that one of the associated dimension tables is joined to the fact table.

    Figure 8-5 shows joins between the example Physical tables in the Physical Diagram.

    Figure 8-5 Joins Between the Example Tables in the Physical Diagram

    Description of Figure 8-5 follows
    Description of "Figure 8-5 Joins Between the Example Tables in the Physical Diagram"

Modeling the Associated Dimension Tables in a Single Dimension

In the Business Model and Mapping layer, you can choose to model the two dimension tables associated with a bridge table in a single dimension, or in two separate dimensions. To model the associated dimension tables in one dimension, create a second logical table source that maps to the bridge table and the other dimension table, and then add columns from the other dimension table.

Providing two separate logical table sources makes queries more efficient, because it ensures that queries against a single dimension table do not involve the bridge table.

To model the dimension tables associated with a bridge table in a single dimension:

  1. Drag objects from the Physical layer to the Business Model and Mapping layer, except the bridge table and the associated dimension table that is not joined to the fact table. For the example described in the previous sections, you would drag all objects except for the Assignment and Employee tables.

  2. In the Business Model and Mapping layer, right-click the dimension table that is joined to the fact table (Jobs in our example) and select New Object, then select Logical Table Source.

  3. In the Logical Table Source dialog, provide a name for the new bridge table source. It is a good practice to use the bridge table name as the name of the source (for example, Assignment).

  4. Click the Add button in the upper right corner of the Logical Table Source dialog. Then, select the bridge table from the Name list (Assignment in our example) and then click Select.

  5. Click the Add button again and select the associated dimension table that is not joined to the fact table (Employee in our example) and then click Select.

  6. Click OK in the Logical Table Source diaog.

    Figure 8-6 shows the Logical Table Source dialog for the bridge table source.

    Figure 8-6 Logical Table Source Dialog for Bridge Table Source

    Description of Figure 8-6 follows
    Description of "Figure 8-6 Logical Table Source Dialog for Bridge Table Source"

  7. Drag columns from the dimension table that is not joined to the fact table (Employees in our example) from the Physical layer to the logical table source that you just created.

You can now create dimensions based on your logical tables, including the logical table with the bridge table source.

Modeling the Associated Dimension Tables in Separate Dimensions

As an alternative to modeling the two dimension tables associated with a bridge table in a single dimension, you can choose to model them in separate dimensions. To do this, create a logical join between the fact table and the dimension table that is not physically joined to the fact table, and then modify the logical table source for that same dimension table to add the other table mappings.

To model the dimension tables associated with a bridge table in separate dimensions:

  1. Drag objects from the Physical layer to the Business Model and Mapping layer. Because you want to model the dimension tables in separate dimensions, drag both of the dimension tables associated with the bridge table. You do not need to drag and drop the bridge table object.

  2. In the Business Model and Mapping layer, select the fact table and the two dimension tables that are associated with the bridge table (Facts, Employee, and Jobs in our example). Then, right-click the objects and select Business Model Diagram, and then choose Selected Tables Only.

  3. With the Business Model Diagram displayed, click New Join on the toolbar. Then, select the dimension table not currently joined to the fact table, and then select the fact table.

  4. Click OK in the Logical Join dialog.

    Figure 8-7 shows joins between the example logical tables in the Business Model Diagram.

    Figure 8-7 Joins Between the Example Tables in the Business Model Diagram

    Description of Figure 8-7 follows
    Description of "Figure 8-7 Joins Between the Example Tables in the Business Model Diagram"

  5. Double-click the logical table source for the logical table for which you created the logical join (Employee in our example).

  6. Click the Add button in the upper right corner of the Logical Table Source dialog. Then, select the bridge table from the Name list (Assignment in our example) and then click Select.

  7. Click the Add button again and select the other associated dimension table (Jobs in our example) and then click Select.

  8. Click OK in the Logical Table Source diaog.

    Figure 8-8 shows the Logical Table Source dialog for the modified dimension table source.

    Figure 8-8 Logical Table Source Dialog for Dimension Table Source

    Description of Figure 8-8 follows
    Description of "Figure 8-8 Logical Table Source Dialog for Dimension Table Source"

You can now create dimensions based on your logical tables, including both logical tables associated with the bridge table.