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:
|
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:
|
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:
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 |