- SAP Managed Tags:
Figure 1: Unit Conversion Example
Introduction
SAP Datasphere is a powerful platform when it comes to extensive data management and analytics scenarios. Technically, a lot is feasible, even if not at first glance. In this blog I want to show you how to extend your skillset using SAP HANA Cloud.
To do so, I would like to showcase a common example of logistics – Unit Conversion.
Unit Conversion
Unit conversion is paramount in the field of data warehousing as it is important in ensuring consistency, accuracy and effective data analysis. In the data context, units can refer to different scales, formats, or measurements applied to different data points. By converting data into a standardized entity, organizations can achieve the following exemplary benefits:
- Consistency and Comparability: Data from diverse sources often come with varying units, making meaningful comparison challenging. Unit conversion harmonizes these variations, enabling accurate analysis and insights across datasets.
- Accurate Insights: Different units can skew analytical results. Unit conversion guarantees that data aligns with the intended analysis, providing accurate insights and informed decision-making.
- Enhanced Reporting: Reporting is more effective when data is presented in consistent units. Unit conversion simplifies reporting processes, resulting in clear, standardized, and actionable reports.
- Effective Integration: When integrating data from disparate systems, differences in units can hinder seamless integration. Converting data to a common unit streamlines integration processes and reduces errors.
- Data Quality: Inaccurate units can lead to poor data quality. Unit conversion contributes to data accuracy, reliability, and overall data quality improvement.
- Future Scalability: As organizations grow and gather more data, ensuring uniform units sets the foundation for scalability and avoids complications as datasets expand.
Basics
Before we turn to the example, I would like to briefly go into a few general details.
In terms of architecture, each Datasphere cluster is tightly integrated with a SAP HANA Cloud database as its technical backbone. As a result, end users benefit from a wide range of HANA features such as SAP HANA SQL - a query language designed specifically for the SAP HANA in-memory database. It allows users to interact with data stored in HANA by writing SQL statements. In a nutshell it enables efficient data retrieval, manipulation and analysis, making it a powerful tool for real-time data processing and decision-making.
Unit conversion in SAP S/4 HANA and SAP ERP are based on the SAP Basis tables T006 (units of measurement) & T006D (dimensions). All information required for translating one unit into another unit are stored in them. SAP HANA Cloud in turn provides function CONVERT_UNIT that leverages these tables to do the conversion within any given domain. As an example, within dimension LENGTH, the respective factors for translating between kilometers, feet, nanometers, yards etc. are used to by CONVERT_UNIT to allow conversion between any two LENGTH units. We’ll use the function from Datasphere’s graphical and SQL Views to do the conversion for us.
All of the content below assumes that tables T006 & T006D are present in your system. If not, you’ll need to integrate both first.
Figure 2: Data Viewer T006 (Units of Measurement)
So far so good – let’s get our hands dirty.
Functional Example using SQL-View
First, we create a simple local table with data that we later want to convert unit-wise.
1. Create a Local Table “LT_WAREHOUSE_A” via Data Builder.
2. Add the three columns Product, Quantity and Unit.
3. Save.
Figure 3: Creation of Local Table LT_WAREHOUSE_A
4. Switch to the Data Editor and add exemplary row items. Let’s use LB (pound) as base unit for all our records, but note that not all records need to be of the same unit.
5. Save the items and deploy the table.
Figure 4: Add exemplary row items
In the following we want to convert our source unit “LB” (pound) to “KG” (Kilogram). Note that all abbreviations for unit names need to correspond to the abbreviations used in table T006 (Units of Measurement).
6. Create a SQL-View “HSQL_CONVERT_UNIT” via Data Builder.
7. Insert the HANA SQL Function “CONVERT UNIT” into the SQL Script (see SAP Help Documentation).
SELECT "Quantity", "Unit",
CONVERT_UNIT("QUANTITY"=>"Quantity"
, "SOURCE_UNIT" =>"Unit"
, "SCHEMA" => 'D049300' -- replace by your space
, "TARGET_UNIT" => 'KG'
, "ERROR_HANDLING"=>'fail on error'
, "CLIENT" => '001') AS "ConvertedValue"
FROM "LT_WAREHOUSE_A"All we have to do is to maintain the source fields from the table. The conversion factor is recognized and determined by the “CONVERT_UNIT” function.
Figure 5: Create SQL-View with CONVERT_UNIT Function
As you can see, the final conversion will be written into “ConvertedValue” column. Result looks great - we have successfully completed our first unit conversion from “LB” to “KG”.
Example using Graphical-View
Imagine a company that operates in multiple countries with warehouses in different locations. Each warehouse stores products with varying units of measurement for the same item.
For this case, a second warehouse table “LT_WAREHOUSE_B” is created and deployed. Attention this warehouse is from different location, hence it doesn’t use “LB” (Pound) as unit. Instead, it uses “KG” (Kilogram).
Figure 6: Creation of Local Table LT_WAREHOUSE_B
In a next step, we want to union both warehouses, but be careful - we need to ensure a common unit.
1. Create Graphical View “V_INVENTORY_MANAGEMENT”.
2. Add calculated column to “LT_WAREHOUSE_A”. Insert the CONVERT_UNIT function into the expression field of column “Quantity”.
CONVERT_UNIT("QUANTITY"=>"Quantity"
, "SOURCE_UNIT" =>"Unit"
, "SCHEMA" => 'D049300'
, "TARGET_UNIT" => 'KG'
, "ERROR_HANDLING"=>'fail on error'
, "CLIENT" => '001')Figure 7: Calculated Column “Quantity”
For completeness, we also add a brief expression to column “Unit”, to get it as “KG”.
Figure 8: Calculated Column “Unit”
3. Create Union of “LT_WAREHOUSE_A” and “LT_WAREHOUSE_B”.
Figure 9: Data Viewer after Union both tables
4. Apply Aggregation “SUM” on Column “Quantity”.
Figure 10: SUM Aggregation on Column “Quantity”
Voilà, now we have an aggregation over the products of both Warehouse tables with common unit kilogram. The Graphical View can now be used for reporting.
Conclusion
This blog gave you a brief but powerful introduction about unit conversions and HANA SQL use cases.
Thanks for reading! I hope this blog helps you move forward. For any questions or feedback just leave a comment below this post.
Special thanks to jan.fetzer for the collaboration on this blog post.
Find more information and related blog posts on the topic page for SAP Datasphere.
If you have questions about SAP Analytics Cloud you can submit them in the Q&A area for SAP Datasphere in the SAP Community.
3 Comments
