Getting Data From AX 2012 R3 To Enterprise Data Warehouse

(0) Share

Report

Posted on by Sefa Duman

1,167

Hi all,

We are on implementing Dynamics AX 2012 R3 in a retail company. Our existing data warehouse is well designed and based on SQL Server 2014. It meets the business units' requirements widely and get the data from other source systems like PLM, e-commerce, retail , POS and etc. We want to keep the DW on.

Now we are working on ETL process that extract the data from AX and load into DW. What is the best way for extracting data including metadata that is defined in AX application, not in database? Of course we can use SSIS packages by creating data flow task with direct SQL query within it. But Numerous metadata is defined in AX application, not in database, so it is really hard to get data just by looking into database.

Is there any AX tool or application that get data including AX business logic in standart AX?

Thanks in advance.

Sefa.

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (0)

Guy Terry 28,924 Moderator on at

Like (0)

Report

Hi Sefa,

The standard AX cubes use Views. There is no reason why you couldn't also use them, to get at the data in a more BI friendly format. You can get more information from here:

technet.microsoft.com/.../jj710394.aspx

This is also a good time to mention new 'Entity store' functionality which has been back-ported to AX 2012 R3. This blog post contains more details, and links to the Microsoft documentation:

dynamicsnavax.blogspot.co.uk/.../ax-2012-r3-entity-store.html

Was this reply helpful? Yes No
Sefa Duman 1,167 on at

Like (0)

Report

Hi Guy,

I intended to use views but I am not sure if it is adequate for AX business logic completely. In addition a lot of data is presented on display methods. Is there any way to query display methods out of the AX?

As for entity store, I read the whitepaper. As far as I understand, entity store has its own DWH. Can it be used for ETL process to another DWH too?

And what about DIXF? Is it only used for data migration between AX enviroments or must the destination AX database? Can it be used for loading data to SQL based DWH?

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

Like (0)

Report

What do you mean by getting business logic into data warehouse? Data warehouse is clearly about data.

Do you mean executing AX business logic to prepare data for the warehouse?

Was this reply helpful? Yes No
Sefa Duman 1,167 on at

Like (0)

Report

I mean the data that presented on display methods, the data joined multiple tables in AX query, the data that a field in a form but calculating from different tables. etc. And I mean making denormalize process easier for DWH.

For example there is a field named receivedInTotal in PurchLine table in DWH. You can see this data in Purchase orders > Line Quantity form. but if you query this data from SQL you have to write a query like this:

SELECT PurchId,LineNumber, SUM(Qty) FROM VendPackingSlipTrans WHERE PurchLine.InventTransId=VendPackingSlipTrans.InventTransId GROUP BY PurchId,LineNumber

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

Like (0)

Report

All right, so you want to execute AX business to prepare data that you'll put into your data warehouse. For that, you obviously need AX, because you can't execute such code anywhere else. You can do it in many ways - for example, you can have batch jobs inside AX to run calculations and store data somewhere in database, push them to another database or so. Or could expose the logic externally, e.g. through a custom service, and call it from another application on demand.

You'll likely want to consider the performance impact on your OLTP database, therefore you may end up, for example, reading the data from the entity store anyway.

DIXF is a general purpose tool; the target can be (among other things) any database accessed through ODBC.

Was this reply helpful? Yes No
Vilmos Kintera 46,149 on at

Like (0)

Report

You could use AX Application Integration Framework (AIF) to expose the data in any way you want including X++ "business logic". In the data contract class you may refer and/or replicate any display methods as well which are stored on the table in question, or you could add the display method from the form straight to the contract class.

Then you could consume it as a WSDL/WCF web service to pull the data out easily. This is also a solution you could consider other than the tools mentioned above.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I'm currently in the same stage as you. I'm extracting Procurement, Sales, Finance, etc...out of AX and into Oracle. Please let me which process you used to extract the Business Process and ready to export.

Any other links and references would be helpful, including table definitions

Was this reply helpful? Yes No
Jakob Bjerg-Heise on at

Like (0)

Report

I would look at www.bibuilder4dynamics.com.

It automates a standard BI build process using metadata from D365 or AX2012.

Was this reply helpful? Yes No