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

Use this procedure to create logical columns in the Business Model and Mapping layer.

  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.
  5. 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 order.

Change the sort order of a column when you do not want to order the values alphabetically (lexical order).

In a lexical order 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 return February, January, March in their lexicographical sort order. You might want sort months in chronological order. Your table needs to have a month key such as MONTH_KEYwith values of 1 (January), 2 (February), 3 (March) to achieve the chronological sort order. You set the Sort order column field for the MONTH_NAME column to the MONTH_KEY and then, a request to order by MONTH_NAME would return January, February, and March.

The sort column is automatically defined for Essbase data sources when business models are created by dragging and dropping cubes from the Physical layer.

  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.

Enabling Double Column Support by Assigning a Descriptor ID 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 to a logical column enables double column support. You can use double column support to defining language-independent filters. For example, in Answers, users see the display column, but the query filters on the hidden descriptor ID column.

See Supporting Multilingual Data.

Double column support provides a mechanism for associating two columns. One column provides the display and description values such as the description of an item. The second column provides a descriptor ID or code column. For example, you can use the actual column to provide the project list, and hide the ID column associated with the first column, as in Clinic and Clinic ID. Only the Clinic description is displayed to the user. Using the double column approach helps satisfy the uniqueness requirements of Essbase. In the Clinic example, you would add an association to a column that contains the clinic ID using the steps in the procedure.

  1. Open the repository in the Oracle BI Administration Tool.
  2. In the Business Model and Mapping layer, expand the business model, and expand the table contain the column to update.
  3. Right-click the column, select Query Related Objects, select Business Model and Mapping, and then select Logical Column.
  4. In the Logical Column(s) related to the selected column dialog, click the column to associate with the selected column, and click Edit.
  5. In the Logical Column dialog, next to the Descriptor ID column field, click Set
  6. In the Browse dialog, select a column to use as the Descriptor ID, and click OK.

Creating Derived Columns

Some columns are derived from other logical columns as a way to apply post-aggregation calculations to measures.

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.

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).

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

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.

  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.
  5. Click OK.

You can display data from multilingual database schemas by using Expression Builder to create a lookup function. See Supporting Multilingual Data in 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.

When you use session variables in an expression for Presentation Services, you must preface their names with NQ_SESSION. Edit any logical columns that display currency values to use the appropriate conversion factor using the PREFERRED_CURRENCY session variable.

See Creating Session Variables and Creating Initialization Blocks.

  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..
  2. In the Business Model and Mapping layer, double-click the appropriate logical column, 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. 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\bi\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.

    The following table shows a sample table with user-preferred currency entries.

    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 Presentation Services to enable this feature. For full information about the Oracle BI Presentation Services configuration, see Defining User-Preferred Currency Options in 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. See EVALUATE_AGGR in Oracle® Fusion Middleware Logical SQL Reference Guide for Oracle Business Intelligence Enterprise Edition and Defining Aggregation Rules for Multidimensional Data 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.

See Setting Up Dimension-Specific Aggregate Rules for Logical Columns.

  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, that is 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.

      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. Some measures can have different aggregation rules for different dimensions.

For example, bank could calculate account balances averages over a specific time, but calculated averages on individual accounts with a simple summation for a period. You can 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. See Managing Logical Table Sources (Mappings).

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

When calculating the measure, aggregation rules are applied in the order (top to bottom) established in the dialog. If you have multiple dimensions, use Up or Down to change the order in which the dimension-specific rules are performed.

  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.
  6. (Optional) If you need to create more complex formulas, click the Expression Builder button to open Expression Builder.
  7. Click OK.

Specifying Dimension-Specific Aggregation Rules for Multiple Logical Columns

You can specify aggregation rules for multiple logical fact columns using the steps in this task.

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

Select a minimum of two columns to enable the Set Aggregation menu item. Set Aggregation is not enables if one or more of the columns are derived columns.

  1. In the Business Model and Mapping layer, select multiple logical fact columns.
  2. Right-click and select Set Aggregation.
  3. In the Aggregation dialog, select All columns the same or select Clear and select specific columns.
  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.
  6. After setting up the rule for a dimension, specify aggregation rules for any other dimensions in the entry labeled Other.
  7. Click the Expression Builder button to the right of the Formula column.
  8. In the Expression Builder - Aggregate dialog, from the Formula list, select the aggregation to perform over the dimension.
  9. To change the order in which the dimension-specific rules are performed, click Up or Down, and then click OK.

Defining Aggregation Rules for Multidimensional Data Sources

Learn the best practices for defining aggregation rules for logical measures sourced from Essbase, Oracle OLAP, and other multidimensional data sources, like Microsoft Analysis Services and SAP/BW.

When you import Essbase and some other multidimensional cubes into the Physical layer, Oracle BI Server cannot read the aggregation rules set within the data source. As a result of the default behavior, the measures are imported automatically with the default aggregation rule of External Aggregation.

External Aggregation means that the Oracle BI Server:

  • is not aware of the underlying aggregation rule for the specific measure.

  • cannot compute the measure.

  • always ships 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. Oracle recommends updating the aggregation rule for each measure in Oracle Business Intelligence, both in the Physical layer and Business Model and Mapping layer, with the corresponding aggregation rule defined in the data source. Updating the aggregation rule for each measure 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 the following image.

Description of GUID-4A924909-4515-4144-87FD-40AD82E9BE0F-default.gif follows
Description of the illustration GUID-4A924909-4515-4144-87FD-40AD82E9BE0F-default.gif

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.

Note:

For Oracle OLAP data sources, you do not explicitly set Physical layer aggregation rules for Oracle OLAP columns. Because of this, you only need to set the aggregation rule for Oracle OLAP columns in the Business Model and Mapping layer.

In addition, if a query requests an aggregate that does not exist in the Oracle OLAP data source, and the aggregation rule is set to External Aggregation, then the Oracle BI Server returns an error. To avoid this error, make sure to explicitly set the aggregation rule for the Oracle OLAP column in the Business Model and Mapping layer.

If you do not explicitly set the aggregation rule for Oracle OLAP columns to something other than External Aggregation, requests from Oracle BI Presentation Services custom groups fail, because custom groups always request aggregates that do not exist in the data source.

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.

You can associate measures with levels from multiple dimensions and aggregate to the levels specified. A measure is associated to a level is called a level-based measure. A level-based measure is computed at that grain, even when the query context has a lower grain. For example, if yearlySales is associated to year level, it is computed at the yearly level in the following query: Select month, yearlySales.

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. See Level-Based Measure Calculations.

  1. In the Business Model and Mapping layer of the Administration Tool, double-click a logical column to associate a measure with a logical level in a dimension.

  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.

  1. In the Business Model and Mapping layer of the Administration Tool, double-click a logical column to remove the association between a dimension and a measure.
  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

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 Administration Tool Options to read about the Prompt when moving logical columns option.

After completing this procedure, the column that you move or copy is associated with the logical source.

  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.