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!