Introduction
In this blog I have a special topic where I thought it would make sense to share it with the community. There is often a requirement to report something like “Top N” and “Others”.
There are different possibilities to solve this topic (e.g. in the BW backend, within the SAC Analytics Designer with scripting). I want to give you an example what can be done using SAP BW Query features only and to stay in SAC stories.
The aim is to get something like this:
Or to be more precise:
Let’s do some work in the SAP BW Query
A short note from my side. You should be familiar with the process how to create & edit SAP BW queries. I will not guide you through the full process but those who has the relevant skills, can easily follow these steps without having the full details. If you read the blog you might think that this is a lot of work. I can assure you it is not. I wrote down a few more details to give you also some background information and to show you some of the activities step-by-step.
The Top 5 – Let the condition do the job in the SAP BW Query
The easiest way in a SAP BW query is to use the condition feature.
To achieve more effective data analysis, you can formulate conditions in a SAP BW Query. What does this mean? In the results area of the query, the data is filtered according to the conditions so that only the part of the results area that you are interested in is displayed. You can restrict the view to the data of a query; with regards to defined threshold values or based on a defined ranked list.
So a condition is nothing more than an object, that restricts the view of the data in a query by hiding numbers that do not meet the defined condition(s). By applying conditions to a query, you do not change any figures. You simply hide figures that are not relevant for you. Conditions therefore have no effect on the values displayed in the result rows. The results row of a query with an active condition corresponds to the results row of the query without this condition. You can define multiple conditions for a query. Conditions are evaluated independently of each other. The result set is therefore independent of the evaluation sequence. The result is the intersection of the individual conditions. Multiple conditions are linked logically with AND. A characteristic value is only displayed if it meets all (active) conditions in the query.
Using conditions, you can restrict how you view query data as follows:
- Threshold Values: An entry is filtered independently of the other entries if its reference value has a specific relationship to a comparison value. An entry is not displayed for example if its reference value exceeds or is less than a specific threshold value.
- Ranked List: All entries for the displayed list or all entries for a logical section of the list (with multiple characteristics in the drilldown) are considered. Their relationship to each another determines whether the entry is displayed. For ranked lists, the sorting is switched on automatically when the condition is activated.
What we need is in the ranked list part. Here you can choose from the following operators for ranked list conditions:
- Top N
- Bottom N
- Top %
- Bottom %
- Top SUM
- Bottom SUM
We will use the Top N condition. Of course you can rank within SAC as well, but currently there is no possibility to calculate a rank with others based on a live connectivity. To achieve this, I will make use of a specific BW query feature which we will see soon.
First what you need to do is to create a condition in your BW query to get the top 5 products for a measure (e.g. for the quantities like in this case). This can be done in the “Conditions” tab on the SAP BW Query Design part.
Then you need to define the details. We need a “Top 5” for the “Quantities” so let’s select it.
And maintain the details. Of course the SAP BW Query experts know that we can use a variable for “N” (orange arrow) so you can decide it it should be a Top 5, Top 10 or Top 20, but we don’t need that in this case. Let’s focus on the top 5 products by quantity.
Now if we would build a SAC live model based on top of this SAP BW Query (so the sender) we will exactly get this – the top 5 products by quantity. But that is not what we want for the moment. But we can leave that view in our SAC story as we anyhow need to show the Top 5 products as well.
Until now nothing new for those who knows already how SAC deals with the SAP BW Queries in the live mode. So how we can get the “Others“? Can we get them without scripting or programming? Can we just get it from the SAP BW Query? Of course we can.
Pass details from one SAP BW Query to another one (Sender & Receiver approach)
In a nutshell I want to do the following now. I want to have a second SAP BW Query (let’s call it “result set query” or just “receiver“) which gets the top 5 from a specific SAP BW Query (let’s call it “pre-query” or simply “sender”).
Now we have the SAP BW Query 1 already (the one above with the top 5 condition) which is ready to send the “Top 5 products by Quantity” to any SAP BW Query you want and this will open the door to some interesting use cases.
Only an example: some customers wants to use the top 5 products/materials/whatever based on a specific measure (e.g. quantity) to show those preselected items with a different measure (e.g. a KPI with a specific rate). The following picture shows a simple example:
What we now need is to receive the Top 5 products in a second SAP BW Query. Here we can make use of variables. Variables are really powerful within SAP BW Queries and you can do a lot with them. In my blog series I have already shown a few examples. Anyhow just let me explain what variebles in SAP BW are.
A variable is a means of parameterizing a query or a query component, as a variable is filled with values only once the query is executed. You define a variable for an InfoObject. This variable is then available in all InfoProviders that use this InfoObject, and can be processed in many different ways.
Variables act as placeholders for characteristic values, hierarchies, hierarchy nodes, texts, or formula elements. The variable type indicates for which object you can use a variable.
Depending on the variable type, variables can be processed in various ways. The processing type of a variable determines how the variable is filled with a value at the runtime of the query (e.g. if you access it live within SAC or in Analysis Office).
When you create the variable, you use the processing type to specify how the variable is filled with a value at query runtime.
The system supports the following processing types:
- User Entry: Manual Entry/Default Value
- Replacement Path: Replacement path
- Customer Exit: Customer exit
- SAP Exit: SAP exit
- SAP HANA Exit: SAP HANA exit
- Authorization: Authorization
We need the processing type “Replacement Path”. You use the Replacement Path processing type to specify the value that automatically replaces the variable when you execute the query.
The Replacement Path processing type can be used with characteristic value variables, text variables, and formula variables.
So all we need is a variable in the SAP BW query based on the required characteristic (in this case it is the product) and the processing type should be “Replacement Path“:
Just create a new variable for your SAP BW Query:
Make sure that you select the following settings:
Now select “Query” and enter the technical name of your SAP BW Query which is the sender:
In the next step just drag & drop the required characteristic (dimension) into the global filter area of the SAP BW query and “restrict” it:
Now select your newly created variable and move it into your “Filter Definition” area of the SAP BW Query:
At the end it should look like this:
Important note: It is not required to have SAP BW Query 1 in your SAC story as model. Whenever you start the SAP BW Query 2, the SAP BW Query 1 will run in the back and will send the top 5 products to SAP BW Query 2.
Let’s calculate the sums for “Top 5 & Others”
You have now several options. We will use an easy example to get the following view:
Drop the measure “Quantity” into the columns of the SAP BW Query and double click on it or use “Edit” from the context menu.
Now write “All Products” into the description. Drag & drop “Quantity” measure and the “Product” dimension into the selection details.
This step is really IMPORTANT!
Dont leave the window and keep it open!
Right click on “Product” and select “Constant Selection“:
What is the effect? You will get the quantity value for all products.
Now repeat the same steps and call your selection “Top 5“.
But this time dont use the setting for the constant selection on the product dimension. The result will be, that you will get a sum for the Top 5 values which comes from the sender SAP BW Query.
In the last step we need a formula to create the “Others“.
Right click and select “New Formula“:
Now we will need a simple calculation for the others. Write “Others” into the description and create your formula. It is just “All Products” minus “Top 5”:
Confirm with “OK” and now we have the structure we need.
Optional step: If needed you can hide the “All Products” element or leave it. You can anyhow hide it in SAC as well.
Now let’s move to SAC
In SAC you can create your SAC live model based on your SAP BW “Receiver” Query by using the standard way and use it in your SAC story. You can now see the values for your Top 5 and the others in many chart types or tables:
As you have a keyfigure structure in your SAP BW Query, you cant use this with a pie chart. For that you need to use a different approach in your SAP BW Query.
Just build a characteristic structure instead of a key figure structure and it will work (dont forget the constant selection):
The result will look like this (don’t forget to select the “characteristic structure” you created in your SAP BW Query as color):
And now you can start to work with it by using different dimensions, trellis etc. And at the end you are fully live on SAP BW.
This is fully dynamic and live. The SAP BW Query 1 which delivers the Top 5 values needs to be created only once and can be reused in lots of cases. The “Top N & Others” case is only one of them.
If required the SAC story and the SAP BW Queries can be created with more flexibility but let’s keep it simple for the moment. I hope this helped our SAC fans out there to create their SAC stories with this kind of requirement.
