SAC live access on top of SAP BW: Top N and "Others" (SAP BW Query approach)

[Adem_Baykal]

Introduction

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.

For more details about the blog series please follow this link: https://blogs.sap.com/2020/01/08/analyse-your-data-live-with-sap-analytics-cloud-on-sap-bw-on-hana-s...

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

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:

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.

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.

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. 😊

Cheers,

Adem