4 Options for Using UoM Conversion During Query Runtime: Which Is Right for You?
- by Gary Nolan, SAP BI Strategic Architect, Sapiex Associates LLC
- October 29, 2010
Deciding where to apply the unit of measure (UoM) conversion logic in your BEx queries in your SAP NetWeaver BW 7.0 system requires the consideration of factors such as performance during query runtime. Discover four options you have for applying UoMs, from using a central table conversion with table T006 to enabling users to select the UoM variable during query runtime.
The use of unit of measure (UoM) conversion functionality inside BEx queries allows for conversion of UoMs during query runtime. Quantity UOM conversion functionality is included as a feature in SAP NetWeaver BW 7.0. The conversion of quantities can occur in the back end with the converted values stored with the source transactional data in an InfoProvider. Alternatively, you can keep the converted values in the source UoM, with the full UoM conversion occurring during the query runtime.
The main concern with conversion during query processing can be the significant processing that must occur during query rendering. In a typical unit of measure (UoM) conversion, the system must look at the data at a very granular material level, determine the conversion factors, and apply it to the data. Therefore, this can be a substantial performance concern, depending on the volume of data. As a general rule, if you have more than one million rows of data in an InfoProvider or if the query requested will bring back several hundred thousand records, UoM conversion during runtime could significantly affect performance.
If substantial data volume is anticipated, moving the UoM conversion to the back end and performing UoM conversion while loading data into the InfoProvider might aid performance. However, shifting the UoM conversion to loading time lends itself to potential issues with restatement of data if the UoM conversion factors for materials are dynamic. Determining which place to apply the UoM conversion logic requires weighing the restatement concern against the performance cost during query runtime.
In this article I will concentrate only on the UoM conversions during query runtime. There are several common scenarios that are typically used in UoM conversions at query runtime:
- Conversion using UoM central 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.
- Conversion to a fixed UoM: Convert quantities to a UoM (e.g., cases) during query runtime. This UoM is static and specified in the UoM conversion type record and included in the query.
- Specify a UoM during query runtime: Convert to a fixed UoM specified in the key figure during query creation
- Variable entry of UoM: User variable entry of the target UoM during query runtime
Choosing among the scenarios listed depends on the presentation needs of the query. I will walk through the setup of all four scenarios.
Would you like to see this full item?