Table Profiles based on Magento Reports

Creation of Table Profiles based on Magento Reports

To create a Load Profile, user navigates to a Magento Report. If the report supports export there is an option of exporting to Power BI available on the page. Triggering the export will create a Load Profile that will keep association with selected filter values. Please note that Export button should be used after the selected filter values are applied to the report. First user enters the filter values, then click on “Show Report” and only after that he can select “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.

Picture: Magento Orders report with Export to Power BI capabilities

Click on “Export” button will result in Table Profile creation and a message informing user about successful profile creation will be shown.

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.

Picture: Properties of report based Table Profile

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 allows having a table that will always hold data for particular period relative to current moment, such as for example the data for latest 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 created based on tables or grids in Magneto backend. Most of them are taken from Magento Reporting pages. The Power BI Integration is supported for the grid if there is an option of exporting to Power BI available in the list of possible exports as it is shown on 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 BIM Extensions -> Data Integration (ETL) -> Magento Statistics Scheduler.

Picture: Magento statistics refresh schedule config

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.