Creating Quantity Unit of Measure Conversion in HANA
One of the areas that still has not been handled correctly in any of the versions of HANA is leveraging MARM for the UOM conversion. Since you can use out of the box conversion functionality, you need to use a few options in order to make this work correctly. On a recent project, we went around with a few options but decided on the following:
1) Create an attribute view against the MARM table and join it with T006A in order to get the external unit of measure. This should be an inner join with the T006A table as this will be tied to the external UOM. It will restrict with the conversion method that you would like to tie to
3) Create a calculation measure (within the attribute view) that will give you the BUOM conversion rate – this will be used for all QTY UOM key figures that you would like to convert. Please note that you may not receive anything if there is not a conversion here. In this case, we will consider this downstream in later views and tie it to a conversion = 1. This will use UMREN and UMREZ.
3) join this attribute view with each of the analytic views that you create (within the Logical Join)
4) Create all of the key figure calculations by multiplying by the BUOM conversion created in Step 3
5) Create an input parameter that has a list of UOM that your company will want to convert on. Please note that this will be tied to the external unit of measure since the internal value in the table may be the German translation (i.e. KI vs. CS)
6) Assign this input parameter to the attribute view external UOM as $$<input parameter name>$$ – This will not be something that you can select but it will work as is
7) Create calculation view against the analytic view (please note that I always use the process that there will be a calculation view on top of all analytic views that reportable – this allows us to follow the SAP HANA Live standards as well)
8) Create a calculated Attribute that will be populated with the external unit of measure and will be used in the Semantic Layer
9) Assign the Display UOM calculated attribute to the Semantic Layer
10) Create the same input parameter for UOM and then manage the mappings between the Calculation View and the Analytic View
What we found out that using the Semantic Layer limits the data after ~20k rows. We worked directly with SAP and found that Analysis for Office version 1.4.9 and greater the issue is raised but 1.4.8 and less, the issue does not exist. If you want more information, please message me on this. There is a fix that will be released on May 7, 2015 that will resolve this issue.
A workaround for the above is the following:
- Don’t use these concepts if you are on 1.4.9 or greater until SAP releases the new AO version
- Hard code the Semantic Layer and don’t allow users to enter the values within the selection screen – If there is not a conversion there is a risk that the end user will be reviewing values that are not correct
- Downgrade to an earlier version until option 1 is available.
Please continue to read my blog, add comments, follow me on twitter @tim_korba and add me on linked in. Thanks so much for reading and I hope this helps!
Tim, nice write up to a common problem with no "standard" solution.
However I find this slightly hard to follow with some details on each step somewhat vague. I had achieved some similar functionality another way, but just curious on the following points as I read through your approach.
1) Re: Step 3.1 - How are you specifically handling the cases where the Alternative Unit of measure is not available? Since you are placing a selection on the attribute view for a specific external UOM, and it's an inner join - if there is no conversion present in MARM for that specific material it will just be dropped from the data set. Therefore, a given set that previously had 10 materials, will now only show 9 if one of the materials don't have an entry in MARM for the UOM the user is requesting.
2) In step 3.2, what is the specific join you are referring to?
3) The MARM table only contains conversions that have base unit of measure as part of the formula (ie getting to base UOM from another UOM, and getting to another UOM from base UOM). So if you have application data that is in something other then base UOM (or target UOM from base) and you are converting to a UOM that other than base (or target from base) you will get incorrect results.
MARM
Material MEINH UMREZ UMREN
A EA 1 1
A CS 12 1
A BX 6 1
Transaction Data
Material UOM Qty Req. UOM Conv. Qty
A EA 12 CS 1
A BX 4 CS 48
Based on the above, if your transaction data is in "BX", you cannot just choose "CS" as this will show (12EA/1CS) x 4.
Maybe I'm missing something here, so I'll look for clarification as a dynamic solution would be ideal.
Regards,
Justin
Justin,
These are great questions and I am sure my answers will not be what you want to hear but let me explain further:
1) You are correct if there isn't an alternate uom, then it will return nothing for this join and what do you do at this point. What I have been doing is using 1 as a default value. This is not ideal but at the end of the day, unlike BW, it should allow you to put it into the Base UOM that does exist for this material. Unfortunately this solution does have limitations and the business needs to identify these limitations and update the alternate UOM further. This is one of the biggest issues that we have seen. The downfall with this is that it still shows the UOM that you requested and the * 1 doesn't necessarily mean that. I told you that you wouldn't like this answer but it is a limitation.
2) All I mean here is for every analytic view, this attribute view that you created will be part of it. I make this a standard for every new one that is created as well.
3) Correct and in order to get this correct, you have to make sure that you have a BUOM to use. If you do not, then you have to create a secondary calculated measure that will get you to this level as a step 1 and then within the CV or as a secondary calculated measure execute the transaction against the BUOM. This is normal though and even in the BW world, you would have to make sure that you have the correct BUOM.
I hope that this further helps and sorry for the confusion. It really is a large problem that has surprisingly not been resolved by SAP yet. But that must mean that it hasn't been something that was easy to resolve in basic views. What they need to do is inherit the BW logic that exists.
Justin,
I have been thinking about this further and I wanted to clarify 1 area that I misstated in my initial response and 1 area that I believe is a good solution to your question:
1) When the conversion does not exist for that UOM, the display UOM, which is a calculated attribute will be null. With this said, the UOM will be NULL and it will show * if you are not at the material level. THis will highlight the issue with that material.
2) As a possible solution, within the calculated attribute that will be the display UOM, you can validate that the UOM IS NOT NULL and if it is, then use the BUOM or the other UOM that is on the transaction. This will then act as expected
Hope this is additionally helpful and I will test this theory out over the next few days.
Thanks for the thoughtful responses Tim. I think my problem is that I am having some trouble visualizing your approach for some of the steps, I will attempt to outline questions here.
3 - you mention that the T006A and MARM attribute view is an inner join, but weren't specific on the join within the analytic view. Based on your last response (point 1), if you are getting null values I have to assume that this is a left outer within the analytic view.
5/6 - You just mean that you create an IP with assignment to column T006A-MSEH3 with the parameter type-column? You mention "this is not something you can select", what do you mean here?
8/9 - I don't really follow here - why is a calculated attribute required? And when you say "Assign the Display UOM calculated attribute to the Semantic Layer", are you saying assigning the UOM through the semantics tab of the calculated column (Quantity with Unit of Measure) or do you mean something else?
In your previous response, point 1, you mention showing "*", what do you mean here? Is this a specific reference to AAO?
It may be helpful to add more details/screenshots to your original blog.
Regards,
Justin
Justin,
Let me put together more screen shots and update the blog. This will provide more direction and allow you and I to continue the conversation. Thanks for the feedback.
Tim
Cool, just want to make sure we're on the same page. I am keen to help refine a good technique for this and I have the perfect application on a real project!
Regards,
Justin
I did get a working solution here based on some of your feedback. I'll post the details tomorrow, works like a charm.
Regards,
Justin
Hi Tim, I went ahead and documented the approach I used here.
http://scn.sap.com/community/hana-in-memory/blog/2015/04/03/how-tomake-uom-conversions-dynamic
Thanks for the initial thoughts on this, this was a cool exercise and is already a big hit with the end users on my project compared to their previous solutions!
Happy HANA,
Justin
Great updates Justin, pleasure have the working session with you.