How to easily perform live ad-hoc analysis on SAP BW Queries in SAP Analytics Cloud

[thomas_tschimmel]

How to easily perform live ad-hoc analysis on SAP BW Queries in SAP Analytics Cloud 

As you all know, you can create live models on top of SAP BW Queries in SAP Analytics Cloud and use them to build your stories (Dashboards). In addition it is also possible to run an ad-hoc analysis without creating a live model.   
 
So what is the Data Analyzer in SAP Analytics Cloud? In SAP Analytics Cloud you can start ad-hoc analyses with data analyzer - a predefined ready-to-run service for multidimensional, pivot styled ad-hoc analysis for SAP BW Live queries, SAP HANA Live views, SAP Datasphere models and SAP Analytics Cloud models. SAP BW queries and SAP HANA views can be accessed directly and no additional model needs to be created. The data can also be displayed in charts. 
 
In my blog I will perform the ad-hoc analysis on top of a SAP BW Query located in SAP BW/4HANA. For more details about the lice connectivity please follow the SAP Analytics Cloud help page: https://help.sap.com/docs/SAP_ANALYTICS_CLOUD/00f68c2e08b941f081002fd3691d86a7/5b4dad4d97664c41ae63b...  
 
There is also a blog series from my colleague Adem Baykal which provides many examples why the SAP Analytics Cloud connectivity to SAP (e.g. SAP BW/4HANA, SAP BW on HANA) matters: https://community.sap.com/t5/technology-blogs-by-sap/analyse-your-data-live-with-sap-analytics-cloud...  
 

1) The Data Analyzer can be opened via the navigation panel on the left side: 

2) First we need to select a data source: 
 

 Choose your SAP BW connection and search for the BW Query: 

 Usually a SAP BW Query also contains query variables. You will see this as a prompt in SAP Analytics Cloud and that is the same what you would see, if you would open the SAP BW Query in other SAP BI clients - e.g. SAP Analysis for Microsoft Office. 
 
Just make your selections or open variant saved on the SAP BW Query side. It is also possible to create a new variant directly in SAP Analytics Cloud which will be saved on the SAP BW side.  
 

We will not do that, but the SAP Analytics Cloud live connectivity to SAP BW provides many great features which underscores the power of the live connectivity and the deep integration into the SAP world (e.g. data point commenting on live SAP BW data). Let’s continue with the prompt. We make our selections and start the ad-hoc analysis by clicking on “Set”. 

The Data Analyzer will appear on the screen with its pre-defined design. We see our data coming live from the SAP BW Query in a cross table.  
 
With the designer on the right side, you can drag and drop measures and dimensions in rows or columns. You can also rearrange the order of the dimensions and measures.  

End users would expect an interactivity with drag & drop and that is exactly what is provided by the Data Analyzer in SAP Analytics Cloud. 

The application itself is fully interactive and we can directly interact with the SAP BW Query. 
If needed a context menu can be used for the end user interactions.  

Just a few examples what we can do now:  

• By clicking “Swap Axes”, you can exchange rows and columns. 
• By clicking “Totals Position”, you ou can set the display of the totals. 
• By clicking “Arrange Parent Nodes”, you can set the position of the parent nodes. 
• By clicking “Compact Display”, you can show multiple dimensions in the drill down as hierarchy (for SAP BW queries only). 
• By clicking “Suppress Zeros”, you can suppress zeros in rows and/or columns (for SAP BW queries only). 

Each dimension in the Builder panel also has a context menu that you can open and use by clicking the three dots behind the according dimension. To view totals, klick on the three dots, choose Show/Hide, and select Totals, and setup your request. 

You can start to work with the live data and one task could be to filter your data. 
This can be done on the right side in the “Designer”……. 
 

 …….or you can create a story filter for a dimension in the menu bar of the Data Analyzer. 
Let’s do that: 
 

The Data Analyzer provides different filter settings: 

With the QRC 01.2024 release a formula editor has been added to the Data Analyzer. With the formula editor you are able to calculate our own calculated measures and there is no need to touch the SAP BW Query. 
 
You can also try different options like flexible drill downs or even “drag and drop” a dimension into the cross table. 
 

It is also possible to switch easily to a different chart: 
 
Try it out and change the visualization options. In the Builder panel you can also switch the visualization of your data from table to chart by choosing a chart type in the dropdown box at the top of the panel. Each chart type offers a specific set of settings that you can change. The following chart types are available e.g. bar, column, stacked bar, stacked column, line, pie or a donut chart.  

We will crate a stacked bar chart: 

There is always the possibility to undo & redo your actions. You can use the toolbar commands “Undo”, “Redo” and “Reset” in the Data Analyzer. These functions support all actions that manipulate the data source in the table (like for example sorting, swap axis and more).   

If needed, you can export your data into different formats: 
  

Of course you can do a lot more if working with the Data Analyzer in SAP Analytics Cloud live on top of SAP BW Queries. Just explore the context menu depending on the element you click in the Data Analyzer. Just an example what happens, if you click on a data value. And also some styling settings are available: 
 

You can also make use of sorting or filtering options:  

You can activate or deactivate attributes of a dimension:  
 

Or run a currency conversion: 
 

Or just easily activate any hierarchy for a dimension which is available in SAP BW. Just do it in the cross table: 
 

Or activate your hierarchy in the Designer on the right side: 
 

The effect is the same. You will see the hierarchy in the cross table and if needed you can drill down or up: 
 

The hierarchy is also visible in your charts if you switch from the table to one of the available options: 
 

If you want to switch to a different SAP BW Query, this is an easy task. Just reset the view in the Data Analyzer a new data source can be connected:  

There are many more options how you can interact with the Data Analyzer, but this was only a short introduction how easy you can perform live ad-hoc analysis on SAP BW Queries in SAP Analytics Cloud.  
 
You can also enable the Data Analyzer for any widget in your SAC stories

This can also be enabled for all widgets of the story easily in the story settings if needed. 
 
That’s it, if you want to jump start your ad-hoc journey with the Data Analyzer live on top of SAP BW Queries, just start with it and if needed please check the SAP Analytics Cloud help page, available in several languages: https://help.sap.com/docs/SAP_ANALYTICS_CLOUD/00f68c2e08b941f081002fd3691d86a7/3bd79ad3e58442e7a5499...