Microsoft Power BI Integration for Magento 2

Ecommerce merchants need to base their activities on constant analysis if they want to make effective business decisions and stay on track to increased sales and profit. It is fundamental for every online store owner to collect essential data and analyze the most critical metrics of their business. However, getting in-depth insights and building long-term strategies becomes possible with advanced tools that use smart algorithms in data analytics. Microsoft Power BI allows merchants to apply business intelligence techniques to the decision process and get access to the comprehensive reporting system.

Today, we are making a review of the solution that brings necessary tools to Magento merchants who want to transfer their reporting data to the Power BI service – the Power BI Integration extension for Magento 2. The module lets you connect to the Microsoft Power BI REST API and monitor reporting data from your desktop and mobile app any time you need. With the Power BI Integration module, you will get access to interactive analytics boards and gain profound insight into your store performance.

Below, we dive deep into the concepts and functionality of the Power BI platform and its integration with Magento 2.

Firebear Studio, an Adobe Bronze Solution Partner, would be happy to discuss your project and provide a free quote and business analysis. Get the free quote! We have more than 10 years of Magento experience and maintain the leading Import & Integration solution used by more than 3000 merchants worldwide.

Microsoft Power BI Overview

Power BI is a Microsoft business intelligence service that allows monitoring and analyzing essential business data from one place on-premises or in the cloud. With Power BI, you can easily connect to various sources of your data and transform it into vivid visualizations. The Microsoft service lets you access the visualized data on interactive dashboards and reports that can be customized and shared with other users. This way, you get a robust reporting and analytics tool that helps you take deliberate business decisions.

Power BI offers flexible pricing options adapted to any budget and business needs. Free Power BI account allows loading up to 1 GB of data, create personal dashboards and reports, share them with other users, and view them both on desktop and mobile. You can check Power BI pricing plans for more details on the specific features of each plan. In this overview, we describe the functionality related to the free Power BI service plan.

Power BI integrates the following elements: Power BI Service (an online SaaS service), Power BI Desktop (a Windows desktop application), and Power BI Mobile (mobile apps for Windows, iOS, and Android). There is one more element – Power BI Report Server – that can be used in case you need to publish a created report on the on-premises server rather than the cloud.

The standard workflow of a user who creates analytical dashboards and reports in Power BI starts from building a report in the Power BI Desktop. Then, the report is published in the Power BI Service and shared with the end users to let them view it. Dashboards are also created in the Power BI Service. You can read more about the basic concepts of the Power BI service in the Power BI documentation.

Next, we want to say a few words about the 4 main building blocks of Power BI: a dataset, workbook, report, and dashboard. All these blocks are organized into personal workspaces or collaborative app workspaces.

Datasets contain all the data that you import from your data sources. Power BI supports different types of data sources: Excel, Power BI Desktop, CSV, and other files; content packs; databases (Azure SQL Database, SQL Server Analysis Services, and others). You can find more information on the Power BI Datasets in the Power BI documentation.

A workbook is a type of dataset that allows getting data from Excel files to Power BI. You can either import your data from the Excel tables into a new Power BI dataset or connect to your worksheet and view it exactly like in Excel Online. You can read more about getting Excel data in Power BI here.

A report in Power BI acts as an interactive visualization of data based on a single dataset presented on one or more pages. Reports are a feature of Power BI service and Power BI Desktop. It is possible to modify created reports by adding and removing data, changing types of visuals, applying filters, and more. You can learn more about Power BI Reports here.

Power BI Dashboard gathers visuals, graphics, and text represented in tiles on a single page. Dashboards are a feature of Power BI Service and not available in Power BI Desktop. Data visualized on a dashboard can be collected from different datasets. Since all visualizations are available only on one screen, dashboard highlights important information from your metrics. It is possible to pin a tile to a dashboard, and when a user clicks it, it will lead to the related report. You can find detailed information on Power BI Dashboards in the Power BI documentation.

Power BI Integration for Magento 2: Core Features

• Reports and dashboards based on Magento reporting data

Power BI Integration enables merchants to build Power BI Reports and Dashboards using data from their Magento stores. The Magento 2 module delivers load profiles created based on tables and grids in your website backend and regularly updates the data in the Power BI service. The Power BI Integration extension allows Magento admins to set a schedule for data updates, as well as monitoring period.

Currently, the extension supports 19 reports and the Orders grid from Magento 2. You can check the full list of supported reports in the module’s user guide.

Power BI Dashboards and Reports allow you to monitor essential business metrics, give access to analytical data to other users with whom you want to share it, and take timely business-related decisions. As already mentioned, dashboards may contain information from several datasets and reports, as well as cloud and on-premises data. This way, you will have an integrated visual representation of your Magento data that can be personalized according to your requirements.

• Magento reporting data on the mobile app

Power BI Mobile allows accessing and viewing your reporting data on mobile devices. Power BI mobile apps are available on Windows, iOS, and Android. After installing the mobile app and logging in with your Power BI credentials, you will be able to monitor your data from Power BI service on created reports and dashboards on the go. It is also possible to customize the look of reports on mobile by changing the editor view in the Power BI service.

