In the first blog post we used the Custom CDS Views application to define a cube view with the required logic for our business case. Let’s have a look at the second key user extensibility app “Custom Analytical Queries” in this blog and understand how we can define an analytical query for our use case.
Custom Analytical Query
Custom Analytical Query app helps the key users in defining structured quires without understanding the query language or technical details. The application provides functionality to select fields from the selected analytical data source, create restricted measure, create calculated measure and perform currency and unit conversion.
In the use case we have considered we will allow user to analyse the measures in one common display currency. We will define the currency conversion from transactional currency to display currency in the analytical query. We will create a custom analytical query similar to the one used in the standard delivered application. We will include all the measures that are available in the delivered analytical query in our custom analytical query
Building Custom Analytical Query
Following steps guide us on creating Custom Analytical query for the defined use case,
- Launch the Custom Analytical Query app
- Click on Create
- Provide the technical name and select the CDS view created in Part I as the data source
- Provide title and select all the required dimensions
- The Custom CDS Cube view used as the datasource has quantities and amount measures required for the Open Sales analysis. In the analytical query we will convert all the measures in transactional currency to a common display currency using the currency conversion functionality.
- We will also create Calculated measures.
- The below table shows how to define calculated measures and currency conversion similar to the delivered analytical query
- Save and Publish the Query
The analytical query CDS view will become the source for the Smart Business KPI application.
In the next blog post we will use the “Manage KPI and Reports” application to develop a Smart Business KPI application