TABLE PROFILES BASED ON MAGENTO REPORTS

This functionality enables the reuse of Magento software's default analytical capabilities, which includes a set of statistical reports. Users can transmit data from these reports to Power BI for further analysis.

The primary contrast in how this data is used is that instead of performing calculations and aggregations within Power BI, the calculations already performed by Magento are leveraged.

However, it is not recommended to use this functionality if Sales Cube package is available as this package provides more efficient and more flexible way to work with statistical data.

Creation of Report Table Profiles

To create a Load Profile, user needs to navigate to a Magento Report ("Reports" "Sales" module). If the report permits exporting, there will be a "Power BI" export option available on the page. Upon clicking the "Export" button, a Load Profile will be created, linked to the chosen filter values. It's essential to note that the Export button should only be used after applying the selected filter values to the report. The user must enter the filter values, click on "Show Report", and only then choose the "Power BI" export option and click on "Export". Each report, if it has a date filter, should have the date range specified in that filter before it could be used as a source for Table Profile creation.

Clicking on "Export" button will result in Table Profile creation and a message informing user about successful profile creation.

Configuring report based table profile

Table Profiles based on Magento Reports may have extra properties on their details page. If the Magento Report supports specifying a date range, there will be "Date From" and "Date To" fields visible on Table Profile Properties page.

"Date From" and "Date To" options allows changing the start of the period that data will cover.

User can also enter dynamic values in those fields such as "-1 day", "-3 week" or other values supported by strtotime() function in PHP (http://php.net/manual/de/function.strtotime.php). This feature permits the creation of a table that continuously retains data for a specific period relative to the present moment, such as the data for the most recent 3 months.

"Date Range Preview" field will show how system interprets the entered period.

Supported reports

This section describes what Magento Reporting data can be sent to Power BI Service with current version of the Power BI Integration extension.

Table Profiles are established on tables or grids found in the Magento backend, with many of them sourced from Magento Reporting pages. The Power BI Integration is compatible with a grid only if the option to export to Power BI is included in the list of available exports, as illustrated in Image 2.

Supported reports list for Magento 1

The Magento Reports that can be exported to Power BI Service in Magento 1 at the moment are the following:

1. Total Ordered Report, Reports Sales Orders

2. Order Taxes Report Grouped by Tax Rate, Reports Sales Tax

3. Total Invoiced vs. Paid Report, Reports Sales Invoiced

4. Total Shipped Report, Reports Sales Shipping

5. Total Refunded Report, Reports Sales Refund

6. Coupons Usage Report, Reports Sales Coupons

7. Products in carts, Reports Shopping Cart Products in carts

8. Abandoned carts, Reports Shopping Cart Abandoned carts

9. Products Bestsellers Report, Reports Products Bestsellers

10. Products Ordered, Reports Products Products Ordered

11. Most Viewed (Products), Reports Products Most Viewed

12. Low stock (Products), Reports Products Low stock

13. Downloads, Reports Products Downloads

14. New Accounts, Reports Customers New Accounts

15. Customers by Orders Total, Reports Customers Customers by orders total

16. Customers by number of orders, Reports Customers Customers by number of orders

17. Customers Reviews, Reports Reviews Customers Reviews

18. Products Reviews, Reports Reviews Products Reviews

19. Search Terms, Reports Search Terms

Additionally creation of Table Profile is possible based on non-reporting lists (only one list is connected in current version):

Orders List, Sales Orders

Supported reports list for Magento 2

The Magento Reports that can be exported to Power BI Service in Magento 2 at the moment are the following:

1. Products in Cart

2. Products Views

3. Downloads

4. Shipping

5. Refunds

6. Orders

7. Invoiced

8. Coupons

9. Tax

10. Bestsellers

11. Abandoned Carts

12. Customer Totals

13. New Accounts

Magento statistics refreshing

Table Profiles based on Magento Reports will use those reports as a data source. Since the date in Magento Reports needs to be refreshed in order to get actual values, the same relates to report based Table Profiles.

To facilitate automated data actualization of report based Table Profiles, the Power BI Integration Extensions come with a functionality to automatically refresh Magento Statistics.

To activate statistics refreshing, user can navigate to Magento Configuration page: Stores Configuration in Magento 2 or System Configuration in Magento 1. Section Insights Ready Extensions Data Integration (ETL) Magento Statistics Scheduler.

The configuration has two options in Magento Statistics Scheduler, each taking as value of one of the available schedules. Typical values are Life or Regular.

Refresh Lifetime Magento Statistics option allows configuring schedule for refreshing complete Magento statistics. User should be careful with this option since refreshing Magento statistics might be computationally intensive task. It is not recommended to refresh this statistics too often, especially if the store has lot of data. This option can be enabled if user wants to refresh data less frequently then just once per day.

Refresh Magento Statistics for the Last Day option allows to configure schedule for refreshing only statistics for the last day. This option is more preferable to be used in connection with automated data updates when data is refreshed at least once per day.

Usage of filters in magento reports

Most of the Magento reports come with filters that allow filtering data by date range, stores, websites, etc. Users can select the desired filters and create Table Profiles with different combinations of filters. This provides some flexibility in making reports.

Some examples on how the data might be combined based on Magento report based Table Profiles:

• Several tables with Magento sales data, each containing data from different stores.

• Tables Profiles with data covering different periods: one table for data for the last year, second table with data for the year before.