Power BI / Azure integration

Azure App registration

An app must be registered in Azure so the plugin can communicate with Power BI.

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 Power BI plugin only needs the "Dataset.ReadWrite.All" and "Workspace.Read.All" permissions

   4. Click "Add permission"

3. Now create a "Client secret". Go to "Certificates & secrets" and click "New client secret"

   1. Note down the secret's Value (not the "Secret ID") – it is only shown once, right after creation.

Power BI configuration

In Power BI, service principals must be allowed to use the Power BI 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 "Power BI Service (Service Principal)" access to the workspace. Note: a service principal cannot access the personal "My Workspace", so you must create a dedicated workspace (or use an existing shared one) for the export.

1. Create a new workspace (not "My Workspace") if you don't have a dedicated one yet

2. Open that workspace and click "Manage access"

3. Add your created app

Installation

The plugin can be installed normally like every other plugin. All configuration happens in its own administration module – see below. On install the plugin seeds sensible defaults and a default field mapping for both feeds.

The Power BI module

Open Settings → Power BI. The module is organised in two levels:

• The top level switches between the two export feeds and the shared connection: Order Export, Customer Export and Connection.
• Each feed (Order / Customer) has the sub-tabs Overview, Field Mapping, Associations and Export.

The configuration is global (not per sales channel). Credentials and the dataset are shared between both feeds; only the table name and the push delay are configured per feed.

Connection (shared)

The Connection tab holds everything that both feeds share:

• Client ID: the "Application (client) ID" from the app's Overview page. This is not the client secret's "Secret ID".
• Client Secret: the Value of the client secret you created above – not its "Secret ID". (The Value is only shown once, right after creating the secret.)
• Tenant ID / Directory ID: the "Directory (tenant) ID" from the app's Overview page.
• Workspace ID: the ID of the dedicated workspace (not "My Workspace"), found in the workspace URL, e.g. https://app.powerbi.com/groups/05asdede0-7cc2-4ad7-8e39-acd73fab27dk
• Dataset Name: the name of the dataset to be created (pre-filled with Shopware Export, change if needed).
• Dataset ID: leave empty – filled automatically after "Create dataset".
• Power BI export active: master switch for the live push.

Two actions are available here:

• Test connection: saves the settings and checks that Azure is reachable and the credentials are valid. The status lights on each feed's Overview then reflect the result.
• Create dataset: creates the dataset in Power BI together with the order table and – if the customer feed is enabled – the customer table, based on the configured table names. After success the Dataset ID is filled automatically. Note: Power BI push datasets cannot rename or add tables afterwards, so changing a table name later requires creating the dataset again.

Overview (per feed)

The Overview tab of each feed shows the current setup status and the feed-specific settings:

• Status: Azure connected, dataset exists, table exists, last synchronisation.
• Test connection: live check of the Power BI connection (updates the status lights).
• Clear data: removes all exported rows from this feed's Power BI table (order or customer). A confirmation dialog shows the affected table name.
• Feed settings:
  • Table name: the order resp. customer table inside the dataset (pre-filled with Orders / Customers).
  • Push delay (hours): delay before a newly placed order / newly registered customer is pushed. Useful for orders so invoice number, payment and shipping status can finish updating first. Configured separately per feed. Default 0 (push immediately). Whole hours only.
  • Exported line item types (order feed only): which Shopware line item types are exported (see below).
  • Enable customer feed (customer feed only): activates the automatic export of the customer base.

Changing the table name and saving automatically triggers a schema sync. If the (new) table does not yet exist in the dataset, re-create the dataset on the Connection tab.

Exported line item types (order feed)

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

Field Mapping

Every row that is pushed to Power BI is built from the configured fields of that feed. The order of the fields in the tree is the order of the columns in the Power BI schema. Order and customer feeds each have their own field set.

Every field has three properties:

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

The variable chips above the editor insert the most common variables for the current feed.

Twig context – order feed

The template is rendered once per line item (and once per shipping row, if enabled) with these variables:

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

Examples:

