How to...implement HANA dynamic material UOM conversions

[justin_molenaur2]

First off, I have to recognize that this conversation was started (as always) in some blog comments with tim.korba2, who provided an approach that I tweaked and used in a current project. That's the best part about this community -  a majority of my "aha" moments have always come by collaborating with others or taking an interesting idea and building on it.

Creating Quantity Unit of Measure Conversion in HANA

Problem Statement: A given set of data needs to be converted dynamically according to user input, for example - "I want to see all quantities in CS (cases)". The existing UOM conversions provided with HANA only cover the most basic conversions like length, weight and volume. From an SAP data perspective, we are always interested in converting back and forth between the different UOM’s that a material may exist in, which could be EA, CS, PL, BG, BX, and on and on....

Functional overview

1. Get a QTY that is in the BUOM that you would like to convert to another UOM. In the case that your transaction data is not in BUOM, convert it before starting the following steps.
2. Determine if a conversion factor exists for the specific material and the target UOM.
3. If a conversion exists, use the conversion factor to calculate the correct quantity as associate the target UOM, else use the existing quantity and associate the BUOM.

HANA Implementation Overview

I chose to show this in a graphical calculation view since that's where I spend most of my time. Of course this can be applied to any other view with the same logic. tim.korba2 had started with an attribute view on an analytic view, which is the same train of though but implemented with a different artifact.

1. Create the base transaction data set. In this case, it is a union created from a number of calculation views, but this could be any dataset really. This data is from APO and is already in BUOM. The aggregation node simply rolls up the result of the union before any joins.

Sample transaction data at this step would look like this.

2. The first join serves to retrieve the BUOM using the material master (MATKEY in APO, MARA in ECC). Depending on application area you might already have this on the main dataset, in this case I did not. This BUOM will be used in case we have no matching entry from the UOM conversion. Since we know all materials must have an entry in material master, we model this an inner join.

Sample data at this step would look like this.

3. Retrieve all the conversions that are relevant for your target UOM. We use an input parameter to apply a filter on MEINH (alternative unit of measure) and also at the same time calculate a conversion factor we’ll use later on.

FACTOR formula

Input Parameter details - you could also use the MEINH column as a list of values or another table. I just made this direct to keep it simple.

MEINH Filter

An example of results from this branch of the calc view would look like this, assuming that the input parameter value was “CS”.

4. Using a Left Outer Join from the transaction data to the conversion data, we see if there is a conversion available. It’s possible there is no conversion for the specific material, so a LOJ is appropriate - and we’ll deal with cases where those are not found in the next steps.

At this point the data will look like this, assuming we have a match for the requested target UOM

5. Create two measures; one to cover the final UOM and one that cover the final qty. The Final UOM will be the target UOM if a conversion is found OR the original BUOM. The Final Qty will be qty x factor if a conversion is found, or the original quantity.

FINAL_UOM

If we don’t have a target conversion that the user was asking for (via input parameter), then we have to use the BUOM that we started with. So the result of this column will either be “EA” or the target of “CS” for example.

FINAL_QTY_SALES

This column will convert into the target unit if there was a target unit found, else we’ll revert to the original qty, the same logic used in deriving the UOM in the other calculated column. The key here is to make sure on the semantic tab we are associating this QTY with the UOM we derived in the previous calculated column.

Sample data here would look like this, we can see that the previous 16,000,000 EA was successfully converted to 16,000 CS based on a conversion rate of 100EA/1CS.

Testing the opposite scenario, where there is no target found (using “XYZ” as the target UOM), the following would be the result at the final aggregation node. Since there was no target found, we have to revert back to the BUOM we already knew.

As seen through a client tool like Analysis for Office, anytime we are mixing units, the aggregation will show an asterisk (*) since two unlike units can’t be aggregated. When you look at the material level however, you can see the qty and the unit associated with it.

Happy HANA,

Justin