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?
Can someone please give some advice?
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.
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.
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.
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
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.
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.
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.