In this article, we are going to walk through the process of integrating data into Power BI via Rest API calls. We will use a real world case, as we are getting data from an e-commerce store, powered by woocommerce. The data from the store will be fetched using the woocommerce rest API, as discussing in a previous post. This store has been created and populated with some data for testing purpose, it is not an active platform used by customers.
Prepare the data
The first step is to prepare our data for the load process. We will export all the products and orders made on the test store. The woocommerce rest api will not be used directly to get data in Power BI. We will build a NodeJs application where we first save the products and order data in a MongoDB database, and then build some API endpoints to fetch data from the MongoDB database. It is important to note that from the purpose of this project, the node js application is not needed ( actually it was already done for another project and convenient to use as the endpoints there are much faster than the woocommerce endpoints ), but it allows us to integrate an Extract and Transform process ( before Loading ) , as we will see later. Power BI will thus consume these 2 endpoints to get data of the store:
- localhost:4000/products
- localhost:4000/orders
Let now load the data obtained in Power BI
Load data
After opening Power BI Desktop, click on Get data in the Home tab, then Web
Enter the endpoint in the field and click OK
The Power Query Editor is opened and a table is created, with the columns being the keys in the json response ( in previous Power BI versions, an additional step was needed to convert the data fetched in a table ). At this stage, we can remove the unnecessary columns ( the auto-generated MongoDB keys for example) , apply the correct data type,... Once we are satisfied, we click Close & Apply in the top left, and it will save our first query made to get the products.
Now let's redo the process the get a query for the orders with the corresponding endpoint: localhost:4000/orders
Handle product and order relationship
Before starting the fun part with the visualization let first make sure the relationships between products and orders are well established. When we click on Manage Relationships on Table tools tab, we can see that there is no relation . Power BI is not able to auto detect the relations
After a second look at the field tables, there is in fact no direct link between the 2 tables. If we look closely at the orders table, we can see some columns marked as links, like the line_items column. This is a column of interest because it actually contains the information of the line items in an order, and the line item relates to the products. Using the Power Query Editor, we can see the contain of a line item:
There is a many-to-many relationship between orders and products, and we need to create a table to represent this relation, using the line_items data.
While i am pretty sure the extraction can be done directly in Power BI, we can definitely use the node js application to create another endpoint. This endpoint will query all the different line items from the orders. It is also a good practice to do all the transformation before loading a clean set of data in Power BI. The code for the extraction looks like this:
router.get('/order_lines', async (req, res) => {
try {
const orders = await orderService.findOrders()
let order_lines = []
orders.forEach(order => {
let line_items = order.line_items
for(let i=0; i<line_items.length;i++) {
line_items[i].order_id = order.id
}
order_lines = order_lines.concat(line_items)
});
res.send(order_lines)
}
catch (err) {
res.status(500).send({
title: 'Get line items',
result: 'error occurred during getting process',
error: err,
})
}
})Of course you need the rest of the code for this to make sense, we just want to highlight the extra steps needed.
For the same reasons, we create a new endpoint that extract category info from the product table. We now have the 2 new endpoints, and we can call them in Power BI to obtain 2 new tables: order_lines and category_lines. We query also the categories table, to link the category id in the category_lines table to the correct category. Let see an example of the new tables with the order_lines.
We can finally open the Model view in Power BI and create the correct links between the tables ( Double check any attempt made by Power BI, there may be wrong )
Visualization
Finally the tables are present, and the proper connections have been made. We can now open the Report view in Power BI and build the dashboards we want. Let make a simple dashboard to display the total sales for a year, the trend, and the top seller products for a given period of time
As we can see, Power BI can ease the analysis process and visualization of the performance of the store. In the admin panel of the website, the analytics screen provided by woocommerce looks like this:
For a simple use, it will be sufficient enough, but if we want more insight, a powerful tool like Power BI is definitely a choice to considerate.
Other considerations
We successfully complete the integration of products and orders in Power BI. We already addressed some concerns about the relations with products and orders, but the cleaning phase is not over yet. For example we did not consider the product variations and the customer data. For an efficient analysis, some changes might also be needed on the checkout page ( use an API to fill the address fields, and harmonize the data)
Conclusion
In this article, we demonstrated how to get data in Power BI via Rest API. From a woocommerce store, we extracted the data, transformed the data, and loaded them in Power BI.
Categories: woocommerce API power bi