Quantity Unit Conversion in SAP Analytics Cloud Pl...

hartmut_koerner · ‎04-16-2021

Summary

SAP Analytics Cloud supports very well different currencies and currency conversions, but does not offer a corresponding functionality for quantities. However, in sales quantity planning you face the situation that different products can be sold in different quantity units and even the same product can be sold in different units.

The conversion between units has two aspects: On the one hand side it is about the conversion between units of the same physical dimension (time, length, mass, area, volume, energy, and so on), e.g. 1 kg = 1000g, 1km = 1000m, 1h = 3600s, and so on. On the other hand, it is about product-specific conversions between different physical dimensions, e.g. 1 box of mineral water contains 12 bottles and 1 bottle contains 0,7l water.

In this blog, I will introduce you to an approach on how to work with quantities of various units in SAP Analytics Cloud and how to convert between the different units, both product-dependent and independent. The approach is based on an SAP S/4HANA integration where the different units and conversions between them are already defined in the T006* tables (product-independent) and table MARM (product-specific).

Basic solution idea

The idea is to create one SAP Analytics Cloud model that contains the conversion factors between the different units for each product. This model is filled from the SAP S/4HANA table MARM. The quantity unit itself is modeled as a generic public dimension that is filled via an OData service that reads table T006.

In a second model, I perform the proper sales quantity planning for the products. The quantity unit is also a generic dimension. I load the actual sales data from last year but get them in the different sales units of measure. In a data action, I access the first model via the LINK function and perform the unit conversion to the base unit of measure of the product.

Solution in detail

Step 1: Check SAP S/4HANA product-specific unit conversion

In transaction MM03, under Additional Data on tab Units of measure, you find the product-specific unit conversion factors:

In this example, product FG129 has the base unit PC (Piece) and 1 Piece weighs 20 kg. In table MARM this looks like this:

The conversion is calculated via two numbers, the numerator field UMREZ and the denominator field UMREN. We will recognize these two fields again in a later chapter, so please keep them in mind. Note also that there is an internal representation (KG, ST) of the unit and an external, language-dependent one (KG, PC). The latter one we use in SAP Analytics Cloud.

Step 2: Create a model for the unit conversion factors

The model simply contains the two dimensions product and quantity unit. In the account dimension I have created two measures “UMREZ” and “UMREN” for the numerator and denominator of the conversion factor.

I used the public dimensions SAP_ALL_PRODUCT and SAP_FI_BPL_QUANTITY_UNIT from the “Integrated Financial Planning for SAP S/4HANA” content package, but you also can create your own dimensions here. Master data for product and unit can be imported via the SAP S/4HANA connection using the OData services API Fin Unit of Measure (C_FINUNITOFMEASURE) with entity C_FinUnitOfMeasure and Remote API for Product Master (API_PRODUCT_SRV) with entity A_Product.

Step 3: Load product-specific conversion factors from SAP S/4HANA

In the data management area of the newly created model, I create a new import using the SAP S/4HANA connection. I choose the OData service Remote API for Product Master (API_PRODUCT_SRV) again and now the entity set A_ProductUnitsOfMeasure.

The two columns material (=product) and AlternativeUnit are mapped to the respective dimensions of the model. The two measures UMREZ and UMREZ are mapped accordingly:

In addition, I set a fixed value for the time dimension (here: 202101) and load the data directly into the Actuals version. The time dimension is not required here from a conceptual point of view , but in SAP Analytics Cloud it is a mandatory dimension. In a story on top of this model, I check the conversion factors. The base unit of measure is an attribute of the product dimension that I have included in addition. The result can now be compared with the table MARM mentioned before:

Step 4: Create advanced formula for product-independent conversions

The idea now is to extend the above table with the product independent conversion factors. In my example, I know that one piece of product FG129 weighs 20 kg. From this information I can derive that one piece of FG129 weighs 20.000g and that 1000 pieces weigh 20 tons.

The advanced formula that creates these additional entries is as follows:

// Mass

IF RESULTLOOKUP([d/MEASURE] = "UMREN", [d/SAP_FI_BPL_QUANTITY_UNIT] = "KG") = Null THEN

	DATA([d/MEASURE] = "UMREN", [d/SAP_FI_BPL_QUANTITY_UNIT] = "KG") = RESULTLOOKUP([d/MEASURE] = "UMREN", [d/SAP_FI_BPL_QUANTITY_UNIT] = "G")

	DATA([d/MEASURE] = "UMREZ", [d/SAP_FI_BPL_QUANTITY_UNIT] = "KG") = RESULTLOOKUP([d/MEASURE] = "UMREZ", [d/SAP_FI_BPL_QUANTITY_UNIT] = "G") * 1000

ENDIF

IF RESULTLOOKUP([d/MEASURE] = "UMREN", [d/SAP_FI_BPL_QUANTITY_UNIT] = "TO") = Null THEN

	DATA([d/MEASURE] = "UMREN", [d/SAP_FI_BPL_QUANTITY_UNIT] = "TO") = RESULTLOOKUP([d/MEASURE] = "UMREN", [d/SAP_FI_BPL_QUANTITY_UNIT] = "KG")

	DATA([d/MEASURE] = "UMREZ", [d/SAP_FI_BPL_QUANTITY_UNIT] = "TO") = RESULTLOOKUP([d/MEASURE] = "UMREZ", [d/SAP_FI_BPL_QUANTITY_UNIT] = "KG") * 1000

