The purpose of this post is to discuss using ETL , often referred to as middleware, to integrate your ERP with other applications like ecommerce (Magento), websites (Drupal), shipping (FedEx, UPS, DHL, USPS), and/or others. Integration options with other systems should be a consideration when evaluating ERP systems. I hope to accomplish two objectives in this post:
- Explain what integrations options are available to you.
- Explain how iDempiere and ADempiere approaches this topic so that you can decide if they fit your needs.
The reality is that you are now, or will be soon, dependent on something/someone to integrate your ERP with external services. Your integration options include (1) using canned product-based modules, plug-ins, connectors or adapters or (2) using ETL products like Kettle, Jitterbit, or Talend.
Use Webstore as an Example
Let’s use the example of integrating a webstore with your ERP to better define your options. Webstore integration is a good example because most people understand the domain, and it can scale from very simple to very complex. Here are the common integration points between a webstore and an ERP:
- Pushing orders from the webstore to the ERP
- Pushing payments from the webstore to the ERP
- Pushing order updates from the ERP to the webstore
- Syncing on-hand product quantities.
Product-Based Modules, Plug-ins, Connectors or Adaptors
Let’s assume you are evaluating OpenERP to work with Magento. By performing a quick Google search, you will find more than a couple of plug-ins that install into Magento and connect the two products. If the connector you install is capable enough, and your needs are simple enough, you can get the connection up and running in a matter of days.
Let’s put this scenario into perspective. If you are a $2M USD company, there is a good chance your needs will be met by the above connector. At $2M in revenue, the owner is still making most of the decisions, and there is little complexity in your business. You buy, you store, and you ship product to your target market. If your connector performs the needed task, you get to enjoy the positive benefits of modules, plug-ins, connectors or adaptors.
Now, let’s assume you are a $20M to $200M USD company. The owner/CEO of the corporation is no longer the owner of most projects. The person in charge of the ERP evaluation has many customers inside the company. These customers include:
- Marketing – drives general product pricing, product campaigns and grouping, customer communication through the website, etc….
- Product Management – drives product features, product configuration, product forecasting, etc…
- Sales – Order entry, warrantee, RMAs, all the special things that sales can do that the customer cannot on the web, etc…
There is a good chance the complexity of your business exceeds the ability of the connectors you found on Google. Each of the above groups will have demands that drive how your webstore and ERP communicate. Below is a list of complexities (see: Complexities With Webstore and ERP Integration) that will drive you beyond the average connector. When this happens, be ready to get your wallet out. If you have an internal development team, and the terms of the adapter allow for it, you will now be the owner of a new code base to maintain. If you do not have an internal dev team, or you cannot gain access to the connector code, you will now be dependent on the connector creator to support your customizations. Either way, be ready to spend some time and money.
ETL – Extract, Transform, Load
In the above section, we assumed that you found a tool that installed inside of either OpenERP or Magento. But, what if you could have a tool that did not require you to install a connector inside of one or all your applications? Instead, the tool fit in between your applications. ETL is just that – a product that sits in between two products to help them communicate.
The reality is that most applications are good at communicating with itself; however, they are bad a communicating with other applications. ETL helps make communication between applications much easier. ETL offers the following benefits:
- You communicate using your strengths. If you are a database person, ETL helps you read and write from an applications database tables. If you are a web services person, ETL helps you make web services calls into an application.
- You communicate using your application’s strengths. If you have an ERP with strong web services and a webstore with good flat file import/export, ETL helps you do both at the same time – pulling from the ERP using web services and writing to the webstore using flat file.
- You minimize coding. Inter-application communication tends to require a lot of coding to handle the many scenarios. ETL handles much of communication details for you.
If you are less complex company, you find a connector that supports your needs, and you do not see your business changing much in the future, there is not much need to explore ETL. The above connector will save you time and money. However, if the canned connectors do not support your needs, or if you support communication in many directions (webstore, website, shipping, multi-channel commerce, taxes, other business partners, etc..) ETL will make your integrations much easier, faster and more flexible.
Example ETL Webstore and ERP Integration
I use ETL to help Magento and iDempiere/ADempiere (iD/AD) communicate. In my last project, I used Jitterbit because they already had an instance in production. Normally, I would use Talend. Here are the major topics:
- When pulling data from Magento, look to the tables. Magento API and web services calls have not matured yet; therefore, it is much easier to look for data directly at the database. I was recently in a room for of Magento developers discussing this very topic. They believe the Magento 2.0 interfaces will provide much more utility; however, in the mean time, you are best served reading from tables.
- When writing to ADempiere or iDempiere, write to the import tables (i_invoice, i_order, i_payment, etc..). The processes that pull from these tables are highly intelligent. They handle the logic of grouping lines into order, creating new business partners if they do not exist, etc… The import code is easy to understand and modify.
- If you combine the above two topics, you can push Magneto order into iD/AD by reading Magento’s sales_flat_order table and writing to iD/AD’s i_order table. Below is a magneto order SQL query that will tell you everything you need to know about a Magento order.
- Create a ETL schedule. One of the first things I do in the ETL is create an execution schedule. The schedule drives how often you want the transformations to occur. You choose how often events fire. You can set it for once a minute or once a day.
- Writing the ERP’s inventory level (quantities on hand) back to Magento is a little more complex. Magento has multiple product caching tables; therefore, you need to update multiple tables with the correct qty. Below is a list of tables you need to update. FYI – You can use the Magneto API to update Magento’s qty and stock status; however, if you product count grows beyond 1000 or so, the Magento server will crash under the web services load.
Complexities With Webstore and ERP Integration
When you are researching connectors or ETL options, be aware of the following complexities with ERP and webstore integrations:
- Product Attributes – you need to decide which system holds the record of truth. Trying to sync webstore product attributes and groupings with your ERP’s attributes is probably beyond most people’s scope.
- Webstore product configurators – configurators add quite a bit of complexity to order management and communication. This is true for webstore to ERP communication and customer service data entry.
- High volumes of products – Some of my customers have 100,000’s of products. Strategies used by connectors sometimes fail under the stress of high product counts.
- RMAs – Which system is the record of truth.
- Delayed credit card authorization and token management. If you allow for back orders, your credit card management processes will likely outgrow most connectors. For example, you should cancel an authorization if it approaches 5 to 7 days old. This scenario is common when shipping back orders where the product might ship months later.
- Address management – Just because a customer updates an address in the webstore does not mean the same address should be updated in your ERP. There could be open orders. The address could be tied to a credit card transaction. The customer could have edited an address when they should have created a new address.
Reference – Magento Product Quantity Tables
cataloginventory_stock_item is the master holder of qty and stock status.
cataloginventory_stock_status is the temp holder of qty and stock status. This table gets updated from cataloginventory_stock_item during/after a transaction. This table is queried when a user browses an item.
cataloginventory_stock_status_idx is seldom used (example: when selecting lots of products based on stock item).
To update these tables, use ETL to write your current QtyOnHand product list to a temp table in Magento (chuck_qty_import). Then use the below query to update the actual records for each of the above tables.
update cataloginventory_stock_item i set qty = coalesce(( select v.qty from chuck_qty_import v join catalog_product_entity e on (v.sku = e.sku) where i.product_id = e.entity_id ),0) ;
This information is further validated by this query:
SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN ('qty') AND TABLE_SCHEMA='YourSchemaName'
Reference – Magento Order View
SELECT o.increment_id, o.entity_id, o.status, o.state, o.customer_id, o.base_grand_total, o.base_shipping_amount, o.base_shipping_refunded, o.base_subtotal, o.base_subtotal_incl_tax, o.base_tax_amount, o.base_total_invoiced, o.base_total_paid, o.created_at, o.updated_at, o.total_item_count, o.order_currency_code, o.customer_firstname, o.customer_lastname, o.customer_email, o.customer_middlename, o.customer_prefix, o.customer_suffix,
baddr.customer_address_id as billing_address_id, baddr.region as region_baddr, baddr.postcode as postcode_baddr, baddr.street as street_baddr, baddr.city as city_baddr, baddr.email as email_baddr, baddr.telephone as telephone_baddr, baddr.country_id as country_id_baddr, baddr.address_type as address_type_baddr, baddr.company as company_baddr,
saddr.customer_address_id as shipping_address_id, saddr.region as region_saddr, saddr.postcode as postcode_saddr, saddr.street as street_saddr, saddr.city as city_saddr, saddr.email as email_saddr, saddr.telephone as telephone_saddr, saddr.country_id as country_id_saddr, saddr.address_type as address_type_saddr, saddr.company as company_saddr,
oi.sku, oi.name as prod_name, oi.qty_ordered, oi.base_price,
(select GROUP_CONCAT(CONCAT_WS('_',p.method,p.po_number)) from sales_flat_order_payment p where p.parent_id = o.entity_id group by p.parent_id ) as payment_details
FROM sales_flat_order o INNER JOIN sales_flat_order_address baddr ON o.billing_address_id=baddr.entity_id INNER JOIN sales_flat_order_address saddr ON o.shipping_address_id=saddr.entity_id INNER JOIN sales_flat_order_item oi ON oi.order_id=o.entity_id WHERE is_exported = 0 ;
I hope this helps!!!
Why consider Open Source ERP
Open source ERP gives you every opportunity to prove or disprove its ability to support your company’s ERP needs on a timeline that satisfies your organizational needs. With open source ERP, you do not face the same financial constraints nor do you face the same conflicts of interest as with commercial ERP. Instead, you invest in the appropriate skills and knowledge for your people and processes. Best of all – if open source ERP cannot solve your company’s needs, you can safely justify spending the additional $2K to $5K per person per year for life of your commercial ERP to help drive your organization’s success.
ADempiere vs iDempiere vs Openbravo vs Compiere
The ADempiere, iDempiere, Openbravo and Compiere environments are amazingly similar. iDempiere came from ADempiere. ADempiere and Openbravo came from Compiere. Compiere came from Jorg Janke. Jorg came from Oracle. As a result, iDempiere and ADempiere have much in common with Oracle’s ERP in terms of the financial feature set.
This is both good and bad. Good because iDempiere and ADempiere are quite capable to help a company grow beyond $500M USD. Bad because they tend to be more complex in that they account for multiple languages, accounting schemas, currencies, calendars, costing types, costing methods, etc…. If you are a growing organization, and you need a system that will grow with you, and you have the right internal talent/resources, iDempiere or ADempiere will be a big asset for you.
The biggest difference between these products is that ADempiere and iDempiere are pure open source. ADempiere and iDempiere make all feature available for free. Compiere and Openbravo hold back features behind a commercial or paid license.
iDempiere and ADempiere vs OpenERP:
iDempiere/ADempiere (iD/AD) and OpenERP approach ERP from two very different directions. OpenERP comes out of the box with very simple options. If you are coming from QuickBooks, and you need a simple ERP system help you manage your business, OpenERP will look and feel comfortable.
iD/AD comes out of the box with every feature installed and configured to run a $200M+ USD business. If your business is growing rapidly, and you are willing to invest the time to learn an enterprise accounting system, then iD/AD will give you confidence.
Which one is best for you depends on your internal talent, growth and business complexity. Here is a post to help you learn more.