It is important to understand how Power BI REST API is working in order to know how to organize proper development of reports and dashboards.
The Power BI Integration extensions were developed with intention to simplify usage of Power BI API as much as possible. From one side, the goal is to protect users from making mistakes that may lead to broken reports or dashboards. From another side, user should be able to deliver data to Power BI as easy and fast as possible. Reaching this goal is not a trivial issue and desired level of flexibility not always can be reached due to limitations of Power BI APIs.
Basic terminology is described in this section. This includes terminology from Power BI Service and from Power BI Integration extensions.
Power BI Service Concepts
Power BI setup comprises of key components such as datasets, reports, and dashboards, which are systematically arranged within workspaces. A dataset may comprise of several tables, and this information is extensively documented in the Power BI Service documentation.
Here these concepts will be referred as Power BI Workspaces, Power BI Dashboards, Power BI Reports, Power BI Datasets and Power BI Tables.
Power BI Dataset is a collection of data organized in one or more tables. It is not only a data storage, datasets keep information about relationships between tables and other information that helps Power BI Service to better interpret the data. It is important to note that deletion of a dataset might result in deleted reports and dashboards this dataset relates to.
Power BI Table is a table-like storage of data with predefined columns of specific data types.
Power BI Column is a single column in Power BI Table that holds the data.
Power BI Measure is a formula that defines how the data should be aggregated. In Power BI interface the measures look like the columns in tables, but instead of holding the actual data, it is applying the calculation to the data from columns or other measures in order to calculate its value. Users can add their own measures using Power BI Desktop.
Power BI Report is a visual representation of data from a Power BI Dataset. Unlike Power BI Dashboards, Power BI Report shows data from only one Power BI Dataset.
Power BI Dashboard is a collection of visualizations organized as
Power BI Tiles. Unlike Power BI Report, Power BI Dashboard may show data from multiple datasets.
Power BI Workspace is a storage for Power BI Service content. Every Power BI Service user has by default its own workspace called "My Workspace". All content by default will be saved in that workspace: datasets, reports, dashboards. Power BI Pro users can create additional workspaces and move part of the content into separate workspaces: datasets, reports, dashboards. Such workspaces can then be shared with other Power BI Pro users. This gives possibility to share content through the Power BI Service.
Power BI Integration Concepts
The main goal of the integration is to create Power BI Datasets inside Power BI Service. In order to do so, user first prepares a Dataset in Magento Platform.
Magento Dataset or simply a Dataset is a representation of a Power BI Dataset inside Magento Platform. There are several types of datasets: Mixed Dataset Type and Sales Cube Dataset Type.
Dataset Publishing is a process of Power BI Dataset creation based on Magento Dataset. Initially Dataset is created and configured inside Magento Platform and once it is ready, it can be published. Publishing creates new Power BI Dataset inside the Power BI Service. Publishing delivers only the structure for the data (tables, measures and their relationships) without sending the data itself.
Dataset Republishing is the process of updating the Power BI Dataset with the recent changes of the Magento Dataset`s structure. Not all structural changes might be applied to the existing dataset inside Power BI. For example, adding new tables to Power BI Dataset is not possible now (due to limitations of Power BI REST API).
Dataset Loading is a process of loading of data from the Magento Platform into a Power BI Dataset. Loading can only be done if the dataset is published. Datasets can be loaded multiple times.
Mixed Dataset Type is a type of the dataset that may include different Table Profiles. Data for this dataset may come from sources: CSV files, Magento API, Database, etc. Currently, Sales Cube Dataset Type also supports different data sources.
Table Profile is a representation of a Power BI Table inside Magento Platform. It usually holds the information needed to link particular data from Magento Platform to Power BI Table.
Sales Cube Dataset Type is a complex dataset type with predefined tables and relationships that form comprehensive set of data around Sales process. Table Profiles can be assigned to this dataset type in order to extend the default list of Sales Cube Tables with data from additional data sources.
Bringing it all together
Reports and dashboards are created based on datasets. In order to create a dataset in Power BI Service with Magento data, user should perform the following:
Create and configure a Dataset in the Magento Platform.
Add Table Profiles to the Dataset if needed.
Publish Dataset to Power BI.
Load data to the Power BI Dataset.
As result of this action, dataset from the Magento Platform will be linked with a dataset inside Power BI. If the dataset has Table Profiles assigned to it, they will appear as tables inside Power BI Service.
Loading process describes the process of transferring data from Magento Platform to Power BI Service with usage of Power BI Integration extension from user's perspective.
Step 1: Creation of a Dataset
First, user creates a Dataset. Some datasets are self-contained and does not require any additional actions to include data into the dataset. Some Dataset require also Table Profiles to be created for the dataset to have tables with data in it.
To create dataset user goes to Reports Datasets page in Magento Admin. And on the Datasets page, user should press "Add New Dataset".
If Extension supports more than one dataset type, user will be offered to select the type of the dataset he wants to create:
Dataset type selection dialogue
Mixed Dataset Type would require Table Profiles assigned to it. If the dataset type does not include tables by default, user creates Tables Profiles for the dataset. Creation of Table Profiles could be started from Reports Power BI Table Profiles page.
Sales Cube Dataset Type does not need Table Profiles.
After selecting a dataset type, user will be redirected to dataset creation form:
Dataset creation form
Dataset Name option is the name that will be visible in the list of datasets in Magneto Platform and later in Power BI Service after the dataset will be created.
Workspace name option contains the Power BI Workspace where the Dataset will be published to. Other workspaces then "My Workspace" can be shared with other Power BI users, but in order to use this functionality Power BI Pro license needs to be purchased from Microsoft.
Load Mode option can be "Manual" or contain a schedule name. Default schedules are "Live" and "Regular". If the name of the schedule is selected, the content of the dataset will be automatically updated according to the schedule configuration. In order to be loaded automatically, Dataset needs to be published first.
Is Temporary option allows to made dataset Temporary or Permanent. Usage of temporary dataset may result in loss of the created reports based on that dataset, but this type of the dataset is more usable for the quick preview of the data inside Power BI. Permanent dataset can be published only once.
Dataset Type option cannot be changed at this stage. It shows what type of the dataset user is going to create now.
Remote Dataset ID field is not relevant for dataset creation. It will include Power BI Dataset ID once the Dataset will be published.
Step 2: Creation of Table Profiles (optional)
Sales Cube dataset type does not require creation of Table Profiles. Mixed dataset type is empty by default (has no tables) and requires Table Profiles. Process of Table Profiles creation for a dataset is described in section "Datasets and Table Profiles".
Step: Data Preview in Temporary Dataset (deprecated)
As the next step the user may transfer the report to Power BI Service and see if expected results might be achieved. To do so, user may navigate to created Table Profile and press "Load to Power BI" button.
Since created Table Profile is assigned to a Temporary Dataset, the dataset will be re-published automatically to add new table and the data will be transferred to populate the table with data inside Power BI Service.
Note that Temporary Dataset should not be used for reports or dashboards development if you plan to save them for the future. Every time Temporary Dataset gets republished, related data visualizations will be deleted.
Step 3: Dataset Publishing
If you are ready with your Dataset configuration, you may publish it.
Publishing of the dataset will create Power BI Dataset in Power BI Service and Tables associated with it. Publishing will prepare the storage for the data that will be sent during the loading.
Publishing can be triggered by clicking on "Publish" button on Dataset Properties page.
Step 4: Loading
Loading of a Dataset will populate the Power BI Dataset with latest data available for it.
Loading can be triggered by clicking on "Start Data Loading" button on Dataset Properties page. Please, note that loading is done by crontab process, so it should be configured in order for data to be loaded.
Step 5: Scheduling data refresh
Finally, user may want their reports and dashboards in Power BI Service to be updated automatically with recent data.
The following conditions should be met in order to make sure that scheduled updates are working:
Dataset Load Mode should be set to match the name of one of the Schedules. This option is configured in Dataset Properties page in Magento Platform.
The Schedule to which the Dataset is assigned to has to be activated and configured in Magento Configuration as described in "Basic Configuration: Scheduled updates" section of this document.
Dataset needs to be published.
User should have his Power BI account linked with Power BI Service. State of the connection with Power BI Service can be verified on Power BI Datasets page (Menu Reports Power BI Datasets).
Use should activate Automatic data actualization for his Magento account. This is also done on Power BI Datasets page (Menu Reports Power BI Datasets).
As mentioned in "Terminology: Power BI Integration Concepts", a Dataset is a representation of a Power BI Dataset inside Magento Platform.
The list of Datasets user can see inside Magento Platform by navigating to Reports Power BI Datasets:
Information in the columns "Dataset Name", "Dataset Type", "Is Temporary", "Load Mode" corresponds to dataset properties described in section "General Concepts: Loading Process: Step 1: Creation of a Dataset" of this documentation.
Status column in datasets list shows current status of the dataset. Status column may show the following values:
Not Published -- dataset is created, but was not published yet. It only exists inside Magento Platform.
Published -- dataset is published and is linked with remote dataset inside Power BI Service.
Inconsistent -- dataset is published and is linked with remote dataset, but remote dataset in Power BI Service has different structure (different tables, table names or columns)..
Disconnected -- there is no connection with Power BI Service or user did not connect to its Power BI account.
Missing -- dataset was published and has a link to remote dataset inside Power BI Service, but it does not exist in Power BI. This could be happen in dataset was deleted from Power BI Service or if the user is connected to another Power BI Account, not to the one which was used during the dataset publishing.
Malformed -- some error occurred during retrieval of dataset status. An error can be found in exception logs.
In order to edit or perform any other actions on the dataset, user should click on the dataset's row and navigate to Dataset Properties page. For the simplicity some actions on single dataset can be performed also by selection appropriate option from the list of actions in the Action column.
Dataset Properties Page allows editing the dataset or performing some actions on top of the dataset.
Listed options are described in section "General Concepts: Loading Process: Step 1: Creation of a Dataset" of this documentation.
User can trigger the following actions from the Dataset Properties page:
Delete Dataset -- delete dataset from Magento Platform. This action will not delete dataset from Power BI Service. Published dataset cannot be deleted.
Disconnect dataset from Power BI -- will remove the link to remote dataset and therefor will put the dataset into "Not published" state.
Publish -- performing the publishing of the dataset to Power BI Service.
Start Data Loading -- loading of the data to published dataset.
Delete loaded data -- this will clean up the data in the remote dataset inside Power BI Service.
The dataset properties page also includes a section with information about the data loading state. This helps to monitor the loading process and get insights on the possible issues during the data actualization process.
Depending on the dataset type, Dataset Properties Page may include additional properties specific for the type of the dataset.
Dataset may be self-containing with all tables and relationships (Sales Cube) or may include Table Profiles that will represent the tables for the datasets.
List of Table Profiles can be opened in Magento Platform by navigating to Reports Table Profiles.
Each row in the table lists
Name -- Profile Name.
Type -- type of the Table Profile. This property is used internally. It depends on the way the profile was created.
Created At -- date of the Table Profile creation.
By clicking on the row in Table Profiles list, user can open Table Profile Properties page.
User can change the following properties of Table Profile:
Profile name -- name of the Table Profile for internal reference (used only inside Magento Platform)
Profile type -- the type of Table Profile. Different types connect to different data sources
Also, Table Profile details page may list additional properties depending on its type.
Dataset Tables -- assigning Table Profiles to Datasets
In order to deliver the data from Table Profile into Power BI, this Table Profile must become a part of a dataset.
On Dataset Details page, tab "Tables", the Table Profiles could be assigned to a dataset. Together with the assignment, a name should be given to the table. This is the name that will be used in Power BI. It is possible to include the same Table Profile several times into the same Dataset under different names.
Relationships between tables are needed to get the most from Power BI. Relationships help to automate cross filtering behavior and aggregations of data that is located in different tables.
Relationships are specified on Dataset Details page, tab "Relationships".
It is easy to understand how to use the solution if seen from the perspective of one user. But once more distinct roles are involved it becomes more complex. There are multiple options on how multiple users can organize their work with Power BI Service and Magento Platform and each option might also have different Power BI subscription costs.
To be able to review all use possible options, let's identify the roles that each person may take:
Magento Administrator -- Magento administrator may restrict access to certain functionality and allow only dedicated users to use Power BI Integration or parts of its functionality. In case if Power BI Embedded is used, it might also be possible to restrict access to certain embedded content. Administrators are allowed to view any content inside Magneto Platform
Azure AD Administrator -- role which is able to add new users and manage their permissions and licenses inside Azure AD.
Power BI Integration Datasets administrator -- role for the users who will create, configure and publish the datasets that will contain data from Magento Platform to Power BI Service.
Power BI Service Report Creator -- role for the users who will be creating reports and dashboards inside Power BI Service. They may also create reports using the Power BI Desktop and upload their reports to Power BI Service.
Power BI Service Content Consumers -- role for the users who will be accessing Power BI content using Power BI Service.
Power BI App Content Consumers -- users who may get access to created Reports that were shared with them either from Power BI Service or, if Power BI Embedded extension is used, from inside the Magento Platform.
Power BI Embedded Content Consumers -- such users cannot get access to shared reports inside Power BI Service, but it is possible to share content with such users within Magneto Platform using Power BI Embedded extension.
The general use case is the delivery of the content from the report creators to content consumers. Additionally it might be desired to be able to restrict access to certain content. Report creators may wish to deliver particular reports to particular content consumers.
To split the use cases we review first the delivery and creation of the content in Power BI Service. Other use cases relate to different ways in which the content can be shared with other users.
The simplest use case is to have one user who will play all roles and do everything: from administration and reports creation to consuming the content with Power BI Service and Power BI Mobile App.
In this case it might even be enough to have Power BI Free subscription.
Upgrading to Power BI Pro subscription would make sense to extend the limitations of FREE account and deliver more data, more frequently.
Independent users will create and publish their own datasets and create reports based on those datasets. In this scenario they do not share the content between each other.
One of the advantages of this approach is that each user may stay with Power BI Free subscription and have full flexibility in configuring and publishing the datasets.
The disadvantage is that there might be duplicated data sent from Magento Platform to Power BI Service for multiple users. The more independent users there will be the more performance it will consume from the Magento Platform. At the moment the Power BI Integration Extension can handle only one loading job at a time. And in case if multiple users will be frequently updating their datasets, the process of loading of the data for one user may wait for another processes to finish. Therefore it is not a recommended approach.
The better approach would be to share dataset between the users, but this may require purchasing Power BI Pro subscription for every report creator.
Multiple independent report creators
As in the example with multiple independent users, independent report creators will create and publish their own datasets and create reports based on those datasets.
The advantage would be that each report creator will have the possibility to create datasets that better meets their needs. This might be useful in cases where the dataset is highly configurable and each report creator may prefer its own configuration for the dataset.
As in the independent users scenario, same data might be sent from Magento Platform to Power BI for multiple users. The more independent report creators there will be the more performance it will consume from the Magento Platform. This will also slow down data delivery for independent report creators.
Multiple report creators sharing the same dataset
This approach is more optimal since data might be delivered from Magneto Platform to Power BI Service only once and then be shared with multiple reports creators.
Sharing the access to a dataset would require each report creator to have a Power BI Pro subscription.
One of the users should take over the role of Power BI Integration Datasets Administrator. This user will create dataset and will configure the scheduled update of the data. The dataset can then be published to a Power BI Workspace shared with report creators.
Special use case: share report templates
One special option on how the content of Power BI might be delivered to other users is though Report Templates.
In this case the user may receive a report template that he can upload to Power BI Service. Even though, he was not developing the report himself, but in fact he plays a role of report creator.
This becomes possible if the purchased edition of Power BI Integration Extension supports this functionality.
The content from Power BI Service might be delivered to end users in the following ways:
As their own content from their workspace.
As the content shared with them through workspace.
As the content embedded to Magento Platform.
As the public content shared in the web.
Let's review each option.
Assessing own content in Power BI Service
To access the Power BI Service user needs to have at least Power BI Free subscription (which means that user should be registered and have Power BI Service Account).
Of course in order to have own content, user will need to be create it. So, the user should be a Report Creator.
Own content can be accessed through the Power BI Service, on Power BI App or through embedding into Magento Platform.
If the Power BI Integration Extension supports embedded content in Magento Platform, User can navigate to Reports Power BI Reports (for reports) or Reports Power BI Dashboards (for dashboards) to access the content from Power BI Service.
Accessing content in shared workspaces
Report Creators with Power BI Pro subscription can create workspaces and place their content there. Such workspaces can be shared with other users.
In order to have access to shared content, users also need to have Power BI Pro subscription.
Content shared through workspaces can be accessed through the Power BI Service, on Power BI App or through embedding into Magento Platform. If the Power BI Integration Extension supports embedded content in Magento Platform, User can navigate to Reports Power BI Reports (for reports) or Reports Power BI Dashboards (for dashboards) to access the content from Power BI Service shared with him through workspaces.
Access content embedded into Magento Platform
Report Creators with Power BI Pro subscription can create workspaces and place their content there.
There are two ways, how other users can access embedded content:
Access content from their Power BI account linked with Magento Platform.
Access embedded content without the need to have Power BI Service account.
First option is just an extension to the use cases listed before: user either has his own content in Power BI Service or the content is shared with him through Power BI Workspaces.
Second option would require purchasing Power BI Embedded capacity from Microsoft and assigning this capacity to the Workspace where the shared content is placed.
More detailed about embedding with Power BI Integration Extensions are described in "Embedding Power BI Content" section of this documentation.
Special use cases may popup when different users are intended to have different permissions. Please, contact Extension provider to discuss the solution that will fit the concrete requirements.