Skip to Content
Technical Articles
Author's profile photo Sven Metzger

SAP Datasphere – Unit Conversion


Figure 1: Unit Conversion Example


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.



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",


 , "SOURCE_UNIT" =>"Unit"

 , "SCHEMA" => 'D049300' -- replace by your space


 , "ERROR_HANDLING"=>'fail on error'

 , "CLIENT" => '001') AS "ConvertedValue"


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”.


 , "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.


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.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Tim Huse
      Tim Huse

      Great Blog, Sven 🙂

      Author's profile photo Adem Güler
      Adem Güler

      Hello Sven,

      Thank you for your great blog.

      I would like to make an addition to the article you have written.
      (whether SQL or Graphical based view) When I want to create an Analytic Model from the view as deployed after the UNIT/CURRENCY CONVERSION implementation, it will encounter the error similar as in the below.

      The query executed with errors.
      Caught exception : exception 70000719: currency/unit conversion error: could not read base units from dimension table "YOUR_SPACE"."T006D" - insufficient privilege: Detailed info for this error can be found with guid '994778E3E5D5324B9CA5AF5F2B73EB98' at Authorization/insuffPriv/impl/InsufficientPrivilegeThrow.cpp:343


      What needs to be done to resolve this error:

      1. To use the CONVERT_UNIT function, the unit conversion tables T006 and T006D must be available in the SAP HANA database (or in other words, must be available in your Datasphere' SPACE).

      To use the CONVERT_CURRENCY function, the currency conversion tables TCURV, TCURX, TCURN, TCURR, TCURF and TCURC must be available in the SAP HANA database (or in other words, must be available in your Datasphere' SPACE).

      2. Wrap the views on top of the tables used for UNIT/CURRENCY CONVERSION and set to Expose for Consumption and Run in Analytical Mode for proper consumption. Ex: T006 as V_T006 and T006D as V_T006D.

      3. Declare the tables used in the UNIT CONVERSION function.

                   "SOURCE_UNIT" =>"RELATED_FIELD",
                   "SCHEMA" => 'YOUR_SCHEMA_ID',
                   "DIMENSION_TABLE" => 'V_T006D', --the table used for the conversion must be declared here
                   "RATES_TABLE" => 'V_T006', --the table used for the conversion must be declared here
                   "TARGET_UNIT" => 'KM',
                   "ERROR_HANDLING"=>'fail on error',
                   "CLIENT" => '100'

      Declare the tables used in the CURRENCY CONVERSION function.

          "AMOUNT" => "RELATED_FIELD", 
          "SOURCE_UNIT" => "RELATED_FIELD", 
          "TARGET_UNIT" => 'EUR', 
          "CONVERSION_TYPE" => 'M', 
          "CLIENT" => '100', 
          "SCHEMA" => 'YOUR_SCHEMA_ID', 
          "ERROR_HANDLING" => 'set_to_null', 
          "PRECISIONS_TABLE" => 'V_TCURX', --the table used for conversion must be declared here
          "CONFIGURATION_TABLE" => 'V_TCURV', --the table used for conversion must be declared here
          "PREFACTORS_TABLE" => 'V_TCURF', --the table used for conversion must be declared here
          "RATES_TABLE" => 'V_TCURR' --the table used for conversion must be declared here


      Best Regards.

      Author's profile photo Sven Metzger
      Sven Metzger
      Blog Post Author

      Hi Adem,

      many thanks for adding/sharing your experiences in detail.