Power BI / Azure integration

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.

Field Configuration

You can find a extra configuration panel under Settings->PowerBi. Here you can define the fields and values which are exported to PowerBi.

In the Field Mapping tab you have a list of all your Fields and a button to synchronize the fields to PowerBi. 

Every field has 3 values:

• Name: The name of the column in PowerBi
• Type: The type in PowerBi e.g.: Int64

• Value: A twig function, which is later rendert

Keep in mind that each row which is exported to PowerBi represents a lineitem of the order.

If you add/remove or change a field, you have to synchronize the fields to PowerBi.

Associations

Under the tab associations you can add additional associations. This is needed if a object property is not loaded automatically. Each association comes in a new line.

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.

Export Orders to PowerBI

Setting the Date Range

From/To:

• Use the calendar picker to select your start/end date
• This determines the earliest/latest order date to include in the export
• If you want to export all orders leave both empty

Actions

Once you've set your date range, you have three action buttons:

Start Export

• Begins the export process immediately
• Exports all orders within the selected date range

Cancel Export

• Cancels any ongoing export process
• Use this if you need to stop an export in progress

Refresh

• Refreshes the current view
• Updates the interface to show the latest status

Monitoring Progress

Show History

• Click this link to view previous export activities
• See status of completed, failed, or ongoing exports
• Review export logs and timestamps

Troubleshooting

• If an export fails, check the history for error details
• Large date ranges may take longer to process - be patient
• Cancel and restart exports if they seem stuck
• Ensure your PowerBI connection is properly configured before starting exports

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