TABLE PROFILE TYPES

TABLE PROFILES BASED ON MAGENTO API ENDPOINTS

With the help of this functionality, any data that is accessible via the Magento REST APIs may be sent to Power BI.

Upon successful establishment of the API connection between Power BI and user's Magento instance, the user will have access to API endpoints for creating relevant Table Profiles and extracting data to Power BI.

Creation of Tables Profiles based on Magento API endpoints

To navigate to the "Power BI Table Profiles" page, click on "Reports" and see Insights Ready section in there. After settling on the Power BI Table Profiles page and clicking "Add New Table Profile", users are allowed to choose the profile's type, where the user can pick the API type from the dropdown.

The next step requires the user to fill 2 mandatory fields:

• Profile name

• API Path (the user has the option to select an endpoint that corresponds to their specific objectives.)

The next required action is clicking the "Read API Response Format" button which will allow the user to receive a list of columns (API Response Fields) from the requested endpoint. In order to create a table and display the corresponding values in Power BI, the user needs to choose the relevant columns from the list provided, which will help construct the table profile.

Further, user can navigate to the "Configure selected fields" button and the system will recognize the data in the table. Interface will display columns' names and two settings for them PBI Column Name and Data Type:

Moreover, users can notice the Parameters section, which contains several fields: Incremental Field, Search Criteria Field Name and Incremental Mode.

• Incremental Field: Incremental Field can be used to help the user choose the field with increasing values, so that Power BI can easily recognize it. It serves as a unique identifier for each record or row, and whose value increases incrementally with each new record added.
• Search Criteria Field: The utilization of Search Criteria enables the retrieval of a list response of an entity based on specific conditions, which are expected to align with the selected Incremental Field. The Search Criteria are subsequently passed on for filtering and sorting purposes, specifically by utilizing the Incremental Field as a reference point.
• Incremental Mode: It contains 2 variants - New Data Only and Full Data Refresh. Selecting 'New Data Only' will transmit solely the new records to the BI application, while 'Full Data Refresh' will send the entirety of the API data. This option is available when an incremental field is selected.

After ensuring that all the fields are set correctly, the user should click the Save Profile button on the top of the page.

CSV TABLE PROFILES

This functionality allows to bring to Power BI the data that is not stored in Magento database. The data from CSV files might be sent to the same Power BI datasets where Magento data is stored. This provides possibility to merge Magento data with data from another sources.

Limitations and considerations

The data from CSV files is not sent in incremental mode. This means that every time, the data loading will be started, full content of CSV file will need to be delivered into Power BI. This also means that it is not possible to continue data loading from the last record being sent. If the data loading will be interrupted (for example, due to Power BI hourly limits on data rows), the data loading from CSV file will have to be restarted from the beginning.

Creation of Table Profiles based on CSV files

After settling on the Power BI Table Profiles page (Reports Insights Ready) and clicking "Add New Table Profile", users can specify the CSV type of teh Table Profile from the dropdown.

Afterward, user needs to fill in 2 mandatory fields:

• Profile name
• CSV Path

(Note: user should put the .csv file on the server before the creation of a table profile, using the path /magento/root/location/var/bim/imports).

Further, user can click the "Read CSV headers" button and the system will recognize the data in the table. Interface will display columns' names and several options for them:

• Included checkbox - provides users with the option to exclude specific columns from the table profile by unchecking the box.

• PBI Column Name - provides users with the option to change the column's name in the way they want it to be displayed in the Power BI.

• Data Type - provides users with the option to pick the appropriate data type of the values in chosen column. A dropdown menu presents the following options to choose from:

  • Whole Number
  • Decimal Number
  • Date/Time
  • Text/String.

Moreover, the user can notice the Parameters section, which contains the Incremental Field and Mode dropdowns.

• Incremental Field: would help the user to choose the field with increasing values, so Power BI would recognize it correctly.
• Incremental Mode: contains 2 variants - New Data Only and Full Data Refresh.'New Data Only' to send only new records to the BI application or 'Full Data Refresh' to send the entire CSV file during the data-refresh process. This option is available when an incremental field is selected.

After ensuring that all the fields are set correctly, you should click the Save Profile button on the top of the page.

SQL TABLE PROFILES

With this functionality, it becomes feasible to employ certain SQL queries linked to the Magento database as a data source, much like the CSV Table Profiles.

The SQL query itself should be entered in a config file etc/bim.xml inside a custom Magento module.

Creation of Table Profiles based on SQL queries

Once users navigate to the Power BI Table Profiles page ("Reports" "Insights Ready" section), they can select "Add New Table Profile" to create a new profile. At this point, users can choose the type of profile they want and select the SQL type from a dropdown menu.

After selecting the Table Profile type, user is required to fill in 2 mandatory fields:

• Profile name -- set any suitable name for your profile.

• SQL Query -- select a specific query from the pre-loaded set in the dropdown.

Afterward, the user needs to click on the "Load SQL columns" button, which will prompt the system to identify the data within the table. The interface will then show the names of the columns along with various options for each, which are described in greater detailes below.

• Included checkbox - provides the user with the option to exclude a specific column from the table profile by unchecking the box.

• PBI Column Name - provides the user with the opportunity to change the column's name which they want to be displayed in Power BI.

• Data Type - provides the user with the chance to pick the appropriate data type of the values in the chosen.There is a dropdown where the user can choose from the following variants:

  • Whole Number
  • Decimal Number
  • Date/Time
  • Text/String.

In addition, users will come across the "Parameters" section that includes a dropdown for the "Incremental Field." This feature facilitates the selection of a field with progressively increasing values, enabling Power BI to interpret it accurately.

After ensuring that all the fields are set correctly, the user can click the "Save Profile" button on the top of the page.

DATE/TIME HIERARCHY TABLE PROFILES

By utilizing this feature, you can incorporate the Date hierarchy in Power BI as a means to organize your data table, making it easier to drill down and carry out updates based on various time intervals grouped together.

Creation of Date/Time Hierarchy Table Profiles

After settling on the Power BI Table Profiles page ("Reports" "Insights Ready" section) and clicking "Add New Table Profile", users are presented with the option to choose the type of profile they desire, including the "Date Time Hierarchy" type, which can be selected from the dropdown menu.

Afterward, user is required to fill in 5 fields described below:

• Profile name -- set any suitable name for your profile.

• Type -- Date or Datetime, defines the data which is meant to be used in the dataset

• First Year -- set the year of the starting point for the data gathering

• Last Year -- set the year of the ending point for the data gathering

• Time granularity -- a measure of the level of detail which should be based on intervals of seconds, minutes or hours

After ensuring that all the fields are set correctly, you should click the "Save Profile" button on the top of the page.

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.