Analyse your data live with SAP Analytics Cloud on SAP BW on HANA & SAP BW/4HANA (Part 4)

Blog series content (Part 4)

Please note that this is “Part 4” of my blog series. I strongly recommend that you read “Part 1”, “Part 2” and “Part 3” first, before you continue with this blog, because they show some basics about SAP Analytics Cloud and the Live Connection to SAP BW itself. In addition, it shows some great features you should see and know (including many screenshots & videos).

Part 1: https://blogs.sap.com/2020/01/08/analyse-your-data-live-with-sap-analytics-cloud-on-sap-bw-on-hana-sap-bw-4hana/

Part 2: https://blogs.sap.com/2020/03/10/analyse-your-data-live-with-sap-analytics-cloud-on-sap-bw-on-hana-sap-bw-4hana-part-2/

Part 3: https://blogs.sap.com/2020/06/11/analyse-your-data-live-with-sap-analytics-cloud-on-sap-bw-on-hana-sap-bw-4hana-part-3/

Part 4: https://blogs.sap.com/2020/11/24/analyse-your-data-live-with-sap-analytics-cloud-on-sap-bw-on-hana-sap-bw-4hana-part-4/

Part 5: will be available soon.

Part 6: not started!

Note: I have included some videos in this blog series. You can run them directly in the blog and maximize them if needed. Please note as well, that there is no sound in the videos. The content speaks for itself ?

Now let’s start with “Part 4”

Agenda:

• Introduction
• Why SAP Analytics Cloud (SAC)?
• Variables, Variable Popup, Variants & Filtering
• Zero Suppression
• Another example with R: Create a table including sorting & search!
• Additional information related to the custom grouping feature
• Flexible date handling in SAP Analytics Cloud with BW Live Connection
• Using dynamic text to access the content from the SAP BW Query variables
• Support for tuple filters (Filter data that is organized in multiple dimensions)
• What about “Navigation Attributes” & “Display Attributes” from SAP BW?
• Working with SAC and the report-report interface (RRI) from SAP BW
• Create generic hyperlinks in SAC tables, to open external pages using parameters (+example with SAP S/4HANA)
• Tunnel Connection Type from SAC “live” to SAP BW

Introduction

SAP Analytics Cloud lays the foundation for fast, modular use, and can grow alongside your business. As a true cloud solution, SAP Analytics Cloud is convenient, secure, and scales to meet the needs of businesses of any size. It provides a lot of nice features to address all business needs.

The growing popularity among SAP customers, who use SAC to analyze their SAP data, has contributed to the fact that the live interface is constantly being expanded and enhanced with many new innovative features.

In the past months I’ve been involved in many customer activities. Often it was also about SAC and how it connects live with SAP systems (e.g. SAP BW/4HANA, SAP BW on HANA).

Some of the questions that customers often ask in this context are:

It was interesting to see that many questions can be answered by reading this blog series and therefore I decided to continue with it as new innovations come up for SAC related to the live connectivity and there are still some nice features you will explore, when working live with SAP BW.

Why SAP Analytics Cloud (SAC)?

Many new features have been released in the last months for SAC Analytics Cloud. At the same time the exclusive live connectivity to SAP’s own data sources and applications was also further optimized and new features have been added.

The often-requested PowerPoint export or publishing of stories based on SAP Live data has now also been delivered. I already presented both features in the last part of this blog series.
And now let’s look into some additional features I have not included in the other parts of this blog series.

Variables, Variable Popup, Variants & Filtering

In the first blog post you saw how to work with SAP BW Query Variants which has been saved in Analysis Office. You can create a user variant which is only visible to you, or you can create a global variant which is visible for all SAP BW users who opens this query. If you have a variable popup screen with many variables inside, it is a nice way to just bookmark your selected filter values easily.

It works plug & play and there is no need to change the model in SAC.

Another nice feature which end users like is the possibility to copy & paste filter values from their clipboard into the variable popup. Both story viewers and designers can now make changes to story and page filter selections faster, especially when working with a large list of values. You can now simply copy and paste filter values into the input control to either overwrite or append the existing selection when working with BW models.

