I'm looking fo some ideas on best methods for syncing bulk data between an ERP system database and Magento. I'm looking for the fasted and best approach.
Here is what we have done and tried. We currectly have another ERP where we sync bulk data using a .NET application using Magento SOAP API for authentication and then the .NET app generates csv files for importing using the Magento import process. This works pretty good but wundering if there are better and faster methods that could be used.
We have tried using Pentaho Spoon using Magento REST API. We did a test making a single call for each record in Magento (Update) and it takes about 1 second per record which is very slow considering upwards to 20-30k inventory records.
We are open to do this outside of .NET as well.
That depends on the ERP system you are using, you also will not have 30,000 product inventory updates daily so you need changed record loads aka deltas. You don't necessarily need fast, you need efficient which is different. There are various solutions but normally they come under enterprise grade so it comes back to the ERP you are using which determines what approach to use. It also depends on your technical and/or business resource to maintain the sync process, there are a lot of consulting questions to answer before the right solution presents itself. Some of the people via via we have worked with used Pentaho, they threw it out as required too much technical resource to maintain, it wasn't Informatica or SAP.
The ERP application is called Spire using a PostgreSQL database.
Technical and business resources are not a problem. I'm not the developer but have a full team both highly experienced in .NET and Magento development. I'm the project manager and just gathering information and ideas to discuss and explorer with my team.
We are a software developer developing this as an application to sell to our customer base.
Yes agree on efficiency. Our other application is pretty efficient but not the fastest and perhaps there is nothing out there to make it faster. For 30K inventory records it takes about 30 minutes.
Then it becomes more complex. We provide business support for @CommerceKick which is run by an incubator but developed by multiple levels of consultants and architects via other companies. We know the architects (via via) are SAP/Informatica based and tested Pentaho (plus the others) with Magento, those solutions were thrown out. It all comes down to whether your customers view it from a business, a technical, or a hybrid approach. All the mid level toolsets are technical focused and it's why we understand they developed their own based on SAP business processes to multi-channel connect to Magento, saw it running with 300,000-500,000 Magento products processing millions skus from 70-100 suppliers per day, we use SaaS versions which are rolled in to the SIA platforms. For 30,000-50,000 products the mid-level tools need too much resource and as Spire is an SME low install product it's difficult to justify the ROI. There are some major technical and business limitations with Magento and product uploads which require and exact set of balanced tradeoffs in the 10,000s products, once you go in to the 100,000s products you need some serious architects involved to external sync.