ADVANCED FEATURES

SCHEMA EDITOR

If more flexibility is required, users may also change the schema definition of the dataset. Typical use cases might be:

• duplicate some tables to create reports that have two filters over the same dimension, introducing role-playing dimensions

• hide some columns, tables

• adding tables with custom made measures

• changing relationships between tables

In order to open schema editor, navigate to datasets list and select action "Edit Schema (advanced)" in the actions menu as presented on the picture below.

Sales Cube dataset will have quite advanced schema from by default. You may use this schema as an example to understand how it could be adjusted.

Once finished, press "Save" button to save the changes in the schema. The changes will not be applied to the dataset in Power BI. To apply this changes, the dataset will have to be published again. In case if changes in the schema are not adding or removing any tables, the changes might be applied without reloading of the data. But in some cases, data reload or even, delete/publish actions might be required.

In order to cancel the manual changes in the dataset, simply press on "Restore Default Schema".

REPORT TEMPLATES

Use Cases

Report Templates is a functionality that allows to user Power BI APIs to download and upload Power BI reports from Magento Platform to Power BI Service and back.

Please, not that this is not the same templates that could be created from Power BI Desktop.

This functionality is very useful for some use cases and gives possibility to save the work that was done by user during report creation. In particular, it becomes possible to do the following:

1. Receive initial set of reports with purchasing of some Power BI Integration Extensions.

2. Download Power BI reports created on top of a dataset that needs to be deleted.

3. Migrate Power BI reports from one dataset to another.

4. Backup Power BI reports to make sure, they will not be lost.

5. Restore the backed up Power BI reports.

6. Migrate Power BI reports from one account to another (if workspaces could not be used for some reason).

7. Migrate Power BI reports from one workspace to another.

List of Report Templates

List of Report Templates could be found by navigating to Reports Power BI Templates.

• Template Name column shows a name given to the report template.

• File Name column has a report name in form of the file name.

• Source column shows how the template was created, where it comes from.

• Description provides short description of the content and the purpose of the report.

• Quick action column helps users to navigate to template details page.

Report Details page

Opening the Template Details page will show a form where user can change some properties of the Report Template.

• Delete action will delete the report template from the Magento

• Platform.

• Download Report Template action will download the report as pbix file.

• Publish Report action will redirect user to report publishing dialogue to upload a report template to Power BI and create Power BI report based on the template.

Uploading the report

To upload a Report Template, open template details and click on "Publish Report" button.

This will open the report publishing form where user can selecte target workspace, dataset that will be bound to the report and the name for the report that will be created in Power BI Service.

Downloading of the report

To download the report from Power BI Service and save it as a Report Template, user can press button "Create Template from remote Report" on the page with Report Templates list.

This will open the form where user can select the remote report to download and specify the properties of the report template.

• Report to export from Power BI option allows user to select a workspace and a report in Power BI Service.

• New Template Name will include short name for the report template.

• File Name is a name that will be given to the report for internal storage and while downloading of the pbix file from Magento Platform.

• New Template Description should include short description of the content and the purpose of the report.

Click on "Export Report" will trigger the downloading process and will create a new Report Template.

ETL JOBS

Most of the functionality of the Power BI Integration extension is using scheduler that performs ETL jobs. ETL translates into Extract-Transform-Load. Those jobs are mostly responsible for data handling: extraction of data from Magento Platform, transforming the data for fitting the expected format and structure for Power BI Service, loading of the data to Power BI.

List of ETL jobs can be opened by navigating to Reports ETL Jobs. There user can see the list of the scheduled jobs and some information about them.

• Job Type includes indication of the nature of the job. Possible values are: "Setup", "Export" (data extraction), "Transform" and "Load".

• Job ID is internal unique identified of the job.

• Schedule is the schedule by which the job is running. Please, note some jobs might be triggered as dependencies from other jobs, not only by its own schedule.

• Status column shows the current status: if the job is in the jobs queue or is already running. Empty values means that job is not in the currently processed queue.

• Error column may indicate if there were some issues with latest run of the job.

• Ex. Time column shows execution time -- how long it took to process this job last time it was run.

• Last Run -- date and time when the job was run the last time.

• Next Run -- date and time when the job is planned to be run for the next time.

User can also perform some quick actions on the jobs:

• Run action will trigger the processing of the job;

• Run Chain action will trigger the job and all other jobs that this job depends on.

USAGE OF DEV TOOLS

Additionally, Power BI Integration Extensions come with extra page that includes some functionality that might be useful during the development and maintenance.

User can open Dev Tools page by navigating to Reports Insights Ready Dev Tools.

