SAC live access on top of SAP BW: Top N and “Others” (SAP BW Query approach)
In my last blog series with 4 parts I shared my personal SAC highlights on top of SAP BW using the live access. You can see some of the greatest live features of SAC on top of SAP BW/4HANA and SAP BW on HANA. And that with no data replication, no data duplication, no data extraction – just the pure native live access. Since the last blog part a lot happened and I will summarize that as well in part 5 of the blog series soon.
Now what’s the topic of this blog?
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”.
You can find “Rank with others – view top- and bottom-N in comparison to other data points” in the SAP Roadmap Explorer. Until SAC provides this feature you can make use of the workaround presented in this blog.
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
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.
Pass details from one SAP BW Query to another one (Sender & Receiver approach)
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:
But as this is not the right blog to show you all this possibilities like the one mentioned above, let’s just continue with the “Top N & Others” case. 😊
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.
Just create a new variable for your SAP BW Query:
Now select “Query” and enter the technical name of your SAP BW Query which is the sender:
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:
Now write “All Products” into the description. Drag & drop “Quantity” measure and the “Product” dimension into the selection details.
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
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):
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.
And As I wrote. This is not a big thing to rebuild and you can just reuse what’s coming from SAP BW in the live access mode.
See you in the next blog. 😊
Excellent blog post! Thanks a lot for your effort!
thank you for the nicely presented and detailed blog.
I am also working with conditions in queries and noticed, that new conditions added to a query are only visible/selectable in a chart, when you remove and recreate the chart from a story. Any workarounds or experiences for that issue? I would have assumed, that the new conditions would be available as soon as they have been created in the query.
Sorry for the delay. I am still discussing this with SAC Product Management.
Maybe I need to come back to you to get more details as I am also looking into this on our internal environment.
Rank with others – view top- and bottom-N in comparison to other data points
Thank you for the excellent post. I have one question to the enhanced requirement to this blog. Business wants to see the of Title of Top N and rest of the titles in Others. Here, we can only see top N and Others. Could you let me know how will we be able to enhance this pls.