How to Fix commas(,) being read as decimal(.) on ADF while reading from SAP BW via MDX

Question

How to Fix commas(,) being read as decimal(.) on ADF while reading from SAP BW via MDX

Raghav Haralalka - sID 0

While doing a preview data from SAP BW source, the commas (,) are being considered as decimal(.). However, on the SAP Analyzer through excel this isn't happening.

Example, 1,995 on SAP gets converted to 1.995 on ADF.

Smaran Thoomu 25,100 Reputation points Microsoft External Staff Moderator

2025-05-05T12:01:25.7633333+00:00
Hi @Raghav Haralalka - sID
The issue you're facing—where commas (,) are being interpreted as decimal points (.) in Azure Data Factory (ADF) while reading from SAP BW via MDX - is typically related to locale or number format settings during data retrieval or parsing.

Root Cause

ADF treats incoming numeric data based on standard en-US number formatting by default, where:

. is used as the decimal separator.

, is treated as a thousand separator and often ignored during parsing.

In your case, it seems that values like 1,995 (which should be "one thousand nine hundred ninety-five") are being misinterpreted as 1.995 (one point nine nine five).

This discrepancy likely arises because:

ADF is interpreting the number as a string and automatically converting it based on default culture settings.

SAP BW might be sending the data as a formatted string rather than a numeric type, especially if you're using MDX queries.

Suggested Workarounds

Inspect the actual data type in SAP BW

If the field is formatted as a string in SAP BW, ADF may misinterpret the comma.

If possible, modify the MDX query or SAP side configuration to return raw numeric values instead of formatted strings.

Use a Derived Column or Data Flow to Clean Format

If you're using ADF Mapping Data Flows:

Add a Derived Column transformation to replace commas with empty strings, or parse the value explicitly. Example:

replace(columnName, ',', '')

Or, if it's supposed to be a whole number:

toInteger(replace(columnName, ',', ''))

Locale Handling in Data Source

Currently, ADF doesn't expose locale or culture settings directly in the SAP BW connector.

As a workaround, you could export the data to a staging area (e.g., Azure Blob or Data Lake) and then process the formatting using a Data Flow or a custom transformation.

If this issue appears consistently for certain fields, consider reviewing the SAP BW InfoObject formatting to confirm whether the thousand separator is applied at the metadata level.

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.
Smaran Thoomu 25,100 Reputation points Microsoft External Staff Moderator

2025-05-06T12:37:04.82+00:00

@Raghav Haralalka - sID We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

1 answer

Your answer

Smaran Thoomu 25,100 Reputation points Microsoft External Staff Moderator

2025-05-06T12:37:04.82+00:00

@Raghav Haralalka - sID We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Answer 1

Hello Raghav Haralalka - sID,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand that you would like to know how you can fix commas (,) being read as decimal(.) on ADF while reading from SAP BW via MDX.

Since the SAP BW MDX connector in Azure Data Factory (ADF) retrieves data as parsed numeric values based on SAP's internal formatting and the system user's locale.

Adjust SAP BW User Locale Settings The number formatting is determined by the user account used by ADF to connect to SAP BW. To adjust this:
1. Access SAP GUI:
  - Navigate to the SU01 transaction.
  - Enter the user ID used for the ADF connection.
2. Set Decimal Notation:
  - Under the Defaults tab, set Decimal Notation to X 1,234,567.89 (dot as decimal, comma as thousands) or to the appropriate format like Y 1.234.567,89 if required by ADF. - https://community.sap.com/t5/technology-q-a/how-to-change-decimal-places/qaq-p/1885508
You can validate formatting in MDX Preview Tool using Test MDX Query:
- Use SAP transaction RSMDX or MDXTEST to test the MDX query used by ADF.
- Confirm that the returned numbers have the correct separators. - https://community.sap.com/t5/technology-q-a/global-setting-for-decimal-notation-setting/qaq-p/5085634
This is a Post-Ingestion Type Control in ADF, if the SAP formatting cannot be changed, you can adjust the data after ingestion:
- Use Mapping Data Flow, in ADF, use a Derived Column transformation: replace(replace(toString(ColumnName), '.', ''), ',', '.') → toDouble()
- This assumes . is the thousands separator and , is the decimal separator - https://sapported.com/tutorials/technical/basis/how-to-change-decimals-notation-in-sap-using-transaction-su01/
File-Based Export Alternative is another means if MDX proves unmanageable, export data from SAP BW to a CSV file:
- Export Data: Use Open Hub or direct DSO extraction to export data to a CSV file.
- Control Formatting: Use a DelimitedText dataset in ADF to control decimal and thousand separator settings via dataset culture - https://community.sap.com/t5/technology-q-a/how-to-manage-decimal-notation-based-on-user-profile-in-sac-like-sap/qaq-p/13750240

Those links give more details and steps to the option available.

I hope this is helpful! Do not hesitate to let me know if you have any other questions or clarifications.

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.

Smaran Thoomu 25,100 Reputation points Microsoft External Staff Moderator

2025-05-07T09:24:02.0566667+00:00

@Raghav Haralalka - sID Just checking in to see if the above answer provided by @Sina Salam helped.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Share via

How to Fix commas(,) being read as decimal(.) on ADF while reading from SAP BW via MDX

1 answer

Your answer