Magento is one of the most popular e-commerce platforms. How can you combine your Magento data with other sources to gain new insights? Let’s see how to get the data we have on Magento to an MS SQL Server Analytics data warehouse.
This post will help you define a process or pipeline, for getting your e-commerce related data from Magento and load it into SQL Server for further analysis. We will see how to access and extract your data from Magento through its API and how to load it into a SQL Server database.
This process requires you to write the code to get the data and make sure that this process will run every time new data are generated. Alternatively, you may check out RudderStack to load your data from Magento to MS SQL Server in minutes for you.
Extract your data from Magento
Magento exposes its platform through both a REST and a SOAP interface. Both can be used to pull data from it, which is also the scope of this article but also to interact with the platform. By using these interfaces, developers create rich applications and plugins for Magento. In this post, we will use the REST version of the Magento platform.
As a Web API following the RESTful architecture principles, it can be accessed through HTTP. As a RESTful API, interacting with it can be achieved by using tools like CURL or Postman or by using HTTP clients for your favorite language or framework. A few suggestions:
Apache HttpClient for Java
Spray-client for Scala
Hyper for Rust
Magento does not publish official SDKs but by using the SOAP interface it is possible to automatically generate clients that can act as SDKs for your favorite language or platform. For example in Java, you can create a client in Eclipse by providing the WSDL file that Magento exposes after you set up the platform.
Magento API Authentication
Magento is a self-hosted platform unless you are using the Enterprise cloud edition, so you have much more control over its access than other solutions, but if you want to access its data through the REST API that it has, then you will have to use oAuth for authentication which is supported by Magento.
Magento Rate Limiting
As a platform hosted on your own premises it doesn’t really impose any rate-limiting. In any case, as you would like to avoid stressing your e-commerce platform that is facing your customers you should make sure that your pipeline process does not overstress your Magento installation. But this is completely at your discretion.
Endpoints and Available Resources
Magento exposes the following resources:
Products. Retrieve the list of products, create, update, delete a product.
Product categories. Retrieve the list of categories assigned to a product, assign and unassign the category from a product.
Product images. Retrieve the list of websites assigned to a product, assign, unassign a website to/from a product.
Customers. Retrieve the list of customers, create, delete a customer, and update the customer information.
Customer Addresses. Retrieve the list of customer addresses, create, update, and delete the customer address.
Inventory. Retrieve the list of stock items, update required stock items.
Sales Orders. Retrieve the list of sales orders with detailed information on order addresses, items, and comments.
Order Addresses. Retrieve information on the specified order comments.
Order Items. Retrieve information on specified order items.
The API is possible to return either JSON or XML responses, this is something that you can control by providing the appropriate Accept header content type.
For all the above resources we can request from the Magento platform to pull out a list of results with all the associated data, so ideally we would like to pull all the data and make sure that we keep them up to date on our analytics platform of choice for further analysis. For this post we will just see how we can pull data for one resource, the Sales Orders, the process is the same for all other resources.
To pull data for the Sales Orders, we need to execute a get request to the following endpoint:
As a platform hosted by you, you need to replace the “magentohost” part of the URL with the actual URL of the host that has Magento running. The rest of the URL is the same as the above. The default response is in XML and looks like the following: