Creating Analytical Queries

An analytical query generates ad-hoc views of business data. An analytical query defines which attributes and measures are available in a multidimensional report and also provides many default settings such as the position of the fields (rows or columns), and whether totals are displayed.

Filters can be defined in the analytical query to restrict the data the business user will work with. Hierarchies can be defined to make it easier for the business user to navigate large data sets.

The analytical query is used to answer business-specific questions where free exploration of the data is needed.

Technically, analytical queries are the upper-most CDS views that sit at the very top of the virtual data model stack.

Analytical queries are built on top of cube type CDS views.

An analytical query strictly defines the list of allowed fields and filters. However, many settings such as totals and zero suppression can only be set as defaults by the analytical query developer. Default settings can be changed by the business user when they execute the query to suit their own requirements.

Analytical queries are central objects of SAP S4/HANA embedded analytics. KPIs, Multidimensional Reports, and the Analysis Path Framework all consume analytical queries.

SAP provides a large number of ready-made analytical queries for all lines of business. However, there are times when the SAP delivered analytical queries do not fit the business requirement.

These include:

• You prefer an alternative, default layout of the query (placement of fields in rows and columns)
• To apply fixed filters so that the query is more meaningful to specific business scenarios
• You want to choose different fields to those chosen by SAP
• To generate custom calculated measures
• You have created a custom CDS view so you need a new analytical query
• To define restricted measures for side-by-side comparison (for example, this year - last year)
• To implement hierarchies

The key steps to create a custom analytical query include:

1. Choose a data source
2. Select the required fields
3. Choose the axis for each field (rows, columns or free)
4. Check and adjust the settings for each field
5. Optionally define filters (fixed or user input)
6. Optionally define calculated or restricted measures
7. Save draft
8. Optionally preview the results
9. Publish

SAP provide the Custom Analytical Queries app to define your own analytical queries.

Many key users and developers are already familiar with building queries using SAP tools such as BW Query Designer and BusinessObjects Web Intelligence) and will find the wizard-like tool easy to use. Many of the well-known query features, such as restricted measures, and the option to choose whether master data values should only display when there are posted values, will be familiar.

Once the analytical query is created, a tile is usually created to launch it. This can be done using View Browser app.

The results of an analytical query can be downloaded to Excel.