If you have a SAC live model which is based on a BW query you can see that variables can be easily reused. And if you save the combination of filter values in the variable popup in Analysis Office, you can reuse them in SAC. There is nothing to do – just open your story and it will work. But did you know that you can also set variable filters as page filters in the SAC story for your BW query variables?

In the first blog of this blog series I have presented how SAC deals with BW Query variants. Let’s see in a video how the copy & paste of values in the variable popup works and how you can pass values as a story filter to a specific SAP BW variable.

Zero Suppression

With the update to wave 2020.15, you can now suppress zeros and nulls on SAP BW tables and charts within your story.

In SAP Analytics Cloud, the Zero Suppression feature makes it easier to extract meaning from data as it allows you to remove redundant or invaluable data. You can find this setting for dimensions and measures.

Another example with R: Create a table including sorting & search!

With R you can do a lot of nice things within SAC – and this also works with the live access to SAP BW. In one of the past blogs I presented an example with gauges using R on selected BW Query key figures. A lot of libraries are available, and several blogs has been published around this topic. But when it comes to R the community is very creative. Let’s see an interesting example with R and how easy it is to implement.

First, we insert a R Visualization widget into our story and connect it to our SAC live model. We give our Input Data a meaningful name, which we will use in the R script:

In the next step we will insert the following R script:

