web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Getting data out of Dy...
Microsoft Dynamics AX (Archived)

Getting data out of Dynamics AX

(0) ShareShare
ReportReport
Posted on by Dr. O. 160

Who can advise on best approach to get data extracts out of Dynamics AX so a legacy historical data warehouse can continue to function to produce reporting deliverables that will be based on new ERP data and old ERP data?

*This post is locked for comments

I have the same question (0)
  • Dr. O. Profile Picture
    Dr. O. 160 on at

    Can someone please give some advice?

  • Martin Dráb Profile Picture
    Martin Dráb 237,990 Most Valuable Professional on at

    Unfortunately your question is too wide to answer properly. There are many options, like AIF (inc. BizTalk), Business Connector or direct connection to OLTP or OLAP database, but the best solution depends on your requirements and limitations.

    It would be helpful to know what version of AX do you use, which data do you need, what are capabilities of the warehouse, how often do you need to update it (e.g. real-time vs. night batches) and so on.

  • Dr. O. Profile Picture
    Dr. O. 160 on at

    We plan on implementing 2012 Dynamics AX later this year.  Our existing data warehouse produces key deliverables, SSRS reports and cubes, to manage the business by.  We do lots of time comparisions and need the historical data from the old ERP that is already loaded to our data warehouse.  We need all data from the new ERP related to sales, inventory, customers, products, vendors, orders, etc.. Nightly batch runs would be the priority for getting data out since most of the daily operational reports can be produced via Dynamics itself.

    I know I am not the first Microsoft customer going down the road of cutting over to a new ERP that requires data to be integated with old EPR/data warehouse data.  So the basic requirement is to get data out of the new ERP's database so we can load it to appropriate data warehouse staging tables where it can be further integrated with other data types.  What is AIF?  Thanks.

  • Martin Dráb Profile Picture
    Martin Dráb 237,990 Most Valuable Professional on at

    It seems that your data warehouse is actually based on SQL Server, right? That may simplify few things.

    AIF is Application Integration Framework, but if you want copy data between SQL Server instances, you probably don't need such level or interoperability (it's not for free, you would pay by performance, for example).

    You say that "the basic requirement is to get data out of the new ERP's database", but that's obviously trivial with SQL Server. I think two other points are critical:

    1) To know what data to take. A lot of metadata is defined in AX application, not in database, so you may struggle if getting data you need just by looking into DB. By the way, this something where Intelligent Data Management Framework may be useful.

    2) Business logic in AX. It's easy to extract data from AX, but you may want some calculations performed by AX application. If this is required, you'll need something capable to run AX logic - it may be a simple AX batch writing data to some helper tables in AX DB, or something more fancy like a web service used directly by your data warehouse.

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    Another potential option for you would be to use Connector for Microsoft Dynamics.  This would allow you to build the appropriate query services for the data that you want, and leverage an extensible framework to build an adapter to feed your data warehouse.  Depending on the data you want, some of the adapters/queries likely already exist - so this could reduce the amount of work you would need to do to accomplish your business requirements.

    It also is less brittle than straight SQL table to table mapping, since the AX adapter is moving data through services and Connector has a transform engine.  It could potentially insulate you from upgrades on the AX side.

    More information here: blogs.msdn.com/.../dynamicsconnector

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    You can use SSIS (SQL Server Integration Services) or any other ETL (Extract Transfor Load) or Data Integration tool. In theory you can use also Web Services to do that using a subscription model, though it depends what you need. In most of the cases SSIS would do, and it's actually the tool I would recommend. On the other side depends also what database supports your DWH (Data Warehouse) and your architectural strategy. If it's Oracle then you can go maybe also for OWB (Oracle Warehouse Builder) or similar Oracle tools. When choosing the tools it's important to consider the costs and various constraints, including compatibility issues that might arise by not using adequate tools.

    More likely you might need to redesign your DWH or maybe buy an existing solution. You have the choice of purchasing a COTS (Commercial Off-The-Shelf)  solution that includes Reporting and ETL functionality, though this approach can have many important constraints related to tool’s extensibility, maturity, degree to which achieves the goals, etc.

  • Suggested answer
    NilsRas Profile Picture
    NilsRas on at

    We specialize in extracting data from AX and legacy systems and populating our pre-configured BI360 data warehouse (SQL Server) with the data for combined reporting. If you want to keep your existing data warehouse, then this solution might be less interesting. We have several extraction methods, including pre-built SSIS packages, depending on the need. If interested, you can see more here: www.solverusa.com.

  • Community Member Profile Picture
    Community Member on at

    I think two options for a quick solution, but not sure if there are other solutions better. The first option is writing x++ leveraging ODBC to export required data, and put the code in a batch that I can run manually or as a scheduled job. The second option is writing c# code that works through .net business connector. Of course, you also can use Excel if you the AX add-in for Excel and do visualization with it.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Priya_K Profile Picture

Priya_K 4

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans