Skip to content

BR Execution Parameter Details

This page gives a detailed description about the available BR Execution Parameters, i.e. parameters that are related to execution of IFS Business Reporter reports.

Use this page when you want to know about available BR Execution Parameters and how they can be used to affect the execution of the SQL statements on the Oracle server.

The parameter overview shows available parameters, the parameter type, associated Oracle session parameters and on what level they are applicable. To see the description of each parameter, go to Execution Parameter Description.

BR Execution Parameters

The BR Execution Parameter page is part of the tools related to configuration of IFS BI Services.

Attribute Description
Parameter Name The name of the execution parameter
Description The description of the execution parameter
Data Type The data type of the values to be entered in the column
Parameter Type Execution parameter type. Possible values are:
  • Oracle Session
    Oracle session parameters which are mentioned in the Session Parameter column are modified.
  • Oracle Hint
    Refers to special Oracle SQL statement hints.
  • BR
    IFS Business Reporter specific execution parameter.
Session Parameter Oracle session parameter(s) that will be assigned value(s) during the report execution. Valid only for the Oracle Session parameter type.
Global Level Value The global value of the parameter applicable for all executed IFS Business Reporter reports. It will be applied only if it is not overridden by a report or report set specific value.
Allowed on Global Level Indicates whether the parameter value can be set on the global level, read only.
Allowed on Report Level Indicates whether the parameter value can be overridden on the report level, read only.
Allowed on Data Set Level Indicates whether the parameter value can be overridden on the data set level, read only.

Execution Parameter Overview

Parameter Name Parameter
Type
Session Parameter(s) Configurable at
Global
Level
Report
Level
Data
Set
Level
--- --- ---
ADD_PARENT_CONDITIONS BR   YES
ADD_PARENT_CONDITIONS_DRILL_DOWN BR   YES
DIM_OL_JOIN_BY_DICT BR   YES
DIM_OL_JOIN_BY_DICT_L2 BR   YES
DISABLE_MERGE_JOIN_CARTESIAN Oracle Session _optimizer_mjc_enabled YES
ENABLE_TKPROF_OUTPUT Oracle Session timed_statistics, sql_trace YES
LOG_DURING_EXECUTION BR   YES
OPTIMIZER_INDEX_COST_ADJ Oracle Session optimizer_index_cost_adj YES
ORDERED_HINT_VALUE BR + Oracle Hint   NO
SELECT_STMT_WITH_BIND BR   YES
SELECT_STMT_WITH_BIND_DRILL_DOWN BR   YES
SKIP_OUTER_DIM_JOIN_IF_COND BR   YES
SKIP_OUTER_FACT_DIM_IF_ADDON_COND BR   YES
SKIP_OUTER_JOIN_FOR_LOV_REQ BR   YES

Execution Parameter Description

Parameter Name Description
ADD_PARENT_CONDITIONS Specifies if additional performance conditions should be built or not. By default this parameter is set to TRUE.
Modification of this parameter value to FALSE might be necessary in the following situations:

  • The performance is bad for a specific data set or even for all data sets in a report. Analysis gives at hand that it would be better to skip adding automatic parent conditions.
  • If an unexpected error occurs that is believed to have something to do with the adding parent condition mechanism.
ADD_PARENT_CONDITIONS_DRILL_DOWN Specifies if additional performance conditions should be built or not when performing Drill Down in IFS Business Reporter. By default this parameter is set to TRUE.
Modification of this parameter value to FALSE might be necessary if the performance for Drill Down is experienced as bad.
This parameter only applies on global level, i.e. for all Drill Down execution regardless of report.



Note: Before changing this parameter it is better to first try to reduce the number of involved sources in the Drill Down statement. This is done in design mode in IFS Business Reported by disabling as many of the dimension attributes as possible. E.g. if for the Drill Down detail related to GL Balances it is necessary to see the account, code B, year period and some measures and light items, other code parts as code C to code J can be disabled as detail attributes.

DIM_OL_JOIN_BY_DICT Specifies if dictionary based join should be performed between dimension and add-on dimension for the Online data access type. Default value is TRUE.
The join is based on parent and key attributes for the involved Online views. To make the join work properly it is important that view comments for the views are matching and that the dictionary is up-to-date.
The value is typically set to FALSE if built statements shows incorrect join conditions between a main dimension and an add-on dimension. Then the ID based join will be used instead.
 
DIM_OL_JOIN_BY_DICT_L2 Specifies if dictionary based join should be performed between dimension and add-on dimension for the Online data access type even if keys mismatch. Default value is TRUE.
Same prerequisites as defined for parameter DIM_OL_JOIN_BY_DICT.
There can be cases where the main dimension has two keys and a valid add-on dimension has only one key. If the first key in the main dimension matches the key in the add-on dimension, then a valid join can still be built.
The value is typically set to FALSE if built statements shows incorrect join conditions between a main dimension and an add-on dimension. Then the ID based join will be used instead.
DISABLE_MERGE_JOIN_CARTESIAN Specifies if Merge Join Cartesian should be disabled, e.g. the Oracle optimizer should not perform this type of join.

