Dynamics 365 F&O Data Lake as a TX source

Hi @rvgfox 

We currently do not not support ingesting common data models (CSV file with a separate JSON configuration file) such as that data lake exports from Dynamics 365. Please feel free to add an idea in the ideas section for this. 

As a workaround please try using the following data source

@Christian Hauggaard Thanks, this is the connector that currently I’m usign but it has problems with incremental data and data types (see my support tickets).

In this moment of TX will be better to use the Synapse Analytics data source.

Do you have some guidelines to configure it?

Hi Ramon

I know we were working on just that, I mean a data source that could connect to a F&O that was exported to a data lake. I do not know the current status of that though.

What I suppose you really want is a specific F&O data source outside CData that can connect to the online version of F&O, the OnPrem SQL based one and the Data Lake exported version.

Hi @Thomas Lind until it will be possible to connect directly to the Data Lake used by the export to data lake D365 F&O feature, I think that I can use TX to connect to Synapse Analytics that it will be used over the data lake to show tha data in a SQL serverless configuration.

I need a guideline to configure a serverless Synapse analytics connection.

Hi Ramon

Other customers/partners did this, I think, perhaps some of those can add a explanation of what they did.

To leverage the ADLS dump from D365 you would need to use CDMUtil to create views in Azure Synapse that load the data from ADLS and include the structure metadata to define fields etc. You can then set the Synapse views as a Data Source in ODX Server,

Thanks @rory.smith What type of Data source Synapse or SQL server must I configurate in TX?

If it’s SQL I know how do it, but not if it’s Synapse...

It should be Synapse, but there are quite a lot of fiddly bits. I expect you could also set up Synapse to push the view results to somewhere else, though at some performance cost.

@rory.smith if Synapse give us the data lake as SQL views (Serverless option), Not it would be better to use the TX SQL connector and not the Synapse connector?

@rvgfox Synapse is not exactly a regular SQL Server. As an example, primary keys do not exist. So I would always use the Synapse connector to avoid surprises. You should also be prepared for some spin-up time (and perhaps connection errors) due to the serverless nature of the resouce.

@rory.smith Did you tried the use of create new views using the vies that was creates by CDMUtil and conecct to them using a TX SQL connector?

I have not personally implemented D365 F&O using CDMUtil, but some of our customers do. I do not know what connector they use to connect to Synapse Serverless.

As far as I know connecting to Synapse Serverless using SQL connectors works as long as you do not take Synonyms across. For the 20.10.x release using BU you need to filter them out, I think it may work in the new release or with ODX Server automatically.

It is possible previews do not entirely work either, that was the case the last time I tried, but normal synchronize and transfer does work.

@Thomas Lind or @Christian Hauggaard I need your help to be able to see the data in the Data Lake.
With PBI I can see it directly, but I don't know how to configure the data source:

Thanks @JTreadwell this answer it’s all I need.