General concepts

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.

Terminology

Power BI Service Concepts

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:

https://docs.microsoft.com/en-us/power-bi/service-basic-concepts

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.

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 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).

Bringing it all together

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:

1. Create and configure Dataset in Magento Platform.
2. Add Table Profiles to the Dataset if needed.
3. Publish Dataset to Power BI.
4. Load data to the Power BI Dataset.

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

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:

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.

Step 2: Data Preview in Temporary Dataset

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 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.

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 “Load” button on Dataset Properties page.

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:

1. Dataset Load Mode should not be set to match the name of one of the Schedules. This option is configured in Dataset Properties page in Magento Platform;
2. 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;
3. Dataset needs to be published;
4. User should have his Power BI account linked with Power BI Service. State of the connection with Power BI Service can be verified on Datasets page (Menu Reports -> Datasets);
5. Use should activate Automatic data actualization for his Magento account. This is also done on Datasets page (Menu Reports -> Datasets).
6. If the Dataset has Table Profiles assigned, those table profiles should be set to “Active” on Table Profile Properties page.

Datasets and Table Profiles

Managing Datasets

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:

• 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.

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:

• 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.
• 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.

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

Managing Table Profiles

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

• Name - Profile Name.
• Dataset - dataset to which the profile is assigned.
• Table – Power BI Table 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.
• Load Mode – configured Load Mode for this Table Profile.
• Status – status of the Table Profile.
• Action – list of quick actions that could be performed on the Table Profile.

Status column in Table Profiles list may get the following values:

• Inactive – Table Profile is set to inactive.
• Inconsistent – Table Profile is assigned to a dataset which is published, but Power BI Table is not in the dataset or has different columns.
• Temporary – Table Profile was published with its dataset, but the dataset is Temporary.
• Published – Table Profile was published with permanent dataset.
• Not Published – Table Profile is configured as active, but was not published yet (linked dataset is also not published).
• Disconnected - there is no connection with Power BI Service or user did not connect to its Power BI account.
• Invalid Profile – Table Profile is not supported by currently installed version of the Power BI Integration Extension or is malformed.

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:

• Profile Name – name of the Table Profile for internal reference (used only inside Magento Platform)
• Table Name – name for the Power BI Table. Once the dataset with this Table Profile will be published, it will include table with the given name associated with its Table Profile.
• Dataset Name – the dataset where this Table Profile is assigned to.
• Is Active – determines if the table profile is active and can be published and loaded to Power BI or it is deactivated.
• Load Mode – property similar to dataset Load Mode. Can take value “Manual” for loading of this profile only if the user triggers loading manually or in cases when the whole dataset is loaded. Load Mode may also get a value of one of the available Schedules: Live or Regular. In this case the Table Profile will be loaded by the assigned schedule even if the associated dataset is loaded more rarely. In most of the cases this property may be left as “Manual” and loading of the profile can be controlled through the dataset. It makes sense to set this property only in cases when different table of the same dataset should be loaded by different schedules.

Also, Table Profile may list additional properties depending of its type.

Multiple users

Roles

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.

Simplest use case: single user scenario

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.

Simplest multiuser use case: multiple independent users

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.

Use cases of content delivery

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.

Use cases of content consuming

The content from Power BI Service might be delivered to end users in the following ways:

1. As their own content from their workspace.
2. As the content shared with them through workspace.
3. As the content embedded to Magento Platform.
4. 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:

1. Access content from their Power BI account linked with Magento Platform.
2. 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.

Multiple users with different permissions

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.