{{ order.orderNumber }}

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

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

Twig context – customer feed

The template is rendered once per customer with these variables:

• customer: the CustomerEntity
• group: the customer group
• salutation
• defaultBillingAddress, defaultShippingAddress

Examples: {{ customer.customerNumber }}, {{ customer.email }}, {{ customer.group.name|default('') }}, {{ customer.createdAt ? customer.createdAt|date('Y-m-d H:i:s') : '' }}

Shipping rows (order feed)

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 (type shipping, label = shipping method name, quantity/unitPrice/totalPrice from the shipping cost, the full CalculatedPrice, position = last position + 1). Templates such as {{ lineItem.label }} or {{ lineItem.price.totalPrice }} work unchanged; product is null on shipping rows, so guard product-specific accesses, e.g. {{ product ? product.productNumber : '' }}.

Test mapping

The Mapping preview button runs the full mapping against a real record (an order for the order feed, a customer for the customer feed) and shows the resulting rows in a table, without pushing anything to Power BI. Use it to verify field values, check the row count and spot Twig errors before going live.

Sync fields

After every change to the mapping (new fields, type changes, import) the table schema in Power BI 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, use Clear data on the Overview first, otherwise Power BI rejects the schema change.

Import / Export

The Export mapping and Import mapping buttons back up and restore the field configuration of the current feed as a JSON file. On import, fields that already exist (matched by name) are overwritten; new ones are added; existing fields whose name is not in the file are left untouched.

Associations

The Associations tab adds additional DAL association paths that are loaded for each record before the mapping is rendered. Each path on its own line. Order and customer feeds keep their own list; the list is used for the live push, the bulk export and the mapping preview.

Order defaults: lineItems.product, orderCustomer.customer.group, deliveries.shippingMethod. Commonly added paths: billingAddress.country, deliveries.stateMachineState, transactions.paymentMethod, transactions.stateMachineState, currency, stateMachineState, documents.documentType.

Customer defaults: group, salutation, defaultBillingAddress, defaultShippingAddress.

Export tab

The Export tab pushes a batch of existing records to Power BI in the background.

• Order feed: optionally restrict by order date (From / To). Leave empty to export all orders.
• Customer feed: optionally restrict by registration date (Registered from / until) to keep the volume down. Leave empty to export the whole customer base.

Start export begins immediately, Cancel export stops a running job, Refresh updates the progress bar. Show history lists previous runs with status, parameters, timestamps and any error message.

Automatic export

• Orders: every newly placed order is pushed automatically (with the configured order push delay) when the live push is active.
• Customers: when the customer feed is enabled, every newly registered customer (including guest accounts) is pushed automatically. Changes to existing customers are not re-pushed; use a bulk export to refresh them.

Email export (plugin configuration page)

The classic daily CSV email export is configured under Extensions → My extensions → Power BI → Configure (the only settings left on the plugin configuration page):

1. Active: activate / deactivate the email export

2. E-Mails: the recipients, 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. A matching email template appears under Settings → Email Templates as PowerBi Export Orders Mail; you can edit its text (no variables are available in it).

Note: the email export currently uses a fixed set of columns and does not use the field mapping. The field mapping only applies to the Power BI push.

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. Open the Connection tab, fill in the Azure credentials, Workspace ID and Dataset Name, and set the order (and, if used, customer) table name in each feed's Overview.
2. If you use the customer feed, enable it in the Customer Export Overview.
3. Click Test connection to verify the credentials.
4. Click Create dataset on the Connection tab to create the dataset and tables.
5. Set the Exported line item types for the order feed (add shipping for shipping costs).
6. Add any additional associations on the Associations tab of each feed.
7. Build the fields under Field Mapping (or import a previously exported JSON).
8. Verify with Mapping preview against a real order / customer.
9. Click Sync fields so Power BI knows the schema.
10. Enable Power BI export active on the Connection tab and/or start a bulk export on the Export tab.

Questions? Check our FAQs or contact us directly: scope01.com/shopware-power-bi-plugin/