In Dynamics 365 Finance and Operations Apps, the Export to data lake feature lets you copy data and metadata from your Finance and Operations apps into your own data lake (Azure Data Lake Storage Gen2). Data that is stored in the data lake is organized in a folder structure.

The process of the installation of the Export to Azure Data Lake add-in is well described in this Microsoft documentation. Additional instructions for deploying the CDMUtil Azure function can be found here.

In this article, I would like to focus on some of the issues that you may encounter when using Azure Synapse Serverless SQL endpoint in Power BI and SQL Server Management Studio (SSMS).

1. String or Binary Data Truncation Issue in Synapse View

You may encounter the following error when querying the data in the Synapse view:

String or binary data would be truncated while reading column of type 'NVARCHAR'. Check ANSI_WARNINGS option. File/External table name 'Tables/ProfessionalServices/ProjectManagementAndAccounting/Miscellaneous/ProjPostedTransTable/PROJPOSTEDTRANSTABLE_00001.csv', column 'TXT'

If we look at how metadata is stored in the data lake, we can see that it has only a data format, without specifying the length of the field.

Because of that CDMUtil Azure function creates ALL string fields in the Synapse view with the same length – 100 symbols. So, if the corresponding field in the Dynamics database stories the field with more than 100 symbols you will get this error.

The workaround for this problem: we can create a new view manually with a different name and the correct length of the field (if you just alter the existing view, CDMUtil will overwrite it after a while, and you get the same problem again)

2. Creating a New View in the Synapse with AXDB Database

You can get a definition of any View or Entity from AXDB database and use it to create a new View in the Synapse. For example, you can create “LOGISTICSPOSTALADDRESSBASEENTITY” with the script below (you just need to make sure that all tables that are used in this entity are included in the Export to data lake)

But it can be tricky when you try to create Financial dimensions-related entities. For example, if you try to query “DIMENSIONSETENTITY” you will get the following error:

The query references an object that is not supported in distributed processing mode.

The reason for this error is that the view definition uses functions that cannot be used when querying data in Azure Synapse SQL. The full list of supported function can be found here.

As a workaround for this issue, you can remove the “DISPLAYVALUE” field and add separate fields for each financial dimension. But keep in mind that in this case, you need change all the other entities that use the removed field.

3. Joining Two (or more) Tables in Power BI Datasets

When you build a dataset in Power BI, there is often a need to join two (or more) tables together. Relationships in Power BI are limited to single columns, and this is a serious limitation for Dynamics since most of the relations have at least 3 columns (because of the system fields “DATAAREAID” and “PARTITION” that are used to split the data between different companies).

To workaround this limitation you can create new views that will combine several fields used in the relation into one and use them to build a dataset.

Or simply create a combined view:

There you have it! For assistance with Azure Synapse Analytics, Azure Data Lake, and Dynamics 365 F&O, reach out to Turnkey Technologies for a consultation.