Thresholds in SAP Analytics Cloud are used to measure performance and indicate when an action may need to be taken. They also provide context when sharing reports. Using this information, you can dive deeper into your data to determine potential causes and solutions of high and low performing areas of your business.
You can define threshold values (also known as exceptions in the BW world) for a query in the BEx Query Designer, BW Modeling Tools, as well is SAP Analytics Cloud. However only Thresholds created in SAC are evaluated, the ones defined on the BW side are turned off as it is mentioned in the SAC BW Live Connectivity Support Matrix.
|Threshold defined in SAC||BW Exception modeled in the BW Modeling Tools|
- Exceptions defined in the BW Modeling tools are available in the BW INA Metadata Response, however those are set to Active: false (Analytics.Definition.Exceptions.Active) in the ResultSet Request.
- The scaling factor is ignored during the evaluation of exceptions.
- The exception shall always apply to the value visible in the UI. This is because of the following example: the user defines an exception keyfigure <= 100 the decimal places are 2, so the value on the UI is 100,00 the actual value is 100,0003 or similar. If the exception is evaluated on the maximum precision only, this value would not be marked by the exception this behavior is not understandable by the user.
The array of Exceptions is a child of the Definition object (Analytics.Definition.Exceptions). The technical name is generated on the fly (Analytics.Definition.Exceptions.Name).
In general, the Exception is defined on a data cell, i.e. the crossing point of a measure coordinate and (in case of a 2-structure query) a member of the second structure. The coordinates of the crossing point are contained in the StructureContext node. (Analytics.Definition.Exceptions[i].StructureContext). StructureContext contains a member of the keyfigure structure "KT Key Figure structure" (UID 4VGQIK77R2ZT6FS7SRFV85N3T) and in the case of a 2-structure query, a member of the second structure. Accordingly, the element contains the fieldnames MemberName and DimensionName.
EvaluateDefault: "All" (Analytics.Definition.Exceptions.EvaluateDefault) means, that if the threshold(s) of an Exception object shall be evaluated on values of Keyfigure A, but the Exception shall change the appearance (alert level) of all Datacells of the structure, a boolean field Exceptions[i].StructureContext[j].EvaluateDefault is set to true.
EvaluateOn (Analytics.Definition.Exceptions.EvaluateOn) node contains the list of members (both Measure structure selections and Dimension members), which defines the Cell restrictions, where the Exception can be applied on. In our case, it contains the Measure structure selection "GA_SAL"' (UID 4VGQIK77R2ZT6FS7SRFV8662H ) from Measure structure "KT Key Figure structure" (UID 4VGQIK77R2ZT6FS7SRFV85N3T), on the top of the dimension members which are defined as a Filter for the the Threshold in SAC. Filters of Thresholds in SAC stands for Cell Restriction in the BW Modeling Tools.
Threshold node (Analytics.Definition.Exceptions.Threshold) contains the list of threshold values, intervals that we would like to highlight. By having a closer look, one can see additional "helper" threshold(s) that are just indirectly defined by the user. For example as it can be seen in the table below in our case as well.
- Operator BETWEEN means that all the values between High and Low, as well as the High and Low value itself
- Operator BETWEEN_EXCLUDING means everything between High and Low, besides the High and Low value itself
In order to avoid some potential processing problems, it is needed to make sure that the threshold values are not overlapped. So as an example 3256.48 is included in the OK threshold >= 3256.48 ; <=8516.37, therefore BETWEEN_EXCLUDING used as an operator for WARNING >= 0 ; < 3256.48 threshold to make sure that 3256.48 is not included here as well. At the same time this puts 0 outside of the WARNING threshold too, hence we have the "helper" threshold BETWEEN High: 0, Low: 0. Same stands for BETWEEN High: -9000, Low: -9000, that is needed because of BETWEEN_EXCLUDING High: 0, Low: -9000.
BW cannot handle operator BETWEEN_EXCLUDING, so it is converted to BETWEEN. In order to make sure that result is the same as it would with BETWEEN_EXCLUDING, High and Low values are set to high_exclude and low_exclude. In our example we do not have booked data that is exactly 0, or -9000, so the "helper thresholds" are not needed to be applied.
AlertLevel (Analytics.Definition.Exceptions.Threshold.AlertLevel) is always 1, regardless whether the user sets OK, WARNING, or Critical, the coloring is assigned by the client, once the Exceptions are set by the server to the data cell(s).
|Threshold(s) defined in SAC||Threshold(s) in the ResultSet Request||Threshold(s) converted in BW|
OK >= 3256.48 ; <=8516.37
|BETWEEN High: 8516.37, Low: 3256.48|
BETWEEN High: 0, Low: 0
BETWEEN_EXCLUDING High: 3256.48, Low: 0
BETWEEN High: -9000, Low: -9000
BETWEEN_EXCLUDING High: 0, Low: -9000
BETWEEN High: 8516.37, Low: 3256.48
The Exceptions are applied on the data cell(s) during the ResultSet rendering. The Exceptions that are applied on the data cells, can be found in the ResultSet response in node ExceptionName (Grids.Cells.ExceptionName). It is possible to find out out that which Exception applied on which value, by comparing the values array of ExceptionName (ExceptionName(s) can be looked up from the ResultSet request Analytics.Definition.Exceptions.Name) to for example the values array of ValuesFormatted node (Grids.Cells.ValuesFormatted).
As it has been described, AlertLevel (Grids.Cells.ExceptionAlertLevel) is always 1, it is assigned to actual colors on the client side after the ResultSet response.
It is very important to follow Note 2541557-SAP Analytics Cloud with BW live connection below NW BW 7.50 SP16, BW/4 2.0 SP4 or S/4 1909 and run the attached report to implement the notes in the sequence from top to down. It is needed to make sure that all the NOTES are implemented and also the manual implementation steps are performed. This is the safest way to implement also the NOTEs stated below.
- 2927570-BICS:Query Exceptions using Cell restrictions with Variables dump
- 2830740-Dynamic BW exceptions, initial key selection in the chracteristic context
- 2810261-Performance: exceptions on two structure queries
- 2706089-INA: Exceptions on Dynamic Restriction is not evaluated
- 2601196-INA: Protocol change for Exceptions (2)
- 2589566-RSBOLAP: Exceptions for 2 Strucure-Queries with EvaluatedElement
- 2585314-INA: empty axis and protocol change for Exceptions