• Data alerts

After you create a dashboard, you can set notifications for the dashboard tiles that support this feature. If the data represented in your dashboard has changed beyond a specified threshold, Power BI will create a notification. The service sends alerts with a direct link to the tracked data to the notification center, as well as via email if you allow this option. Notifications are synchronized across Power BI Service and Mobile, so you will immediately receive them no matter which device you are using at the moment.

Power BI Integration Workflow

After installing Power BI Integration for Magento 2, you will be able to transfer data from Magento reports to the extension. The extension creates an export profile, known as a Load Profile, with specified filters and assigns it to a Load Dataset. Load Profile and Load Dataset are transformed into Power BI Table and Power BI Dataset correspondingly. A user can modify settings of a Load Profile and create custom titles for Table and Dataset before publishing the dataset. The next step is the generation of Power BI Dataset with associated Power BI Tables in the Power BI service. A user can build Power BI Dashboards and Reports based on the created datasets.

It is necessary to mention that the extension was designed with the view to simplify the usage of Power BI API due to its limitations. That’s why the backend configuration of the Power BI module allows creating temporary and permanent datasets. We look closer at this concept and other basic terms used within the integration in the Backend section of this review. To learn more about the current extension limitations, refer to the user guide.

Backend

Note that before configuring the Power BI Integration extension, you should create an Azure Active Directory (Azure AD) tenant, separate Power BI users for each Magento admin, and a Power BI service account. Besides, you will need a Client ID and client secret key to get access to the Power BI REST API. You can get more details on how to obtain the Client ID and secret key in the Microsoft Power BI documentation.

Below, we are describing the process of loading data from the Magento backend to Power BI Service using the Power BI Integration module.

First, navigate to a report from which you want to transfer data to Power BI. In our example, we work with the Magento Orders Report. On the report page, you will see an option of export to Power BI. After you click on the “Export” button, it will trigger the generation of a Load Profile which data and selected filter values will be associated with Power BI Table.

Before loading Load Dataset to Power BI, you can modify information in your profile on the Load Profile Editor page. In Profile Information on the page, you can change Profile, Table, and Dataset names and select a load mode. Here, you can also adjust the time interval for the report. Note that there is a “Load To Power BI” button pressing on which will trigger loading of the dataset.

You can view the list of existing load datasets and create a new dataset on the Power BI Datasets grid. The grid shows each dataset’s name, type (Temporary or Permanent), and state (Loaded or Not Published). Note that by default temporary load datasets get a name PBI_TEMP, which will be displayed in the same way in Power BI. Also, note that you shouldn’t use temporary datasets for building Power BI Reports and Dashboards that you want to access permanently. If the structure of such dataset changes, it will be deleted from Power BI and republished with new data, which leads to the loss of created visualizations and tiles in your reports and dashboards.

As a Magento admin, you can create a permanent load dataset by clicking the “Add New” button on the grid. It will forward you to Load Profile Editor that we have described above. A new Load Dataset gets Not Published status by default. After configuring the dataset and assigning all load profiles to it, click “Publish” in the Action column to load the data to Power BI. Note that you can publish permanent Load Dataset only once, so be sure you have added all required Load Profiles to it.

As already mentioned, Load Datasets from Magento reporting pages are associated with Power BI Datasets that consist of Power BI Tables. On the Power BI Data Load grid in the Magento Admin, you can check the details of Load Profiles processing with the connection to the Power BI service. To activate automatic data actualization in the load profiles, click “Enable Auto Load” in the top panel of the page. Note that you should also set Load Mode as Regular or Live for automatic data updates of load profiles.

Now, let’s navigate to the configuration page of the Power BI Integration extension. The general settings of the module are divided into 2 sections: Power BI API and Power BI Scheduler.

First, enter your Power BI API credentials: client ID and secret key.

On the Power BI Scheduler tab, you can enable regular data update, set its start time, and select load frequency. Also, you can activate live data loading to the Power BI service and set its frequency (in minutes). Then, decide whether you want lifetime Magento statistics to be refreshed automatically and choose an update option: No scheduled statistics updates; Update before every Live and Regular Loads; Update before every Regular Load; Update by separate schedule. It is also possible to activate statistics refresh for the last day and select its method from the available options (the same as in the previous setting).

Final Words

Power BI Integration for Magento 2 brings advanced real-time analytics tools to Magento merchants. With the Power BI module, you get the possibility to export all essential data from your Magento reports to the Power BI service and use it for building detailed reports and dashboards enriched with visualizations. Due to the customization possibilities of Power BI reporting elements, you can adjust the look of your analytical panels and use them personally as well as share them with other users.

As an Adobe Bronze Solution Partner, Firebear Studio is delighted to talk with you about your project and offer a free quote and business analysis. Get the free quote! With more than ten years of Magento experience, we continue to operate the industry-leading Import & Integration solution trusted by more than 3000 merchants globally.