ENDIF

IF RESULTLOOKUP([d/MEASURE] = "UMREN", [d/SAP_FI_BPL_QUANTITY_UNIT] = "KG") = Null THEN

	DATA([d/MEASURE] = "UMREN", [d/SAP_FI_BPL_QUANTITY_UNIT] = "KG") = RESULTLOOKUP([d/MEASURE] = "UMREN", [d/SAP_FI_BPL_QUANTITY_UNIT] = "TO") * 1000

	DATA([d/MEASURE] = "UMREZ", [d/SAP_FI_BPL_QUANTITY_UNIT] = "KG") = RESULTLOOKUP([d/MEASURE] = "UMREZ", [d/SAP_FI_BPL_QUANTITY_UNIT] = "TO")

ENDIF

IF RESULTLOOKUP([d/MEASURE] = "UMREN", [d/SAP_FI_BPL_QUANTITY_UNIT] = "G") = Null THEN

	DATA([d/MEASURE] = "UMREN", [d/SAP_FI_BPL_QUANTITY_UNIT] = "G") = RESULTLOOKUP([d/MEASURE] = "UMREN", [d/SAP_FI_BPL_QUANTITY_UNIT] = "KG") * 1000

	DATA([d/MEASURE] = "UMREZ", [d/SAP_FI_BPL_QUANTITY_UNIT] = "G") = RESULTLOOKUP([d/MEASURE] = "UMREZ", [d/SAP_FI_BPL_QUANTITY_UNIT] = "KG")

ENDIF

For each physical dimension that you use, you need to create such a block of calculations for the different units belonging to that physical dimension. This example shows the calculations for the physical dimension mass and the three units g, kg, and tons. Please note that this formula does not make any assumptions about which of the unit conversion factors are already available. Therefore, it starts from the smallest unit g to kg to tons and then back again to kg an g.

After applying the advanced formula, the unit conversion table looks like this:

Now you also can understand the use of the numerator UMREZ and the denomination UMREN. You need both to avoid small numbers and rounding problems.

Step 5: Create advanced formula for unit conversion

Now, let us take a look at the other model in which I perform the sales quantity planning. This model also contains the two dimensions product and quantity unit, and it may contain additional dimensions like customer and others.

The formula to convert the sales quantities into the base unit of measure of the products is as follows:

VARIABLEMEMBER #QUANTITY OF [d/MEASURE]



IF LINK([KOE_UNITS], [d/Version] = "public.Actual", [d/Date] = "202101", [d/MEASURE] = "UMREN") != 0 THEN

	DATA([d/MEASURE] = #QUANTITY, [d/SAP_FI_BPL_QUANTITY_UNIT] = [d/SAP_ALL_PRODUCT].[p/BaseUnit]) =

	RESULTLOOKUP([d/MEASURE] = "QUANTITY") * LINK([KOE_UNITS], [d/Version] = "public.Actual", [d/Date] = "202101", [d/MEASURE] = "UMREZ") /

	LINK([KOE_UNITS], [d/Version] = "public.Actual", [d/Date] = "202101", [d/MEASURE] = "UMREN")

	DELETE([d/MEASURE] = "QUANTITY")

ENDIF

DATA([d/MEASURE] = "QUANTITY") = RESULTLOOKUP([d/MEASURE] = #QUANTITY)

First, the system checks whether a conversion factor has been maintained for a specific sales quantity unit. Then, it multiplies the quantity of this unit by the numerator and divides it by the denominator of the corresponding conversion factor from the linked model. Implicitly, the right side of the equation is aggregated over the different sales quantity units. The data in the original sales quantity unit is deleted and in the last step is replaced by the total value in the base unit.

This formula can be adapted to specific needs: You may want to keep the original values and write the converted value to another measure. Or you do not want to convert into the base unit of the product, but into another specified unit.

Step 6: Apply advanced formula for unit conversion

In this scenario, I assume that you have sales quantity data in different quantity units from the start. This can be the case, if you simply copied the sales quantities from last year’s actual data or if you planned manually in different units:

After applying the advanced formula, you will get the following result:

Two tons correspond to 100 pieces and 200 kg correspond to 10 pieces. Adding the 30 pieces that were planned directly in quantity unit pieces results in a total of 140 pieces.

Conclusion

With this two-step approach, it is possible to convert sales quantity data from arbitrary units to the base unit of the product. The product-specific unit conversions were loaded from SAP S/4HANA into a separate model and enhanced with conversions to other derived units. In a second step, the conversion factors were applied to the sales quantity plan.

The basis for this approach is to model the quantity as a separate measure in the account dimension and the quantity unit as a generic dimension.

This modeling approach has also been chosen in the content package “Integrated Financial Planning for SAP S/4HANA”. This allows you to immediately apply this blog post to the sales planning application within this content package. The conversion to the base unit of measure of the product in particular is important if you also want to use the product cost planning application within this content package, because a product cost estimate is always calculated in the base unit of measure.

For additional information, please see the following references:

Integrated Financial Planning for SAP S/4HANA with SAP Analytics Cloud

Visit your community topic page to learn more about SAP Analytics Cloud

Find all Q&A about SAP Analytics Cloud and feel free to ask your own question