Power BI / Azure Integration

Azure and PowerBi configuration

Azure App registration

An app must be registered in Azure for the plugin to communicate with PowerBi.

1. Go to “App registrations” in Azure and click “New registration”

   1. Name: the name of the app for example the store name

   2. Supported account types

   3. Redirect URL: for example the store URL

2. Go to “Api permissions” in the created app and click “Add a permission“

   1. Select “Power Bi Service”

   2. Select “Delegated permissions”

   3. The PowerBi plugin only needs “Dataset.ReadWrite.Al”  and “Workspace.Read.All” permissions

   4. Click “Add permission”

3. Now we need to create a “Client secret”. Go to “Certificates & secrets” and click “New client secret”

   1. Note down the “Value”.

PowerBi configuration

In PowerBi service principals must be allowed to use the PowerBi API.

1. Go to the “Admin portal” under settings.

2. Under “Tenant settings” search for “Developer settings”

   1. Enable “Allow service principals to use Power BI APIs”

You also need to give the “PowerBi Service (Service Principal)” access for the workspace.

1. Go to your workspace (not “My Workspace”)

2. Click “Manage access”

3. Add “PowerBi Service (Service Principal)”

4. Add your created App
   

Installation and configuration

The Plugin can be installed normally like every other Plugin.

Settings

Email Export

1. Active: activate / deactivate email export

2. E-Mails: The emails that should get the email separated with a comma

If enabled, the order data will be sent once a day to the configured emails in a csv file.

The first export will export the orders from the current day. After that, the orders from the last execution until now are exported.

PowerBi Configuration (for direct export to PowerBi)

1. Set Workspace-ID: can be found in Url (for example: https://app.powerbi.com/groups/05asdede0-7cc2-4ad7-8e39-acd73fab27dk )

2. Dataset Name: choose a dataset name (must not exist in PowerBi)

3. Dataset ID: leave empty

4. Order Table Name: choose name for order table in PowerBi dataset

Azure Configuration (for direct export to PowerBi)

1. Client ID : can be found on overview page of Azure App Registration

2. Client Secret: above created in Azure App Registration

3. Tenant ID / Directory ID: can be found on overview page of Azure App Registration

After configuration

After configuration is done run php bin/console powerbi:init. This command will create the dataset und table in PowerBi.

Email Template

After installation, a new email template appears under Settings->Email Templates with the name PowerBi Export Orders Mail

Here you can insert your own text. Important: there are no variables available.

Messenger

Make sure all Shopware cron jobs are running.

php bin/console scheduled-task:run

php bin/console messenger:consume async

Technical

Events

EmailExportLineItemEvent

Is called every time a line item is added to a the CSV file.

Example:

This will add a column to the CSV with the name “custom_name” und the value of the custom field “test“.

BeforeCsvCreatedEvent

Is called before CSV is created finally.

This will add a column to the CSV with the name “custom_name” und the value of the custom field “test“.

Exported fields

• OrderNo

• OrderPos

• Date

• ArticleNumber

• ArticleDescription

• Amount

• Units

• Price

• User

• Segment

Important Classes

Classname // Function

Migration1686394103AddMailTemplate: Create the default email template on installation

ExportOrdersTaskHandler: Export of order data to csv and sending the Email

PowerBiClient: Handles connection to PowerBi

SendOrderHandler: Sends order to PowerBi

Roadmap

• SalesChannel Config for Email Export

  • if desired, add new option to Config:

    • export in one file

    • one or separate file per SalesChannel

• Customize the exported fields (Email Export and PowerBi API)

• Create FlowBuilder Actions and Triggers for easier manipulation of data

• UnitTesting

• Integration of a PowerBi report into the Shopware backend https://learn.microsoft.com/en-gb/power-bi/collaborate-share/service-embed-secure )

• Integration of targets (ScoreCards) from PowerBI

• Export to Microsoft SharePoint