In the next step we will insert the following R script:
library(DT)
datatable(BWQUERY, options = list(
  searching = TRUE,
  pageLength = 10,
  lengthMenu = c(10, 50, 100),
  filter = 'top',
    initComplete = JS("
    function(settings, json) {
      $(this.api().table().header()).css({
        'background-color': '#000',
        'color': '#fff'
      });
    }")
))

And finally, we can see the result:

And now the whole process as a video:

Additional information related to the custom grouping feature

In the last blog one of the highlights was the custom grouping feature. I have received many questions about this wonderful feature in SAC when using the live connectivity to BW.
Many departments are now able to create their own custom hierarchies directly in SAC (If you don’t know this feature please check the last part of this blog series to learn more about this).

And do you know what? You can also use this custom hierarchy to filter your data in the story. In the next video you can see it how it works as story filter or in the designer tab.

Flexible date handling in SAP Analytics Cloud with BW Live Connection

Wouldn’t it be nice to have an easy option to implement date comparisons and flexible date filters with less effort in SAC based on the BW live connection? Maybe even to allow that the story creators & end users can set up this by themselves? Or a feature that allows them to select the key date for a story and have all charts and widgets adjust accordingly? This was delivered with the Flexible date handling in SAP Analytics Cloud by using the BW Live Connection.

My colleague Jens Braun has posted a blog about this topic and I would strongly recommend to read the blog: https://blogs.sap.com/2020/08/24/flexible-date-handling-in-sap-analytics-cloud-with-bw-live-connection/ 

One interesting feature in this area is the dynamic time filter:

In the following story based on SAP BW live data you can see how I switch dynamically from the full data range to November 2020, the current quarter and the last year. Fully dynamic.

For more details about this feature please read the afore mentioned blog from my colleague Jens Braun.

Using dynamic text to access the content from the SAP BW Query variables

The dynamic text feature is a very powerful feature. It allows you to put any content from filters, input controls, system information and of course BW Query variable input/content wherever you want as text.

Please note that you can make use of dynamic texts in the headers of charts and tables as well.
In my example I want to filter countries in a variable popup from the BW Query and it should be placed in the title of the SAC story.

Now you can access a lot of information delivered by the SAC story or the SAP BW Query.
I will select the variable from SAP BW which has been created on the dimension/characteristic “Country”. Whatever I enter in the BW Query variable popup will be placed as dynamic at the selected place.

You can close the window using “Create” and a place holder has been inserted at the location where you want to see your dynamic text. Now you can refresh your SAC story to see the SAP BW query variable prompt. In my example I select Germany and France as filter values.

If you now click on “Set” the values will be passed to SAP BW and the story will show the data for Germany and France. And the placeholder which has been created for the dynamic text, will show the filtered countries.

Just try this feature with some other objects & story properties.

Support for tuple filters (Filter data that is organized in multiple dimensions)

SAP Analytics Cloud supports tuple filters in SAP BW queries. This includes filtering with multiple dimensions in addition to using the “AND” and “OR” operators. The feature requires BW/4HANA 2.0, BW on HANA 7.5 SP16.

You can create filters by selecting multiple data points in the widget. Note that “Exclude”, blending, and linked analysis between widgets that contain tuple filters are not yet supported. Hierarchy and Currency Unit cannot yet be filtered.

In my example I select the dimension values city “DE/BERLIN” and the products “MOUSE-1” and “PC-2“:

The designer will show that the corresponding dimensions has been filtered:

If you know click on this element in the designer, the following information will popup:

Please note that this also works with charts and tuple filters can also be used in linked analysis. For more details please have a look into the SAC help: https://assets.sapanalytics.cloud/production/help/help-release/en/8a10d5859ffc4df49605efe131c298d1.html 

What about “Navigation Attributes” & “Display Attributes” from SAP BW?

What is the difference between “Display Attributes” and “Navigation Attributes”? Display attributes can only be used for display purpose, which means that they will only act as a detail of a characteristic.

Navigation attributes are very different. They are attributes which can be used like any other characteristic in SAP BW. If we declare an attribute of a characteristic as navigation attribute, we can do functions like filtering or drilldown on this attribute like we do on any other characteristic.

How SAC deals with this different attribute types?

********* Display Attributes *********

A characteristic InfoObject in SAP BW can have display attributes which contains additional information’s. For example, you can have the customer number as a characteristic and e.g. add the customer location, customer segment in the backend to this characteristic as attributes. You can usually activate attributes in the SAP BW query, and they will be shown – e.g. in SAP Analysis Office. It is also possible to activate these attributes in SAP Analytic Cloud as well.

In the following screenshots you can see how I show/hide the display attributes colour and product group for the characteristic product. This can be done by end user during the navigation or by the story creator if it should be activated already.

End User:

Right click on the dimension and select “Show/ide” and then “Properties”

Select the attributes and klick on “OK“

Now you can see the display attributes coming from SAP BW in your table

Story creator:

Story designers can activate the attributes in a similar way. The only difference is that they do it directly in the designer.

 ********* Navigation Attributes *********

On SAP BW side the navigation attributes need to be assigned to the corresponding characteristic and must be declared as “Navigation Attributes”:

You can now activate them e.g. in a Composite Provider and they will be available in the SAP BW Query.

Hint: It’s easy to identify “Navigation Attributes”. You can see it in the technical name of the relevant object. They always start with the BW characteristic to which you have assigned the navigation attribute followed by 2 underscore (“__”) and then the technical name of the attribute itself.

In my example ZSH_PR__0BH_CR and ZSH_PR__0BH_PG.

In SAC you can see them in the BW Live model among all the other dimensions.

That’s what SAP BW customers would expect. Remember: Navigation attributes are attributes which can be used like any other characteristic in SAP BW. If we declare an attribute of a characteristic as navigation attribute, we can do functions like filtering or drilldown on this attribute like we do on any other characteristic. And that’s what we can do now in the SAC story:

The question “if SAC supports navigation attributes in the live mode” has been asked many times and the answer is clear – yes, it is supported.

Again one of the many features which is supported plug & play without touching or changing the SAP BW query ?

Working with SAC and the report-report interface (RRI) from SAP BW

The report-report interface (RRI) allows you the flexibility to call a jump target (receiver) online from a BEx query (sender) within or outside of the BW system. Jump targets assigned to a query are available in the BI client under the Goto function.

Queries, transactions, reports, and Web addresses can be jump targets. The parameterization of the target action is taken from the context of the cell that you have jumped from. You can set parameters for calling query using input variables that are filled from the selection conditions and the element definitions of the selected cells in the sender query.

For more details about the report-report-interface, please have a look into the SAP Help (e.g for SAP BW/4HANA):
https://help.sap.com/viewer/107a6e8a38b74ede94c833ca3b7b6f51/1.0.1/en-US/4a5b96c6517f2e24e10000000a42189b.html 

As you know SAP Analytics Cloud has a URL API which can be used to directly open stories using a URL with parameters. For details please check the SAC help page: https://help.sap.com/viewer/a4406994704e4af5a8559a640b496468/release/en-US

And that’s exactly the point which can be combined with the report-report-interface from SAP BW.

Some customers have asked me in the past, if there is a possibility to open a SAC story with a filter or the SAC Data Analyzer from Analysis Office.

Let’s start with the Data Analyzer. What we want to achieve is that you can right click in Analysis Office and then you can see an entry in the context menu which allows you to jump to the SAC Data Analyzer. This should work from all Analysis Office workbooks where a specific BW Query is embedded. The following screenshot shows what we want to achieve.

The following link will be needed to make this possible (you can also use it as a hyperlink in a SAC story but that’s not what I want to achieve):

https://LINKTOYOURSACTENANT/sap/fpa/ui/app.html#;view_id=dataAnalyzer;connection=BW4;dataSourceName=ZSHSALES4_Q001_SALES

The following screenshots shows how this link works:

To maintain the report-report-interface settings for a specific SAP BW Query you need to have access to the SAP BW backend. Connect to SAP BW and run the transaction “RSBBS” to open the interface.

Enter the technical name of the SAP BW Query and start the maintenance process to create sender/receiver assignments.

In the next screen select “Web Address” and maintain the “Receiver Object“:

Now enter the URL and hit “Apply“:

Finally enter a meaningful description and “Apply“:

Click on “Save” and that’s it:

Open now the SAP BW Query in Analysis Office and jump from there to SAP Analytics Cloud:

One interesting note: Maybe you noticed in the report-report-interface (RRI) a setting called “Assignment Details“:

This is a really powerful SAP BW feature which allows to “send” generic values or URL parameters from the SAP BW Query to any kind of target (e.g. open Transactions in SAP ECC or SAP S/4HANA and many more possibilities).

In combination with SAC and its URL API you can do many nice things – e.g. you can open any SAC story an send any information using a generic URL.

Example: you can right click in Analysis Office on a customer ID and can jump to an SAP Analytics Cloud story which usually shows sales details for all customers. The parametrized URL will allow you to filter the story to a specific customer ID – a fully generic process. There are several blogs available for each – the SAC URL API and the report-report-interface from SAP BW. You can start to test the possibilities on your own. I just wanted to give you a glimpse about what’s possible.

Create generic hyperlinks in SAC tables, to open external pages using parameters (+example with SAP S/4HANA)

In SAC you can add dynamic hyperlinks to your visualizations and tables. The easiest setting is to jump from your item to another page of your story. This includes also the possibility to apply selected dimensions as filters.

You know already that SAC provides a SAP Analytics Cloud URL API. But did you know that you can also click on a bar in a chart or you can click on a dimension value in a table and can open an external page? This includes also, that you can pass parameters in the URL to the target application? I did this in one of the last blogs to show how you can jump from a bar chart to an SAP S/4HANA transaction. I will so something similar now with a table widget.

First of all add your table to your SAC story. In my example I have a table showing sales data coming live from SAP BW/4HANA. The first row shows the product categories. In the edit mode you can add a hyperlink to this table:

What I want to do now is to show you a really simple example. If the end user clicks on a dimension value, the Google search should open and the dimension value should be used as a search criteria.
To achieve this, I add the url “https://www.google.de/search?q=” as “External URL”. You can place the cursor at any place of the URL and SAC will open an interface which allows you to select the product dimension.

It is also possible to select the description or the ID of the dimension.
In addition you can also make use of a dynamic label where you can use the dimension ID or description. I have used “Search in internet for” as label, followed by the dimension value.
This makes the process more dynamic and intuitive. I would also recommend to select “Open in New Tab”. If you finished your settings close this settings by clicking on “Done”.

Now you have 2 options how to work with this URL.

First option is to click on a dimension value (e.g. BROWSER) and then you can navigate to the external page by clicking on the link under the table description.

The second option allows you to show the hyperlinks to the external page directly in the table.
We will use the second option.

You will notice that you can click on the dimension values (for SAC context menu just click in the right empty space).

After clicking on a dimension value, the Google search will open as expected and the search will start. SAC will send the dimension value to Google.

So all in all a simple but powerful feature.
What you need to consider is, that the target URL is correct and you parametrize the target URL parameters correct.

You can see the full process in the following video:

Let me give you now another example.

The following link will open S/4HANA and the sales order 7 will be shown.

https://MYS4HANAURL/ui#SalesOrder-display?SalesOrder=7

So all we need to do in SAC is to repeat the steps above and use the link to S/4HANA.
At the point where the Sales Order ID is needed, you can use the ID provided by SAC.

You need a story with a table inside, which has also the sales order ID in the rows (e.g. a table based on live data from SAP BW or based on another data source).

In the next step just add the relevant URL to SAP S/4HANA into the field “External URL”. You need to enter SAP S/4HANA first to get the required URL to the transaction you want to use.

Now add the ID delivered from the dimension value exactly at the place in the URL, where the sales order is required/expected (just click at the place in the URL and SAC will ask you which dimension you want to use). If needed do the same for the label:

Switch to the view, that you see the hyperlinks directly in the table (like in my first example above).

I have recorded a demo for you which shows exactly that – using a SAC table as source and jump to SAP S/4HANA Fiori Launchpad and open a sales order. No scripting, no programming, fully generic, just a few clicks 🙂

So all in all you need to have your SAP S/4HANA URL ready and you need to know your parameter:

Then you are ready to make use of the hyperlinking feature in SAP Analytics Cloud, if your SAC model provides the dimensions needed to parametrize the URL. Please note that you can have more than one parameter in your URL.

Tunnel Connection Type from SAC “live” to SAP BW

Let me start with a quote from the SAC Q3.2020 update ( https://saphanajourney.com/sap-analytics-cloud/product-updates/sac-q3-2020/ ).

“Would you like to be able to share your business findings and insights with your external stakeholders, without giving them VPN rights? SAP Analytics Cloud administrators will now be able to create connections using the Tunnel connection type. This connection type will allow users outside your corporate network to connect live to your data, without giving them VPN rights. The Tunnel connection can be created to an on-premise remote source through the SAP Cloud Connector. These sources include HANA, BW, and S4HANA.”

For more details about the SAP Analytics Cloud Tunnel Connectivity and its use cases please check the SAP Blog from my colleague Shailendar Anugu.

If you have installed the SAP Analytics Cloud Agent Simple Deployment Kit which includes the SAP Cloud Connector or you have already deployed the SAP Cloud Connector you can make use of this new feature easily.

For further details about the SAP Cloud Connector configuration and other materials please check the following SAP note: https://apps.support.sap.com/sap/support/knowledge/en/2358097

You can find the complete details about the installation of the SAP Cloud Connector here : Cloud Connector Installation – SAP Help.

There are also a couple of SAC Blogs available regarding to the SAP Cloud Connector configuration and the new tunnel connection.

In a Nutshell: after configuring the “Cloud To On-Premise” settings in the “Cloud Connector Administration” …..

…. you can create a live tunnel connection in SAC to your SAP BW system.

You can start to build your stories based on that connection and can access them like shown in the following video. If you watch the short video you will see the new story exploration option available with “Release 2021.01”. For more details please have a look into the product update blog for that release: https://saphanajourney.com/sap-analytics-cloud/product-updates/release-2021-01/#StoryExploration

That’s it for the moment with “Part 4” of this blog series. Hope you enjoyed reading the blog.

See you in “Part 5” with some more things you can do with SAP Analytics Cloud live on top of SAP BW. Part 5 of the blog series will be published soon.

Cheers

Adem