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.All" and "Workspace.Read.All" permissions

   4. Click "Add permission"

3. Now you 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 to the workspace.

1. Go to your workspace (not "My Workspace")

2. Click "Manage access"

3. 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 receive the export, separated by commas

3. CSV-Lineseparator: column separator for the CSV (default is comma)

If enabled, the order data is sent once a day to the configured emails as a CSV file. The first export covers the orders of the current day. From then on, each export covers the orders between the previous run and now.

PowerBi Configuration (for direct export to PowerBi)

1. Active: turn the live push on/off

2. Workspace ID: can be found in the URL, for example https://app.powerbi.com/groups/05asdede0-7cc2-4ad7-8e39-acd73fab27dk

3. Dataset Name: the name of the dataset to be created (must not yet exist in PowerBi)

4. Dataset ID: leave empty: filled automatically after "Create Dataset"

5. Order Table Name: the name of the order table inside the dataset

6. Exported line item types: which Shopware line item types are exported. Default: only product. Available values:

   • product: regular product positions
   • promotion: promotion / voucher items
   • custom: manually added items
   • credit: credits
   • discount: discounts
   • container: container line items
   • quantity: quantity items
   • shipping: shipping costs as a synthetic line item (see below)

7. Push delay (hours): delay (in hours) before each newly placed order is pushed to PowerBi. Useful to wait for invoice number, status changes etc. to be filled in before the snapshot is taken. Default: 0 (push immediately).

All PowerBi settings can be configured per sales channel.

Azure Configuration (for direct export to PowerBi)

1. Client ID: shown on the overview page of the Azure App Registration

2. Client Secret: the value of the client secret you created above

3. Tenant ID / Directory ID: shown on the overview page of the Azure App Registration

Actions

The Actions card on the plugin configuration page provides the following buttons:

• Test Azure Connection: checks that Client ID, Client Secret and Tenant ID are correct.
• Test PowerBi Settings: checks that the Workspace ID is reachable and (if a Dataset ID is already set) that the dataset and order table exist.
• Create Dataset: creates the dataset and order table in PowerBi based on the configured Dataset Name and Order Table Name. After success the Dataset ID field is filled automatically.
• Initialise Orderfeed: (re)generates the CSV order feed in the background.
• Clear Dataset: removes all rows from the PowerBi order table. Useful before re-importing data with a changed schema.

Field Mapping

Open Settings → Power BI. The page has three tabs: Field Mapping, Associations and Export.

Each row that is pushed to PowerBi is built from the configured fields. The order of the fields in the tree is the order of columns in the PowerBi schema.

Every field has three properties:

• Name: the name of the column in PowerBi
• Type: the PowerBi data type:string, Int64, Double, bool or DateTime
• Value: a Twig template that is rendered once per line item

Twig context

The following variables are available inside the Value template:

• order: the OrderEntity with all loaded associations
• lineItem: the current OrderLineItemEntity (also a synthetic entity for shipping rows, see below)
• product: the ProductEntity, or null for non-product line items
• orderCustomer, customer

Everything that hangs off these entities (including lineItem.payload, order.deliveries, order.transactions etc.) can be accessed as usual via Twig property syntax, provided the matching association is loaded.

Examples:

{{ order.orderNumber }}

{{ order.stateMachineState ? order.stateMachineState.name : '' }}

{% set t = order.transactions ? order.transactions.last : null %}{{ t and t.paymentMethod ? t.paymentMethod.name : '' }}

{% set ct = lineItem.price and lineItem.price.calculatedTaxes ? lineItem.price.calculatedTaxes.first : null %}{{ ct ? ct.taxRate : '' }}

Shipping rows

When the shipping line item type is enabled, an additional row is produced for every delivery whose shipping cost total is non-zero. The plugin builds a synthetic OrderLineItemEntity with:

• type:shipping
• label: name of the shipping method
• quantity, unitPrice, totalPrice: taken from the shipping cost
• price: the full CalculatedPrice (including taxes)
• position: last position + 1

Existing templates such as {{ lineItem.label }} or {{ lineItem.price.totalPrice }} work for shipping rows without changes. product is null on shipping rows, so wrap product-specific accesses in a conditional, for example {{ product ? product.productNumber : '' }}.

Test mapping

The Test mapping button opens a dialog where you can pick any existing order. The plugin then runs the full mapping against that order and shows the resulting rows in a table, without pushing anything to PowerBi. Use it to:

• verify that all configured fields are filled correctly
• check that the number of rows matches your expectation (line items plus shipping rows, if enabled)
• spot Twig errors in your templates

Import / Export

The buttons Export mapping and Import mapping let you back up and restore the entire field configuration as a JSON file.

• Export downloads a JSON file containing every field with its id, name, type, value and afterId.
• Import uploads such a JSON file and merges it into the current configuration:        
  • Fields that already exist (matched by name) are overwritten: type, value and position are taken from the file.
  • Fields that do not yet exist are added.
  • Existing fields whose name is not in the file are left untouched.

Sync fields

After every change to the mapping (new fields, type changes, import) the table schema in PowerBi must be aligned. Use the Sync fields button at the top of the Field Mapping page. If you change the type of a column that already holds data, click Clear Dataset on the plugin configuration page first, otherwise PowerBi would otherwise reject the schema change.

Associations

The Associations tab lets you add additional DAL association paths that are loaded for each order before the mapping is rendered. Each path goes on its own line. This list is used for the live push, the bulk export and the "Test mapping" preview.

Default associations are lineItems.product, orderCustomer.customer.group and deliveries.shippingMethod. Examples of additional paths that are commonly needed:

• billingAddress.country
• deliveries.shippingOrderAddress.country
• deliveries.stateMachineState
• transactions.paymentMethod
• transactions.stateMachineState
• currency
• stateMachineState
• documents.documentType

Email Template

After installation a new email template appears under Settings → Email Templates with the name PowerBi Export Orders Mail. You can insert your own text. There are no variables available in this template.

Export Orders to PowerBI

The Export tab on the plugin page lets you push a date-bounded batch of existing 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
• To export all orders, leave both empty

Actions

Once you've set your date range, three buttons are available:

Start Export

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

Cancel Export

• Cancels the currently running export

Refresh

• Updates the displayed status / progress bar of the running export

Monitoring progress

A progress bar is shown while an export is running. The link Show History below the buttons toggles a list of previous export runs with status, parameters, timestamps and any error message.

Troubleshooting

• If an export fails, expand the history for error details
• Large date ranges may take longer to process, be patient
• Cancel and restart exports if they seem stuck
• If a field is empty in the "Test mapping" preview, an association is usually missing, add it on the Associations tab
• Run Test Azure Connection and Test PowerBi Settings on the plugin configuration page to verify credentials

Messenger

The live push and the bulk export run via Shopware's Messenger queue. Make sure the standard Shopware workers are running so messages are processed:

php bin/console scheduled-task:run

php bin/console messenger:consume async

Recommended workflow

1. Fill in the Azure and PowerBi configuration.
2. On the plugin configuration page click Test Azure Connection and Test PowerBi Settings to verify credentials.
3. Click Create Dataset to create the dataset and order table in PowerBi.
4. Set the Exported line item types (at minimum product, add shipping if you want shipping costs).
5. Add any additional associations on the Associations tab.
6. Build the fields under Field Mapping (or import a previously exported JSON).
7. Verify the mapping with Test mapping against a real order.
8. Click Sync fields so PowerBi knows the schema.
9. Enable the live push (Active in PowerBi configuration) and/or start a bulk export on the Export tab.