This page includes some descriptions on the listed functionality could be used. Here we review some use cases when such functionality might be useful.

Assigning datasets and assignment removal

From the Dev Tools page user can navigate to "Dataset assignment form" and to "Dataset assignment removal form".

The standard workflow of using the Power BI Integration Extensions is to create a dataset in Magento Platform, publish it to Power BI Service and then just keep loading data to that dataset. But in some cases this workflow might need to have some adjustments. The manual dataset assignment functionality helps to manually assign dataset inside Magento Platform to a dataset in Power BI Service.

On the dataset assignment form, user selects the dataset from Magento Platform, then the dataset in Power BI Service and first need to run the compatibility check.

Check Compatibility will compare the structure of both datasets to validate that there will be no error happening during loading of the data.

This functionality does not guarantee the complete compatibility between the datasets, but helps to prevent some of the errors.

If the system did not determine incompatibilities between the datasets, user will be offered to assign the datasets together. After the assignment, it becomes possible to load the data to Power BI dataset that was disconnected from Magento before.

Dataset assignment removal is quite simple form that only accepts the dataset inside Magento Platform. Triggering the "Remove assignment" action will put the dataset from "Published" state to a "Not Published".

Sales Cube Dev Tools

If the Sales Cube Dataset functionality is available in Magneto, user will get access to some development tools for maintenance of Sales Cube Datasets.

Most important actions are: doing manual Sales Cube clean-up and performing capacity check.

Sales Cube clean-up

From Dev Tools page users can navigate to Sales Cube clean-up page. The clean-up might be configured to cover different scope.

Sales Cube datasets published to Power BI will only clean-up all datasets published to Power BI. Please, note that not only datasets of current admin user will be affected, but all Sales Cube Datasets created in Magento Platform and linked with remote Power BI datasets.

Sequence numbers and published datasets will do the same as previous option, but will also clean-up sequence numbers. Clean-up of sequence numbers is useful because it will increase the performance of the reload of the data. Sequence numbers influence the amount of rows that will be sent with every data load request. If the iterative load was running for some time, sequence numbers will not be generated efficiently.

DWH data, sequence numbers and published datasets will do the same as previous option, but will also clean-up DWH data. This option is useful if the DWH building was changed and the data transformation logic needs to be run again.

Staging area, DWH data, sequence numbers and published datasets covers full scope of data handling for Sales Cube Datasets. This scope covers the scope of previous option, but will additionally clean-up the staging area. Staging area is where the data are stored after being extracted from Magento Platform. This means that the data will need to be extracted from Magento Platform again. This is the option that needs to be selected if user wants to refresh completely the data inside the DWH. This will do Slowly Changing Dimensions (SCD) refreshing by updating product names, category names, linking between categories and products and so on. Read about SCD in "Sales Cube Data Dictionary" document.

Sales Cube capacity check

Due to limitations of Power BI, even Power BI Pro users may not always be able to load full history of data within one dataset in Power BI Service. Loading of data to Power BI Service may take several days until all data that will fill in the dataset will be delivered to Power BI. In order to be able to know upfront if the data for a dataset will fit inside Power BI Service, user can do Capacity Check.

The outcome of the capacity check will show what the maximum amount of rows for a single table is and will show amount of tables that exceed maximum allowed size of 5,000,000 rows.

CHANGING THE STRUCTURE OF PERMANENT DATASET

It is better to plan your dashboards and reports development in advance and start with dataset that contains all data that will be needed. This is not always possible and sometimes it might be required to update the dataset structure even though it has already been published and was used to create some reports. It does not apply to Sales Cube dataset which might be changed only during upgrade of the Power BI Integration Extension.

If you want to modify Dataset, you will need to first remove the link to Power BI Dataset. After removing the link from the old Power BI Dataset it will not be possible to update data in that dataset. Read "Advanced features: Assigning datasets and assignment removal" to learn how to unlink a Dataset from Power BI Service.

Once modifications are done, the Dataset can be published again as a new Power BI Dataset. Alternatively it could be possible to try to assign modified dataset to the remote Power BI dataset, but only if modifications are not related to amount of tables or to table names.

Users who have Report Templates functionality coming with their Power BI Integration Extension can create Templates from the reports created based on the old version of the dataset and publish those Templates with link to the new Dataset. Read "Advanced features: Report Templates" to learn how to use Templates.

CONFIGURING ACCESS CONTROL

Magento supports flexible configuration of access rights for different backend user roles. The access could be restricted to certain resources. The Power BI Integration extensions introduce additional access control resources.

Most of them will restrict access to the underlying backend pages. "Insights Ready OData API" resource allows restricting access to OData API feed.