Preferred option to extract data from Dynamics AX

(0) Share

Report

Posted on by Community Member

Friends, we are exploring options to extract data from Dynamics AX (on Azure) to on-premise downstream BI application. The said BI application is on Netezza database and we use a combination of ETL & CDC options to extract data from other source systems.

We are looking for best possible approach to connect to Dynamics AX and extract the data in batch (one/twice a day extracts) and near-real-time.

Any suggestions/recommendations are high appreciated!

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

Suggested answer

Mea_ 60,284 on at

Like (0)

Report

Hi Deepak.Suryaprakash,

On of the options is to export entities to your own DB

blogs.msdn.microsoft.com/.../export-dynamics-ax7-entities-to-your-own-azure-sql-database its developed exactly for BI purposes.

Was this reply helpful? Yes No
Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

There is no single best solution; you really need to evaluate your needs and pick the right tool for your particular task. You mentioned the frequency, which is one important aspect, but it also depends on how much data you need, whether you want to utilize change tracking, how your "near-real-time" solution will be triggered or scheduled, where the target system resides and so on.

It seems that you have two different scenarios and therefore you'll likely use two different tools.

For export data in batch, you can use the data import export framework in various ways (a batch, recurring integration, Bring Your Own Database or so). But it's not the only possibility, e.g. you could use OData services, if you don't need too much data.

The "near-real-time" sounds more like a candidate for some integration option, but details require better analysis.

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

You have selected Current version in your tags but use old AX naming, I will assume you are talking about Dynamics 365 for Operations. The D365fO database for a Production instance is not exposed, so you cannot use anything that would be a direct interaction like ODBC.

You have to use Dynamics 365 for Operations itself to publish such data, for which you have a lot of tools. If you want to "push" data, the Data Import Export Framework is the tool of your choice. Look into the following documentation, also navigate up and down in the same group on the left to get more details:

docs.microsoft.com/.../data-management-integration-data-entity

If you want to "pull" data, then you have to look into the service integration endpoints that D365fO offers.

docs.microsoft.com/.../services-home-page

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks a lot ievgen Miroshnikov. I went through the blog, very useful for our requirement. I will get back if any more details are to be clarified.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Martin Dráb for your response. I am exploring data import export framework with BYODB and incremental push option (export changed records only).

Can we plan BYODB with a local SQL server?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Vilmos Kintera, thanks for your response. Implementation of Dynamics AX is not yet complete, it is planned for later this month, so the version in question here is the current Dynamics AX on Cloud (Dynamics AX 2016).

I am exploring incremental push option in Data Import Export Framework.

Was this reply helpful? Yes No
Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

If you're talking about a production environment hosted in Azure, your database must be in SQL Azure too.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Would it work only with Azure SQL database or a SQL Server on a Azure VM will do?

Thanks for the clarification!

Was this reply helpful? Yes No
Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

As I said, it must be SQL Azure. SQL Server isn't SQL Azure.

Was this reply helpful? Yes No