Unit of measure (UoM) conversion functionality can add significant flexibility and enhanced analysis capabilities. Instead of having to create custom ABAP programs, you can use the new standard functionality in SAP NetWeaver BW 7.0. Here are the steps to implement UoM conversions in transformations.
Key Concept
Unit of measure (UoM) conversion refers to the act of converting quantity key figure values into other units of measure for analysis purposes. For example, orders might be taken and stored in UoMs, such as each, case, and pallet. If you try to add the values without regard for UoM, the total is meaningless. To provide a meaningful sales total, you must convert the values from the multiple source UoMs to one common UoM for analysis. The UoM conversion functionality within SAP NetWeaver BW provides this capability.
Unit of measure (UoM) conversion has always cause challenges in BI reporting. Unlike currency conversion, which applies a common multiplier to various key figure values, UoM conversion typically requires much more complex logic because each material can differ in its conversion multiplier. For example, when converting from each to case, material A has 10 eaches in a case and material B has 24 eaches in a case. The system must apply the multiplier for materials A and B based on both the individual UoM and material conversion rate for the associated material.
In SAP BW 3.x, the typical method of UoM conversion involved writing custom ABAP user exits and formula variables to provide this functionality because there was no standard functionality in SAP BW to allow automated UoM conversion. SAP now provides this functionality standard in the SAP NetWeaver BW 7.x.
You can use UoM conversion mainly in two areas: during a transformation (i.e., moving data from one structure in SAP NetWeaver BW to another in the back end) and during query processing. In this article, I discuss the transformation scenarios.
UoM Conversion Design
The first decision to make with regard to UoM functionality is where to implement the conversion: at the time of loading during transformation or during query processing. Both approaches have pros and cons.
A design in which UoM conversion is performed within transformations in the back end lends itself to potential issues requiring a restatement of data. For example, I have an InfoCube with key figure values to represent each (EA), case (CS), and pallet (PAL). The source data contains sales quantities in the base UoM. The UoM functionality populates the three target key figures when data is loaded.
If material A has a source quantity of 100 EA, based on the conversion rates in Table 1, this corresponds to 10 CS and 1 PAL. The system applies the conversion to the data and adds the converted data as key figure values in the InfoCube. The system then adds a value of 100 in the each, 10 in the case, and 1 in the pallet key figures. This conversion is applied for each source quantity, converting all values to their appropriate UoMs and storing them in the associated key figure.
Table 1
Conversion rates for material A
The potential issue occurs when the UoM quantity configuration changes after data has already been loaded. In the example in Table 1, material A might have 10 eaches to a case now, but later it might have 15 eaches to a case based on a change to packaging standards. This change could render the previously loaded transactional data obsolete because it was loaded using the previous conversion factor.
It is important to consider the potential for configuration changes when designing UoM conversion scenarios. If changing the conversion factor requires you to restate data, it might make more sense to perform conversions dynamically in the query using transformations rather than at the time the data is loaded. Data would never become obsolete when converting it in the query because the most current UoM conversion factor is applied to the conversion at query run time.
The main concern with conversion during query run time can be the significant processing that must occur during query rendering. The system must look at the data at a very granular material level, determine the conversion factors, and apply them to the data. Therefore, depending on the volume of data, this can be a substantial performance concern. Determining which place to apply the UoM conversion logic requires weighing the restatement concern against the performance cost during query run time.
UoM Transformation Conversion Scenarios
The three common transformation UoM conversion scenarios are:
- Central UoM table conversion: The central UoM table T006 converts data from one UoM to another. For example, source sales data is in pounds and a conversion is needed to kilograms. The same basic conversion multiplier value is used for all materials.
- Conversion to fixed UoM: Source data is converted to one set UoM. For example, convert sales quantity to a fixed UoM such as cases.
- Conversion to dynamic UoM: Source data is converted to a UoM based on a field in the master data, such as sales data converted to the order unit UoM stored in the 0MATERIAL master data. Each material could have a different order unit, so the conversion is dynamic.
I will walk through the setup of all three scenarios.
Central UoM Table (T006) Conversion
This scenario is the most basic. The system applies a static multiplier to a key figure value. You use this scenario when converting from constant UoM values, such as from kilogram to pound or each to dozen. These conversion factors are not based on the individual material and never change. In this scenario, the system reads the constant values from table T006. This table stores the common conversion values. In my example, I will convert the source data from kilograms to pounds.
The first step in any UoM conversion process is to establish a quantity conversion type, which allows the system to understand the source, destination, and method of UoM conversion. You can set up quantity conversion types for each conversion scenario, depending on your requirements. The quantity conversion type allows you to specify what fields the system should use as the source and target, and where to obtain the conversion factor.
You set up the quantity conversion type in transaction RSUOM (Figure 1). Enter a technical name and then click the create icon or press F5.
Figure 1
Use transaction RSUOM to set up the quantity conversion type
The next screen shows the Conversion Factors tab (Figure 2). The system prompts for the field to locate the conversion factor. This is the method that the system should use to perform the conversion. In my example, I want the system to use the table T006 for the conversion, so I select the Dynamic Determination of Conversion Factor option and set it to Using Central Units of Measure (T006).
Figure 2
Set the Dynamic Determination of Conversion Factor to T006
The next step is to establish the UoM for the target (Figure 3). Click the UoM tab. In my example, the source data is in kilograms and I want to convert to pounds. I need to set the target Fixed Unit of Measure as pounds (LB). I am now ready to save the quantity conversion type. Click the save icon. I can now use this quantity conversion type in the transformation to provide the conversion from kilograms to pounds.
Figure 3
Set the Fixed Unit of Measure to pounds (LB)
When creating a transformation mapping between two quantity key figures, you have an option to provide a quantity conversion type. The system uses this field to determine which method to apply to the conversion. You can select the UoM conversion any time you have a source and target quantity key figures mapped inside a transformation. When you map the fields in the transformation, the Rule Details screen appears (Figure 4).
Figure 4
Transformation using quantity conversion type ZCONVLB
In this example, the transformation passes the data source stored in the 0QUANT_B key figure to the target 0QUANTITY key figure. The source data in 0QUANT_B is from a base UoM (stored in 0BASE_UoM) of kilograms. I want to convert this value into pounds and store this value in 0QUANTITY. The target UoM of LB is stored in 0UNIT. Note that I have chosen the previously created quantity conversion type ZCONVLB.
When data is loaded, the system takes the source value (stored in key figure 0QUANT_B) in kilograms and checks table T006 for the conversion factor to LB. This table provides the constant conversion factor of 0.45359 and applies that factor to the source data in kilograms to convert these values into pounds. In the example in Figure 4, the results of the conversion from kilogram to pounds are stored in the key figure 0QUANTITY. The UoM of LB is associated with the new value.
Note
To load the T006 table, use transaction RSA1. Under Source Systems, find your source SAP ERP Central Component (SAP ECC) system and right-click. Choose Transfer Global Settings > Units of Measure. This populates the T006 table from SAP ECC.
Conversion to a Fixed UoM
Converting to a fixed UoM refers to the conversion to one static material UoM such as converting to cases. You can source quantities in any key figure value, but the system converts them to the UoM of case. This conversion is more complicated than the conversion based on table T006 because it must use conversion factors associated with a specific material.
This source for all conversion data is found in the SAP ECC source system in the material master table MARM. This table is filled via the material master maintenance transaction MM02 (Figure 5). This UoM maintenance screen allows multiple UoM values and the translation of these values from the material’s base UoM. One material can have multiple UoM value translations. For example, one material might be set up to convert to each, case, and pallet.
Figure 5
Transaction MM02 with multiple UoMs
To use the UoM values shown in Figure 5, you need to load these values from SAP ECC to SAP NetWeaver BW. The system also needs a place to store the UoM conversion values. As part of the UoM conversion functionality, you need to activate the system-defined standard DataStore object (DSO) UOM0MATE. You can then map this DSO to the SAP ECC source data and load it with the UoM values via a transformation.
To activate UOM0MATE, use transaction RSD1 and choose the 0MATERIAL InfoObject (Figure 6). Choose the Business Explorer tab. If UOM0MATE has never been activated, the field Units of Measure for Char. will be empty. To start the activation process for UOM0MATE, click the create icon next to the Units of Measure for Char. field.
Figure 6
Click the create icon to start the activation process for UOM0MATE
The system then prompts you to create the DSO (Figure 7). You can specify the InfoArea for UOM0MATE. You can choose any active InfoArea. The system creates UOM0MATE inside this InfoArea.
Figure 7
Select the InfoArea for UOM0MATE
After you press Enter, the system creates the standard UOM0MATE DSO with system-defined characteristics and key figures. You should not modify these key figures and characteristics at all, nor should you use any other key figures or characteristics to supplement the standard ones in UOM0MATE. You should only activate UOM0MATE with the characteristics and key figures that are generated by the system by default (Figure 8).
Figure 8
When this screen appears, click the save and activate icons to generate the DSO
The fields in UOM0MATE mimic the UoM fields that appear in table MARM in SAP ECC. This DSO stores material, UoM, base UoM, and the counter (numerator) and denominator used to calculate the conversion from one UoM to another.
Next, fill UOM0MATE with the UoM conversion data values from SAP ECC. To do this, create a transformation in transaction RSA1 to fill UOM0MATE from table MARM. This transformation maps the source data from table MARM to UOM0MATE. The standard DataSource 0MAT_UNIT_ATTR maps and extracts the source data from MARM. Once activated, you can assign this DataSource in the transformation and load it into UOM0MATE (Figure 9).
Figure 9
Creation the transformation to load from the DataSource 0MAT_UNIT_ATTR to the DSO UOM0MATE
Inside the transformation, map the source fields from 0MAT_UNIT_ATTR to the UOM0MATE fields (Figure 10). Not all fields in the DataSource are mapped to UOM0MATE, just those fields are relevant. The relevant fields are Material, Unit of measure, Quantity - Counter, and Quantity - Denominator. Then you can activate the transformation and load the data via a data transfer process (DTP) and InfoPackage.
Figure 10
Mapping in the transformation from the DataSource 0MAT_UNIT_ATTR to the DSO UOM0MATE
After the data is loaded into UOM0MATE, you can establish the conversion to a set UoM. The system uses the values from UOM0MATE to provide the conversion factors.
To convert to a fixed UoM, it helps to have a key figure that is set to that UoM only. In my example, I need a key figure with the UoM of cartons (CAR). Key figure ZQTYCA represents the quantity in cartons and is set up with a fixed UoM of CAR in transaction RSD1 (Figure 11). I can then use this key figure inside the InfoCube to store the quantity in cartons. I use the UoM conversion to convert from the source UoM to the target UoM of cartons and store the value in this key figure.
Figure 11
ZQTYCA key figure in transaction RSD1 with the fixed unit of measure of CAR
You can now add key figure ZQTYCA to an InfoCube. In this case, the source data comes in using the base UoM. The system converts the UoM as data is loaded to cartons. During the data load, the system reads UOM0MATE to determine the conversion factor for the materials in the source data and convert the source key figure to cartons. In this scenario, you can have a different conversion factor to cartons for each material. The system reads each material and checks its conversion factor in UOM0MATE and applies the conversion factor accordingly.
The next step in the process is to establish another quantity conversion type. In this case the quantity conversion type tells the system not to look to table T006 for the factors, but instead use UOM0MATE to provide the conversion factors. You set up the quantity conversion type in transaction RSUOM (Figure 12).
Figure 12
Establish another quantity conversion type ZBASE_CAR using transaction RSUOM
The first screen of transaction RSUOM asks for a quantity conversion type name and description. The system then prompts for the conversion factor, which tells the system which table to use to find the conversion factor. In my example, I want to use UOM0MATE. Choose the option Using Reference InfoObject for the Dynamic Determination of Conversion Factor. This tells the system that the conversion will be dynamic based on an InfoObject. In my example, the reference InfoObject is 0MATERIAL because the system looks up the conversion factor from UOM0MATE and applies it to the conversion.
Now I need to tell the system to use this quantity conversion type to convert to a fixed UoM of CAR. Choose this target UoM in the UoM tab. The source data comes from the mapped value in the transformation. Therefore, I do not want to specify a set source UoM. To tell the system to use the source data as it comes into the transformation, I set the Source Unit of Measure to Unit of Measure from DataRecord. This means that the source is a dynamic UoM based on the mapped source field in the transformation.
The target unit of measure needs to be cartons, so I set the Fixed Unit of Measure to CAR (Figure 13). This allows the system to convert the dynamic value in the source data to the UoM of cartons. The system uses the values loaded in UOM0MATE based on the UoM conversion values for the specific material loaded.
Figure 13
Set the source UoM from DataRecord and the target UoM to CAR
I am now ready to use the quantity conversion type inside a transformation from the source key figure value to the key figure ZQTYCA I created (Figure 11). To do this, I must create a transformation from the source to the destination. As with all transformations, the source and destination can be DataSources, DSOs, or InfoCubes, for example. You can use UoM conversions inside each transformation.
While mapping the transformations, the source key figure is mapped to the destination key figure. If the source and destination values are quantities, the system allows you to enter a value in the Conversion Type field. In my example, I use the ZBASE_CAR quantity conversion type that I set up in Figures 12 and 13. I then map this to the target key figure value ZQTYCA, which has a fixed UoM of CAR mapped from a source value stored in 0QUANT_B (Figure 14). The system converts the values from the source to cartons based on the material master fields and quantity conversion values loaded into UOM0MATE. The quantity conversion type tells the system to use UOM0MATE (based on the 0MATERIAL in the source data) and lookup values in UOM0MATE (based on the source and target UoM values) to determine the factor to apply.
Figure 14
Transformation using the quantity conversion type ZBASE_CAR
Upon loading, the system performs the transformation based on the source data in 0QUANT_B and the UoM found in 0BASE_UoM to the UoM value of cartons. The system reads the value from UOM0MATE to apply the factor. If the system does not find an associated value to for the material in UOM0MATE, the load fails and the package is set to red. When you add the necessary value to UOM0MATE, you can reload the data and the load will be successful.
Conversion to a Dynamic UoM
Sometimes you need to convert quantity values to a dynamic UoM. This means that the target UoM is not a fixed UoM as in the previous example (in which we converted to a key figure that was hard-coded only to cartons). Sometimes the target key figure UoM is dynamic. The system must use that UoM as a target UoM and using the conversion values found in UOM0MATE. In this case, the target key figure value carries with it a UoM field (such as 0UNIT) that stores its UoM rather than a hard-coded value found in Figure 11.
To demonstrate converting to a dynamic UoM, I use the source quantity in the key figure 0QUANT_B and the target key figure is 0QUANTITY. The source value uses the base UoM found in the material master (0MATERIAL). The data is converted to 0QUANT_B, which uses the order unit (0PO_UNIT) from the material master. This means that the system takes the UoM from the source, looks to 0MATERIAL to determine the contents of the 0PO_UNIT field, and uses the contents of UOM0MATE to convert to that UoM.
To set up this scenario, you must first establish the quantity conversion type in transaction RSUOM. The Conversion Factors tab matches Figure 12. The reference InfoObject is 0MATERIAL. The UoM tab is shown in Figure 15. The quantity conversion type sets the source to the value from the DataRecord. This means it takes the UoM value from the source — in my example, 0QUANTITY — and its UoM of 0BASE_UOM. The target is set to use the 0MATERIAL InfoObject and the field 0PO_UNIT. The system dynamically looks at this field 0PO_UNIT from 0MATERIAL to determine the target UoM and provide the conversion.
Figure 15
Quantity conversion type to convert to 0PO_UNIT
Now that I have established the quantity conversion type, I am ready to establish the actual conversion within the transformation. In this transformation, I am mapping from the source 0QUANTITY to the target 0QUANT_B (Figure 16). The system uses the source UoM of 0BASE_UoM and convert to the target of 0UNIT using the 0PO_UNIT stored in the material master (0MATERIAL).
Figure 16
Transformation mapping from 0QUANT_B to 0QUANTITY using the quantity conversion type ZMATSLU
There are other scenarios that are possible with the SAP NetWeaver BW UoM conversion functionality. However, in my experience the three scenarios previously detailed cover more than 90% of all SAP NetWeaver BW UoM conversions within transformations.
