To homepage To homepage
Solved

Dynamics 365 F&O Data Lake as a TX source

  • 22 February 2023
  • 15 replies
  • 457 views
rvgfox
Rank
Userlevel 3
Badge +1

We are planning to use  Export to Azure Data Lake feature in D365 F&O

My question it’s:

How can connect to the Data Lake from TX?

Or, we can connect to Synapse Analytics?

How can we make the data source setup in TX?

icon

Best answer by JTreadwell 3 March 2023, 01:29

View original

15 replies

Christian Hauggaard
Rank
Userlevel 6
Badge +5

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

 

rvgfox
Rank
Userlevel 3
Badge +1

@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?

Thomas Lind
Rank
Userlevel 6
Badge +5

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.

rvgfox
Rank
Userlevel 3
Badge +1

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.

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi Ramon

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

rory.smith
Rank
Userlevel 5
Badge +7

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,

rvgfox
Rank
Userlevel 3
Badge +1

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...

rory.smith
Rank
Userlevel 5
Badge +7

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.

rvgfox
Rank
Userlevel 3
Badge +1

@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?

 

rory.smith
Rank
Userlevel 5
Badge +7

@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.

rvgfox
Rank
Userlevel 3
Badge +1

@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?

rory.smith
Rank
Userlevel 5
Badge +7

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.

rvgfox
Rank
Userlevel 3
Badge +1

@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:

 

 

JTreadwell
Rank
Userlevel 3
Badge +5

Hi @rvgfox:

The current best-practice method for ingesting D365 Data Lake export into TimeXtender is this: 

  1. Install Export to Azure Data Lake add-in - Finance & Operations
  2. Create Synapse Analytics Workspace
  3. Grant Synapse Analytics Workspace access to data lake
  4. Use the FastTrack CDMUtil to Auto-Create Views in Synapse Serverless for each table in F&O Data Lake
  5. Use the Standard SQL Server Data source (or SQL ADF) to connect to the Synapse Serverless On-Demand Endpoint and see views as tables. 

 

If you would like to connect directly to files in an Azure Datalake:

  1. Connect to Synapse Serverless Views using the TimeXtender SQL or ADF SQL Data sources. (We’ve found this to provide the best performance on large datasets)
  2. OR depending on the file format to connect to files directly:
    1. CData Parquet Data Source
    2. CData CSV Datasource 
-Joseph Treadwell
rvgfox
Rank
Userlevel 3
Badge +1

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

Reply


Terms and ConditionsCookie settings