Inaccurate Decimal Data to On-Site SQL from Dataflow of Fabric Data Across Gateway

[khkhoo]

We are using D365 F&O and are pushing data to Fabric for reporting and visibility because Export to Data Lake is being discontinued. 

From Fabric we are using Dataflows to replicate tables to an on-site repository so it can be used by legacy reporting processes like Excel pivots. 

We've run into an interesting problem where data is being pushed to the on-site repository incorrectly. 

Specifically the D365 table would push to Fabric and use the data type Decimal for the column.

(Example: D365 Table generaljournalaccountentry using Decimal data type for column accountingcurrencyamount)

With the data type of Decimal it will drop trailing zeros. So if the first general journal account entry was for $10.00 it will display as 10 and with the second journal entry being for $175.60 it would display as 175.6. 

We have an on-prem gateway to our SQL server and the dataflow is setup to replicate the table to the on-prem server without data transforms. So on the Fabric layer column accountingcurrencyamount is Decimal(38,6),null) and the On-Prem copy of the table column is also Decimal(38,6),null)

However, because the first record of the table has dropped trailing zeros it will automatically truncate all the subsequent records to match which results in the data being incorrect. 

So on the Fabric side the first record is 10 and the second record is 175.6 while the On-Prem side the first record is 10 and the second record is 175. Because all decimals are truncating this results in several thousands of dollars of drift from actual general journal entries. 

I was able to resolve this in an ad hoc manner by creating a duplicate column in Fabric and using data transforms to convert the new column to the Currency data type. This forces Fabric to keep the trailing zeros and prevents the truncation to at least the 2nd decimal place. 

Is there a better way to address this issue? 

The additional transforms to squash this has eaten into our Fabric CUs and it is hard to detect when this occurs because it is reliant on the first record in D365 to not be an integer. 

[dlevy]

Hi @khkhoo - My first thought is that you shouldn't have to create a new column, you should be able to modify the column in the source to be your preferred decimal type.

This doesn't sound right in general though. It would be great if you could open a support case so we can have the engineers dig into this a bit and see where we are getting tripped up. 

Thanks,

Dave