SAP Online Documentation
Restrictions relevant for a Data Cell
In general a query can have 2 structures and various infoobjects drilled down in the rows and columns. In addition its possible to use the cell editor (two structures necessary) to define formulas/selections for a certain cell. If we run a query and focus on a certain cell we need to know which filter values and restrictions are relevant for this data cell (and how are they evaluated):
If C = (Eo,Fo) is the cell of a query with two structures, in which Eo is a selection element of the first structure and Fo is a selection element of the second structure, then C is affected by:
(S1) The selections of Eo.
(S2) The selections of Fo.
(SF) The fixed filter, that is, all restrictions that are defined in the QueryDesigner under 'Characteristic Restrictions'
(SD) The dynamic filter, that is, restrictions in the navigation block ('select filter values...') and 'Default Values' defined in the Query Designer.
(SA) Characteristic values of drilldown characteristics, that is, characteristic values that are in the corresponding lead column or
In queries with only one structure, (S2) does not apply. In queries with exception cells (created with the cell editor), (S1) and (S2) are replaced with the selections (SC) of the appropriate cells.
All data records are aggregated in a cell that comply with selections (S1) to (SA) (intersection of (S1) to (SA)).
(S1) selection from structure1
(S2) selection from structure2
(SF) fixed filter
(SD) dynamic filter
(SA) restriction generated by the drilldown chars
(SC) cell editor
Global and Local Filters (FEMS)
We distinguish between 'global' filters (filters defined in the filter areas "Characteristic Restrictions" (SF) and "Default Values" (SD) - and 'local' filters from restricted key figures (S1 and S2).
Global filters are applied on all key figures (all structure elements/cells), the local filters are only relevant for the corresponding structure element. The intersection of these filter values are finally relevant for a certain cell (in addition to the restriction coming form the drilldown characteristic). The global and local filter values for a certain infoobject are connected with a logical 'AND' (see also the example of note 1591837). Hence, if e.g. there is a global filter 0COUNTRY=US and a local one with 0COUNTRY=GB, the corresponding restricted key figure will never display values.
If you define a Restricted Key Figure (RKF), filter values are in general connected with "AND" for various characteristics and with "OR" for the same characteristic. For more details please see notes 351827 and 1962321.
RKF1: KF(basis key figure) with 0country=US
RKF2: RKF1 with 0country=GB
RKF2 is equal to: KF with 0country=(US,GB) -> (US or GB)
Internally the OLAP Engine uses the field FEMS to distinguish between global and local filters. Since this technical concept plays a very important role in many areas(e.g. see blog BWonHana and the FEMS), we mention here the relations:
Concept of FEMS
- Global Filters: FEMS = 0
- Local Filters: FEMS > 0
A query has
- two global filters
- 0COUNTRY = US
- 0COST_CENTER = 100
- and 2 restricted key figures (local selections)
- key figure Amount restricted to 0CALYEAR 2012 - 2015 and 0PLANT = P10
- key figure Amount restricted to 0CALYEAR 2015
Internally the selections are stored the following way:
If a unrestricted key figure is added to this query, this internal table changes to
One consequence of this unrestricted FEMS 3 is that the DataManager only uses the FEMS 0 restrictions (global selections) when creating the corresponding statement(since logically all other local filters would not change the requested data set any more). This may lead to a bad performance for queries.
Hard(Fixed,Static) and Soft(Dynamic) Filters
In the BI 7.0 Query Designer, there are two areas under the tab 'Filter': "Characteristic Restrictions" and "Default Values". Filters in the "Characteristic Restrictions" area operate as so called hard(SF,fixed, static) filters. Filters in the "Default Values" area operate as so called soft (SD,dynamic) filters.
- a dynamic filter can be changed during query navigation
- a hard filter cannot be changed during query navigation
The dynamic and Hard filters are combined with a logical AND connection(intersection).
A simple and typical example would be the following:
hard filter: 0COUNTRY = (AT,DE,IT,GB,FR)
soft filter: 0COUNTRY= IT
When you run the query it is restricted to the value IT (default value the users normally want to see at the beginning of their analysis). If needed it is possible to change the filter (via 'select filter value in the navigation block') to one (or more) of the other values form the hard filter (e.g. from IT -> FR, or IT -> (DE,IT,GB). It is not possible to select a country out of the data set of the hard filter.
Variables used under "Characteristic Restrictions" or "Default Values" are called SPACE or STATE variables, respectivelly.
- Variable Refresh
- Dynamic Variables and Dynamic Filters
- Dynamic Filters(Default Values) and Hierarchies
Dynamic Restrictions during Query Navigation(using 'Selector')
After a query was executed, it is possible to define (dynamically) global restrictions for free characteristics and the characteristics in the drilldown. The corresponding Value help(F4) is internally often called 'Selector'. The various frontends use names like 'Filter', 'Select Filter Values...' or 'Filter by Members'(AO). Screenshot of the ABAP BICS mode in RSRT:
From a technical perspective the Selector is internally handled similar to the usage of filters in the dynamic filter section(default values). Hence, the technical behavior (like functional restrictions) is often equal.
Multiple Selections regarding same Characteristic
As discussed above, we need to distinguish between hard, dynamic, global and local filters. In the following we focus on global filters on a certain characteristic. When there are restrictions in the hard and dynamic filter section, then they are intersected(logically connected with AND), see simple example of chapter "Hard and Dynamic Filters". In case there are more selections for the same characteristic in either the hard or soft filter section, then they are linked by "OR"(see also note 351827).
Simple example where there are two variables assigned to a characteristic:
There is a Interval and a Single Value variable, both are Input-Ready. If e.g. the interval 1-10 and the single value 12 is selected, then the superset (1-10, 12) is used. The interval 1-10 and the single value 5 results in (1-10).
There is one important point you have to know. In case one variable is left empty, the system does not assume that there are no restrictions(default interpretation which would lead to the display of all posted data) but continues as though the condition does not exist. See also the long text of warning BRAIN 294 which is displayed when you check such a query in the query Designer. So, e.g. an empty interval plus a single variable value leads to single value filter.
Filter Types(Excludes,Node Restrictions,..)
When defining restrictions for a characteristic, you can choose between single characteristic values, value ranges or hierarchy nodes. Its also possible to do this with the help of variables (see OT-OLAP-VAR Variables).
See also: SAP Online help: Restricting Characteristics
Exclusion of values and nodes
- values/intervals can be excluded in both filter sections(hard and soft filters and selector)
- hierarchy nodes can only be excluded under Characteristic Restrictions (hard filter) and in local filters
- since the 'selector' works similar to the usage of default values(see above), it does not offer the possibility to exclude hierarchy nodes
See note 1968275 for further details to the topic Excluding Selections.
- 351827 Incorrect data with multiple selection?
- 1962321 Understanding results for nested selections on the same characteristic
- FEMS and BW on HANA
Tiny Link http://wiki.sdn.sap.com/wiki/x/A4BEEg