When working with BW system, occasionally we will run into some query result looks very strange. Sometimes it is a problem, which need to be fixed by applying notes. But in many cases, it is not that case. What we need to do is to look closer to these numbers and see what exactly happens in OLAP engine. The misunderstanding of OLAP feature can also lead to unexpected query result.
Recently I happened to see such an example. Let's look at the following query:
Here, the query result is quite obvious. For this specific infoprovider, we only have quantity for component F07 with color 3 in country C02, which is 760 PC.
Now the end user performs further navigations, he sets a filter of F07 on component as following: right click on component and choose 'Fix filter value on Axis'
With this action, we actually put a dynamic filter of component=F07 over the query result. And we expect, the query result should be the same.
But surprisingly, we see:
We get additional quantity for color 2! What is this -10 PC? Where does it come from? How could a simple navigation step change the query result totally different? It must be something wrong.
But before we exclaim it is definitely a bug with a firm tone, let's take a closer look to this query. Is there some special setting on the query? The most suspectable is 'Conditions and Exceptions', with conditions active, the query result will get extra filter which will affect what we finally receive at the end.
For this query, we can see, it does have a condtion active as following:
What is defined in this conditon? Let's look into it further:
Here we can see, this is a conditon based on combination of characteristic 'color' and 'country', the condition is key figure FABC_STZ must not equal to 0. This means this condition will only applied when both color and country are drilled down side by side, the value FABC_STZ=0 will be hidden from the result.
So, to understand what exactly happened, we need to see the real query result without condition active. Let's deactivate this 'condition 4', we will see:
So actually, we do have data in this infoprovider for color 2 and country C02. This full section is suppressed because the aggregated quantity for color 2 and country C02 is '0'.
This is exactly what condition is designed to do, see more details at online help: 10 Rules for Conditions
For this specific case, it is exacly what Rule 6 explained. You can also check the example provided in Condition for Characteristic Combination
When we add an additional dynamic filter of component=F07, the query result without condition is:
In this case, as we can see the aggregation quantity for color 2 and country C02 is not 0, thus F07 with -10PC is shown in query result even the condition is active.
So, after some analysis, we can see although the result may not be exactly what you expect to see, nevertheless, it is correct as per design logic.
Such situation is very typical when we read queries with conditons active. To better understand OLAP logic regarding to conditons, a more convinient tools is delivered in BW 7.x (7.0 as of SPS15): transaction RSFC.
Inside of RSFC, quite a lot of demo queries are listed there to show you how OLAP engine is designed to work. You can find queries to demonstrate different condition scenarios at folder Analytic Engine -> Conditon. By double click these queries, you can directly run them on BW system and play arround to see the effects.
For more details about how to use RSFC, please read more in Note 1508237, Note 1133936.
