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.