SAP BW Query Filter Dialog Box
This document covers possible solutions to various scernarios for issues in Filter Dialog Box (F4 for Help) and its performance optimization techniques.
This document is divided into two sections.
1. Selections For Filter Dialog Box (F4 for Help) – How to fix the issue if older /incorrect values appearing in the Filter Dialog Box (F4 for Help)
2. Performance Optimization BW Query – Filter Dialog Box (F4 for Help) – Focus on how to improve the perofrmance of filter dialog box in queries with various settings changes in Info Objects, Info Provider (for Navigational Attributes only) and Web Template levels.
1. Selections For Filter Dialog Box (F4 for Help)
Business Scenario: In some BW query wrong values in Filter Dialog Box (F4 for Help)are maintained.
The maintained values for ‘Time interval’ in transaction SPRO are not taken into account in F4 help for time characteristics.
Example: In this example Variable [Fiscal Period] is of type customer exit to get the latest fiscal period value. This variable is getting the right result but in the selections window the required fiscal period value does not exist (Assuming the current fiscal period is Sep 2001 -003/2012); it shows up all the old values only till Jun 2010.
When tried to search for the current fiscal period 003.2012 in the selections window found no results
Please note in this scenario the fiscal year starts in July and ends in June. Also this issue is only limited to the selections window and is not impacting the final results of the report.
The time interval maintained in SPRO under:
> Business Information Warehouse
> General Reporting Settings
> Set F4 Help and Hierarchies for Time Characteristics/OLAP Settings or in transaction RSRHIERARCHYVIRT is taken into account only when the F4 mode “Values in Master Data Table” is choosen. In F4 help for the characteristic values the leaves of the hierarchy or all values are then in the time interval set.
If the interval is later changed, all aggregates that contain a hierarchy to time characteristics are deactivated.
Report Output – Output of the report after making the changes in SPRO
*Reference: SAP Note 804113 – SPRO setting: ‘Time interval’ not taken into account. Refer this not for detailed level settings with explanation
2. Performance Optimization BW Query – Filter Dialog Box (F4 for Help)
Business Scenario: In some BW queries the ‘select filter value’ dialog box in variable entry screen either takes lot of time to display values or show blank screen (Screenshot 1).
Screenshot 1: Filter Dialog Box (F4 for Help ) for variable show blank screen or takes a lot of time to display the values
You can use either of these below mentioned settings to improve the performance of Filter Dialog Box (F4 for Help)
a. Re-indexing Info Objects
b. Setting for Info Objects and Info Provider levels (for Navigational Attributes only)
c. Web Template properties
a. Re-indexing Info Objects
Re-indexing the P table of the Info objects speeds the time taken to display values for filter dialog box in queries. Index rebuilding will reduce the average fragementation in percent.
BASIS-DBA team can rebuild the index upon request provided the table name
b. Setting for Info Objects and Info Provider levels
There are different level settings that can affect F4 behavior in BW query. Settings for Info Objects, Info Provider (for Navigational Attributes only) and Web Template levels to improve the perofrmance of filter dialog box in queries
The F4 mode for a characteristic in the variable screen can be set on following levels:
· Info Object properties
· Info provider specific properties (for Navigational Attributes only)
Level 2 setting will supersede level 1 setting. If Level 2 is not set, the system will use level 1 setting.
Info Object properties – You can set F4 mode in ‘Query Execution Filter Val. Selectn’ in tab ‘Business Explorer’ as follows:
Info provider specific properties
In RSA1, edit the corresponding Info Provider, right click on the characteristic and set F4 mode in info provider specific property depending on the requirement
For Navigational Attributes the performance can be optimized in the following way
F4 help for navigational attributes is handled differently from normal characteristics.
The possible F4 modes for navigational attribute A__B (characteristic B is the navigational attribute of characteristic A) are: M mode, D mode, Q mode and a special internal A mode (which can’t set directly in all property setting interface).
M mode: means all value in master data table for characteristic B.
A mode: means all available B values in master data table for characteristic A.
D mode: means all available B values posted in info provider via characteristic A. (Technically this means the join of X-table of characteristic A with D-table of the info provider).
Q mode: means available B values in query result set for characteristic A. This mode is only possible in ‘Select Filter Value’ in query result.
For the F4 help for a navigational attribute A__B, in web queries the system will use A mode to read F4 values by default. This means, that only characteristic B values available in characteristic A master data table will be displayed. This will perform a join between A master data table with B. In case A and B have very big master data tables, the F4 performance may degrade. To avoid such a join, please set the M mode for characteristic B in RSD1.
*Reference: SAP Note# 1565809 – Settings for BW query FQ for help. Refer this not for detailed level settings with explanation
c. Web Template properties – Setting the BOOKED_VALUES = Q or M in the template HTML code will optimize the Filter Dialog Box (F4 for Help) performance (Screenshot 2).