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:
Use this procedure to create logical columns in the Business Model and Mapping layer.
The name of the business model and the associated logical table appear in the Belongs to Table field.
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.
You can make some changes in this dialog. If you make changes, click OK to accept the changes instead of Cancel.
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.
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.
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
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.
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.
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.
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..
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" )
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
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.
Global currency 2
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.
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
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
In the Logical Column dialog, click the Levels tab.
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.
In the Logical Level list, select the level.
Repeat this process to associate this measure with other logical levels in other dimensions.
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.