How to do Unit of Measure Conversion in Sales analytical applications with Key User Tools
Table of Contents
In S/4HANA’s Sales process, a lot of embedded analytics apps are provided for the customers to monitor the ongoing sales and billing situation.
It is very common for an internal sales representative or a sales manager to see the analytical KPIs not only by different dimensions but also in different currencies or Units of Measure (UoM).
In SAP’s standard sales analytics applications or CDS views, the quantity KPIs like Incoming Sales Orders, Sales Volume and Credit Memos are always displayed in Base Unit.
The sales unit and billing unit are also provided in the CDS views like I_SalesOrderItemCube and I_BillingDocumentItemCube, but they are not exposed in the consumption query views and their corresponding analytical applications.
Even if you create a custom analytical query to expose the quantity KPIs in sales unit or billing unit and create analytical applications based on it, it is also very difficult to see the aggregated KPI data grouped by high level dimensions like sales organization, year, quarter, month, etc.
This is because of the base unit or sales unit for different products might be different, it is not possible and makes no sense to aggregate the quantity data of different UoM.
In case you have such requirement to convert all the quantity data of different products to a uniformed UoM and see the aggregated data in the analytics applications, this blog will guide you to achieve such unit conversion functionality in a custom CDS cube view.
2. How to maintain alternative Units of Measure
First thing first, in order to get the alternative UoM of your product data, you need to maintain them properly via Fiori app “Manage Product Master”.
Under the “Units of Measure” section, you can add additional UoM with conversion factors:
The conversion factors between two units can be variable due to different products, for example:
- For soda drinks, 1 case = 6 each
- For spring water, 1 case = 32 each
After you maintained the alternate UoM, the data including product, base unit, alternative unit, conversion factors will be saved to data base tables and can be retrieved from CDS view I_ProductUnitsOfMeasure.
3. Units Of Measure Conversion Logic
In CDS view I_ProductUnitsOfMeasure, the necessary fields are provided to do unit conversions.
4. How to implement UoM conversion with Key User Tools
Within SAP’s sales analytical applications, currency conversion is provided as standard feature, but unit conversion is not provided, therefore we need to achieve this functionality by custom CDS views and then build custom analytical applications.
With all the information above, now we can start the implementation in key user tools. (In order to use key user tools, you need the authorization from business role SAP_BR_ANALYTICS_SPECIALIST)
First, we need to create a custom Cube view which has association to CDS view I_ProductUnitsOfMeasure (this association is not provided in the standard Cube view). In this blog I will use I_BillingDocumentItemCube as example and you can refer this approach to create other Cube views too.
1) Go to app “Create Custom CDS views”, click “Create” to create a new CDS view, add I_BillingDocumentItemCube as primary data source
2) Add view I_ProductUnitsOfMeasure as associated data source
3) Then we need to specify the on-condition of the association, here due to the one to many cardinality between I_BillingDocumentItemCube and I_ProductUnitsOfMeasure, we need to specify additional foreign key condition to make the cardinality to one to one.
To do this, we need to add an extra parameter P_DisplayUnit to this custom CDS view. Go to the parameters tab, add a new parameter and set a default value (e.g. CS) for this parameter or leave it to the user end for input at runtime
Now let’s go back to the General tab and edit the association condition
Set the association condition as following, here on the right side of the condition you can select Field, Parameter or Session Variables, however a fixed value or string is not supported, this is why we need the additional parameter P_DisplayUnit.
4) Now we can continue to the Field Selection tab, and add a new calculation field based on the unit conversion logic.
First, select some basic dimension fields or KPI fields required for your use case, and then click Add to create a new calculation field.
The calculation expression is like following:
division( I_BillingDocumentItemCube.BillingQuantityInBaseUnit * _I_ProductUnitsOfMeasure.QuantityDenominator, _I_ProductUnitsOfMeasure.QuantityNumerator, 2 )
With that we create a new calculated field BillingQuantityInDisplayUnit
5) Then we need to set the corresponding properties for our new field BillingQuantityInDisplayUnit. Before you set the Aggregation and Semantics properties, you need to save your custom CDS view and edit it again, this will trigger the static checks for your custom CDS views and if no error pop up, you can save it.
After you save your CDS view successfully, then you can go to the Filed Properties tab and set the Aggregation and Semantic, choose a default aggregation for this new field and set corresponding UoM to Alternative Unit.
6) Then you can preview the data to verify the values in the new field
7) You can also add CASE WHEN condition to handle the situation where the specified display unit is not maintained in some products’ master data, for example set to 0 or null.
At last you can publish your custom CDS view and then you will be able to use it as data source in “Custom Analytical Queries” and then build a query or an OData to be consumed by your custom application.