Setting Up Microsoft Power Platform
for Magento Reporting
Magento data + Power BI reporting = Data Driven Marketing! If you want to be in the front seat of your company and it’s performance, it’s important to use your customer data in order to make the right decisions in your strategy, products and marketing campaigns.
Here’s a guided approach to start using your Magento data and quickly learn in general how your orders are performing. It’s 2020 and if you haven’t started drilling into your business data streams, now is the time!
Microsoft Power BI has recently launched it’s Power Platform which makes it easy and efficient to create data streams – called dataflows. Those will be the backbone of your reports and specific datasets driving your insights. Why is this so powerful? Because you prevent duplicating data, which will be the case if you go the default route and create multiple reports with datasets in Power BI Desktop and publish them to PBI Service.
It is no fun to replicate all kinds of transformation steps over again on the same source when you are starting your next desktop reports. In terms of governance I prefer having standards on PBI Service for different sources and connectors and not have duplicates which might slightly differ or have different outcomes all together.
Enter Power Platform!
The recipe is really simple, you need a Power BI Desktop (sign up for a free trial) and in this example we are using the sales order items and sales order header tables from your Magento store. Best practice is to create a copy or slave dataset/database so you don’t impact your live database! Update this every day to have recent data.
And always remember to develop with the end result in mind!
Steps:
1. Connect to your Magento database copy
On Power BI Service (app.powerbi.com), create a new Workspace. this will allow you to set up dataflows. It’s a good start for all Magento or Sales reporting for instance. Next, hit the + Create on the top to start setting up the dataflow. Choose Add new entities in the next screen and you can pick your data connector. Can be a connection to MySQL or Cloud, I’ll leave that to you.
2. Load the sales order data and sales order items
Select the tables you want or prepared in copy. You’ll see the Queries will be loaded and data is visible.
3. Select columns order_id, all qty_* columns, price
Let’s remove all the columns we don’t need for the output we’re working towards. Select the columns you want to keep and right-click for Remove other columns.
4. Calculate the total amount invoiced in sales order items
You want to summarise the total amount invoiced, in my dataset the row_total shows the total for ordered. But when a product is canceled I don’t want to see the amount in my total sum.
- calculate a new column with
qty_invoiced*price=Total invoiced- select qty_invoiced column, then add column > standard > multiply, and select to use the values in column price
- change this column name to Total invoiced
5. GroupBy OrderId
Create a groupby order_id: create new Total order amount column summarising Total invoiced per order. Click Transform table > Group by.
The grouped table can be joined to the sales order table in our reports – also to the customer table in a later stage if you want.
6. Select columns
Select the columns you need in the sales order table: I selected order_id (entity_id), created_at, customer_id, and status.
7. Update dataflow refresh schedule
You have to set up a refresh schedule on the dataflow in order to load new data from your Magento store in this case. Important: finally you have to set up a refresh schedule on the dataset as well, that will tied to your PBI report. The logic is: connect a report to a dataflow and the report will create a dataset once you publish it to PBI Service.
The advantage is that you can transform, filter and enrich your dataset accordingly for your specific report. So a bigger dataflow can be broken into more specific reports and datasets, for multiple user groups! When you load Google Analytics data, you can choose per report to connect GA and join to Magento data.
Anyone within your organisation can now build new reports in desktop using the dataflows!
In the next blog some usage scenarios on how to connect Power BI Desktop to the Power Platform dataflows and create reports.