Intended behavior is achieved by modifying the hidden session variable _optimizer_mjc_enabled.


Note: Oracle strongly recommends not modifying hidden parameters. This parameter should be set as a last way out. The explain plan may be modified via hints, changing the reports design, checking status of gathered statistics, adding indexes etc. Please investigate these options before setting this parameter.
If a statement still, after other measures taken, leads to an execution plan containing Merge Join Cartesian, consider changing this parameter. After changing the parameter, please check the generated statement to find out the new execution plan.




Note: The Oracle session parameters associated with this parameter (currently only _optimizer_mjc_enabled ), will only be set if the Oracle view FND_ORA_PARAMETER, owned by the SYS user is granted to the Application Owner.

ENABLE_TKPROF_OUTPUT This parameter will enable TKPROF output for the Oracle session executing the report. The trace file can be accessed from the directory specified in the Oracle system parameter USER_DUMP_DEST
LOG_DURING_EXECUTION Specifies if server actions, typically triggered by IFS Business Reporter or BI access via IFS Lobby, should be logged or not. This functionality is very useful for debugging purposes to find out the time consumed in different processing steps etc. By default the value is set to FALSE. To enable logging set the value to TRUE. The page Information Sources - Runtime Log in IFS client can be used to examine generated log information.
OPTIMIZER_INDEX_COST_ADJ Uses the optimizer_index_cost_adj session parameter to modify Oracle optimizer behavior for access path selection to be more or less index friendly.
ORDERED_HINT_VALUE Specifies the order of database views in the FROM clause of a SQL statements by simply defining one or more view names as the parameter value.
View names are separated by a comma.
Specified views will appear in the FROM clause in the order specified via this parameter value and the ordered hint will be added to the SQL statement.
The ordered hint is defined according to /*+ ORDERED */
Read more on the General Tuning Advice page.
SELECT_STMT_WITH_BIND Specifies whether bind variables are used when creating online SELECT statements.
Default value is TRUE.
Changing the parameter value to FALSE can improve the performance in some cases. But this could lead to security issues, since using bind variables is the only way to protect from SQL injection via a parameter value.
 
SELECT_STMT_WITH_BIND_DRILL_DOWN Specifies whether bind variables are used when creating SELECT statements related to Drill Down (generated from IFS Business Reporter )
Default value is TRUE.
Changing the parameter value to FALSE can improve the performance in some cases. But this could lead to security issues, since using bind variables is the only way to protect from SQL injection via a parameter value. This parameter only applies on global level, i.e. for all Drill Down execution regardless of report.


Note: Before changing this parameter it is better to first try to reduce the number of involved sources in the Drill Down statement. This is done in design mode in IFS Business Reporter by disabling as many of the dimension attributes as possible. E.g. if for the Drill Down detail related to GL Balances it is necessary to see the account, code B, year period and some measures and light items, other code parts as code C to code J can be disabled as detail attributes.
SKIP_OUTER_DIM_JOIN_IF_COND Specifies if outer join should be skipped between:
  1. Fact and dimension if a condition has been specified for the dimension.
  2. Dimension and add-on dimension if a condition has been specified for the add-on dimension.

If for a join (fact->dim or dim->add-on dim) that is supposed to be built as an outer join, a condition is defined on an item in the dimension or in the add-on dimension, it is for most operators possible to skip the outer join. Changing to ordinary (inner) join will improve performance.
There is one exception and that is if the operator IS NULL or IS NOT NULL is used. For this case the outer join will be used regardless of parameter value.
Default value is TRUE.
Only change to FALSE if it is expected that the statements are not correctly built.
SKIP_OUTER_FACT_DIM_IF_ADDON_COND Specifies if outer join between a fact and a dimension should be skipped if there is a condition on an item in an add-on dimension related to the actual dimension.
Even if the join between the fact and the dimension is supposed to be built as an outer join, defining a condition on an item in an add-on dimension related to the actual dimension for most operators means that the outer join can be skipped. It is not necessary to have a condition on the dimension. The add-on dimension condition will be enough to remove the outer join condition between the fact and the dimension and using an ordinary join will improve performance.
There is one exception and that is if the condition IS NULL or IS NOT NULL is used. For this case the outer join will be used regardless of parameter value.
Default value is TRUE
Only change to FALSE if it is expected that the statements are not correctly built.
 
SKIP_OUTER_JOIN_FOR_LOV_REQ Specifies if outer join between a dimension and a related add-on dimension should be skipped when building a List of values request.
Default value is TRUE.
If a List of Values query has to be built as a combination of items from a dimension and a related add-on dimension, then normally this should be done as an exact (inner) join to get values from matching records. If however the List of values does not show all expected values, then set the parameter value to FALSE and test again.
Performance is improved by using an exact join instead of an outer join