Magento Reporting with Microsoft Power BI
By Jan Brooijmans
Realtime Magento data and Power BI reporting = creating business impact! With all the data from Magento commerce – and Google Analytics – you can run very insightful analyses and improve your business performance every day. It allows you to make the best decisions, funded by the numbers that drive the most important trends in your store.
You have to make sure you have all your data loaded and running smooth. In the previous blog I wrote about How to connect to Magento using Power Platform Dataflows. If you haven’t read my previous blog, please do before you continue.
8. Open PBI Desktop and load dataflow
With the dataflow build and refreshed on the PBI Service, let’s continue. In Power BI Desktop, select Get Data and choose Power Platform dataflows and Connect.
Select the tables from the Sales order header and items dataflow. The data will load in your Query and you can proceed to close and apply the query.
9. Edit/check relationships
In order to use the two tables, let’s join the two by order_id. In the Model view create the link on order_id like in the image below.
*If you have PBI Premium you can join the tables in the dataflow as well, for better governance.
10. Time to create a report
Change to the Report view and start a new horizontal bar chart. Select the order_id and Total order amount fields.
Now let’s change the formatting to improve the user experience: apply sorting, labels and colour if you want. I’m using Magento orange as colour for the bars in this 🙂 Finally, let’s create a top 25 list of order by amount.
11. The report is ready to be published!
Click publish in the ribbon, on the right to select the destination (Workspace) you want it to share with.
12. Update dataset refresh schedule
The dataset is published along with the report to PBI Service. Go to Datasets and set the refresh schedule for the dataset. Click the credentials and set them for the Dataflows. Remember to schedule the refresh for the dataflows first and then the datasets!
Anyone within your organisation can now consume this report but also start new reports in desktop using the dataflows or dataset.