Recently added item(s)
You have no items in your shopping cart.
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. It is important to leave the possibility of making changes in the future while being able to keep stable data delivery to dashboards and reports.
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.
Some additional concepts were introduced to find a way around those limitations. Those are among the others the Temporary and Permanent datasets.
Some of the basic terminology is described in this section. This includes terminology from Power BI Service and from Power BI Integration Extensions.
The 4 major building blocks of Power BI are: dashboards, reports, workbooks, and datasets. And they're all organized into workspaces. Datasets are organized in tables. These concepts are described in Power BI Service Documentation:
Those concepts will be referred as Power BI Workspaces, Power BI Dashboards, Power BI Reports, Power BI Datasets and Power BI Tables.
Power BI Datasets is a collection of data organized in one or more tables. It is not only holding the data itself, but also keeps 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 the 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 Report is a visual representation of data from a Power BI Dataset. Unlike Power BI Dashboards, Power BI Report only shows data from 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 Tile is a single visual representation of data, placed on a dashboard.
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 Dataset is a representation of a Power BI Dataset inside Magento Platform. There are different types of datasets: Mixed Dataset Type, Sales Cube Dataset Type or other custom types. Datasets also can be classified as Temporary or Permanent.
Dataset Publishing is the process of Power BI Dataset creation based on Magento Dataset. Initially Dataset is created and configured inside Magento Platform and when it is ready, it can be published to create the Power BI Dataset inside the Power BI Service. Publishing delivers only prepares the storage for the data without sending the data itself.
Dataset Republishing is the process of updating the Power BI Dataset with the recent changes inside Magento Dataset structure. Not all changes might be applied. For example, adding new tables to Power BI Dataset is not possible at the moment (due to limitations of Power BI REST API). Therefore, republishing sometimes may result in Power BI Dataset deletion and re-creation (with loss of all related reports) and is only allowed for Temporary Datasets. In some cases the Power BI Integration Extension might try to update existing Power BI Dataset without deletion of the content (updating calculated measure definition or change of column type), but this is relevant only for advanced customizations or for applying the Power BI Integration Extension updates.
Dataset Loading or Table Profile Loading is the process of loading of the data from Magento Platform to Power BI Dataset. It does not change the amount of tables in a dataset or their names. Any Table Profile or Dataset can be loaded multiple times. If Table Profile is assigned to a Permanent Dataset, it can be loaded only after publishing of the Dataset. If Load Profile is assigned to a Temporary Dataset, dataset publishing will be triggered during the load automatically if needed.
Mixed Dataset Type is a type of the dataset that may include Table Profiles. It is called as mixed because data of such dataset may come from different places.
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 cannot be assigned to this dataset type.
Temporary Dataset is a dataset that can re-published. Re-publishing might cause loss of reports and dashboards associated with the dataset. Current implementation supports re-publishing only through delete/create operations. It means that internally old dataset should be deleted from Power BI Service and new dataset has to be published again. Temporary datasets are not intended to be used to create reports and dashboards that will stay to be used in the future.
Permanent Dataset is a dataset that might be published only once. This is intended to protect user from unintentional loss of reports and dashboards. Permanent Dataset cannot be re-published. Once Permanent Dataset gets published, it should not be possible to make modifications to the Dataset structure (amount of tables and their names).
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 there part of the content: 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.
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:
As result of this action, Dataset from Magento Platform will result in dataset inside Power BI. If the dataset have Table Profiles, they will translate into Power BI Tables inside.
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.
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:
Picture: 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. To create a Table Profile based on a Magento Report, user navigates to a Magento Report page, fills in the values for the report options and triggers the report data to be shown. After that user should select “Power BI” in the list of export options and click “Export”.
Sales Cube Dataset Type does not need Table Profiles.
After selecting dataset type, user will be redirected to dataset creation form:
Picture: 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.
Report Dataset ID field is not relevant for dataset creation. It will include Power BI Dataset ID once the Dataset will be published.
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.
If you are ready with your Load Dataset and you have assigned all Load Profiles you want to use with this dataset, you may publish it.
Publishing of the dataset will create Power BI Dataset in Power BI Service with 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.
Loading of a Dataset will populate the Power BI Dataset with latest data available for it.
Loading can be triggered by clicking on “Load” button on Dataset Properties page.
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:
As mentioned in “Terminology: Power BI Integration Concepts”, a Dataset is a representation of a Power BI Dataset inside Magento Platform.
List of Datasets user can see inside Magento Platform by navigating to Reports -> Datasets:
Picture: list of datasets inside Magento Platform
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:
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.
Picture: dataset properties page
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:
Depending on the dataset type, Dataset Properties Page may include additional properties specific for particular dataset type.
Latest version of the extension will also include information about the data loading state. This will help to monitor the loading process.
Picture: Data Loading Information
Dataset may be self-containing with all tables and relationships or may include Table Profiles.
List of Table Profiles can be opened in Magento Platform by navigating to Reports -> Table Profiles.
Picture: Table Profiles list
Each row in the table lists
Status column in Table Profiles list may get the following values:
By clicking on the row in Table Profiles list, user can open Table Profile Properties page.
Picture: Table Profile properties
User can change the following properties of Table Profile:
Also, Table Profile may list additional properties depending of its type.
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:
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.
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.
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.
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:
Let’s review each option.
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.
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.
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:
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.