question

AchatesChen-4307 avatar image
0 Votes"
AchatesChen-4307 asked AchatesChen-4307 commented

Why CDM inline dataset only get 2 digits fraction?

I am getting data by CDM inline dataset. I have some columns for cost which have fraction with decimals more that 2 digits. In infer drift column types setting, I tried decimal, float and double, but I always get 2 digits decimal only. How can I overcome this?

azure-data-factory
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What is the data type for the columns before you click infer/detect data type or import schema?

0 Votes 0 ·

In cdm.json, it is defined as below. I don't really know the logic how it is working, but I guess the type is decimal.
BTW, data is from Dynamics 365 F&O.

195780-image.png


195833-image.png


195809-image.png


0 Votes 0 ·
image.png (8.6 KiB)
image.png (8.6 KiB)
image.png (6.2 KiB)
MarkKromer-MSFT avatar image
0 Votes"
MarkKromer-MSFT answered AchatesChen-4307 commented

Rather than try to infer the type in the source, leave it as string and then cast it in a Derived Column using toDecimal()

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The problem is I have to use schema drift so it is impossible to pick up a data type for specific field.
Any possibility to modify the json? For what I can see, there is no way to modify.
195934-image.png


0 Votes 0 ·
image.png (2.0 KiB)

you can modify that script using the script button on the top-right corner of the data flow designer

0 Votes 0 ·

But change to what? As you can see in my screenshot, the script only provide option 'preferredFractionalType', and its option is just double, float or decimal. No difference as selection. Any other possible way of formatting?

0 Votes 0 ·

Hi Mark,
I do believe that current logic of CDM inline dataset with schema drift is not perfect. There is no reason to restrict the fractions to be only 2 digits. It should allow define more digits, or detect and apply more digits automatically. Will that be possible to achieve in future?
Currently I have to build single pipeline for those files which I want to have high precision. With that, I can change schema settings in json script.

0 Votes 0 ·
ShaikMaheer-MSFT avatar image
0 Votes"
ShaikMaheer-MSFT answered AchatesChen-4307 commented

Hi @AchatesChen-4307 , You can have plan to convert your data to string and then back to decimal. While converting decimal you use toDecimal() function to control how may fractional values to get after dot. Please check below screenshot for better idea.
198117-image.png

Please let us know how it goes. Thank you.


image.png (94.0 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

sorry for my late response.
I understand your suggestion. However, my problem is I am using schema drift, so there is no schema pre-defined, so no way to change anything in the data flow.

0 Votes 0 ·