Preview: Prepare for analytic reporting with Power BI
Effective November 2020:
- Common Data Service has been renamed to Microsoft Dataverse. Learn more
- Some terminology in Microsoft Dataverse has been updated. For example, entity is now table and field is now column. Learn more
This article will be updated soon to reflect the latest terminology.
Dynamics 365 Marketing provides a wide selection of built-in analytics throughout the application. But you can also create your own custom analytics and reports from your Dynamics 365 Marketing data by using Power BI. We provide endpoints that you can use to connect Power BI to Dynamics 365 Marketing, plus a downloadable Power BI template that you can open in Power BI Desktop, connect to your Dynamics 365 data sources and then customize as needed. When you're done setting it up, you can publish and share your Power BI report using the standard Power BI online tools.
A preview feature is a feature that is not complete, but is made available before it’s officially in a release so customers can get early access and provide feedback. Preview features aren’t meant for production use and may have limited or restricted functionality.
Data sources for custom analytics
To create custom analytics, you can connect two different data sources, each of which provides a different type of data:
- Profile data is stored in the organizational database and includes the entities and records that you see, edit, and create when working directly in Dynamics 365 Marketing. These include common entities like contacts, accounts, leads, events, customer journeys, and more. You'll use the Power BI data connector called "Common Data Services for Apps" for this type of data.
- Interaction data is stored in the marketing-insights service database and includes information about how your contacts interacted with your marketing initiatives, including email opens, email clicks, event registrations, page submissions, and more. You can see this type of information when you look at the insights built into Dynamics 365 Marketing, but you can't create these records nor view them directly. In the current release, you'll use the Power BI "Azure Blob Storage" connector for this type of data. In future releases, you'll also be able use the dataflow connector.
You'll be able to connect directly to your Dynamics 365 Marketing database from Power BI to fetch your profile data, but to access interaction data you'll need to set up Azure Blob Storage, configure Dynamics 365 Marketing to save interaction data there, and then connect Power BI to your blob storage.
Set up Azure Blob storage and connect it to Marketing
Sign into portal.azure.com using the same account where you are running Dynamics 365 Marketing.
If you don't already have one, then create a general-purpose storage account in the Azure Blob storage as described in Quickstart: Upload, download, and list blobs using the Azure portal.
Your general-purpose storage account can be either V1 or V2. Both will work with the Power BI template supplied by Microsoft, but we recommend using V2 if possible because it will support more advanced Power BI capabilities in the future.
Create a blob storage container in your storage account, also as described in Quickstart: Upload, download, and list blobs using the Azure portal.
Launch the Microsoft Azure Storage Explorer, which is a desktop app, and connect it to your Azure account. If you don't already have it, you can download if for free from https://azure.microsoft.com/features/storage-explorer/.
In the Storage Explorer, navigate down to the blob storage container you just created.
Right-click on your blob storage container and then select Get shared access signature from the context menu. The Shared Access Signature dialog opens.
Make the following settings:
- Choose a Start time and Expiry time to establish the period during which your signature will remain valid. Note that the signature must remain valid for as long as you intend to run the export—the export will stop immediately when the signature expires.
- Enable all four Permissions by selecting their check boxes.
Select Create to create the signature. The dialog refreshes to show a URL and Query string. Select the Copy button to copy the URL shown here and paste in a temporary text file so you can use it later in this procedure.
Sign into Dynamics 365 Marketing and go to Settings > Advanced settings > Marketing settings > Marketing analytics configuration. A list of marketing analytics configurations opens. If a record is already listed here, then select it to open it; otherwise, select New from the command bar to create a new record.
The Marketing analytics configuration page opens.
Make the following settings:
- Name: Enter a name to identify this configuration record.
- Export to blob storage URL: Paste the URL you copied earlier in this procedure.
The URL you pasted here includes a connection string to connect to your blob storage. Dynamics 365 Marketing will use this to set up a connection to the blob storage but will then discard it to prevent other people from being able to find it. For this same reason, the connection string will be hidden on the form after submit. This URL won't be stored in the Dynamics 365 database. That means that if you later copy your instance to a sandbox or restore from a backup, then you'll need to re-enter this URL to reestablish the connection to your blob storage in Azure.
Save your settings.
After a short time, Dynamics 365 Marketing will begin to export interaction data as individual files to your blob storage container, where you'll be able to pick it up using Power BI. You'll also be able to see these files and an associated JSON file in the Azure Storage Explorer.
Get Power BI templates and connect them your Marketing data
Once your Azure Blob storage is set up and connected to Dynamics 365 Marketing, you're ready to start working in Power BI to connect to your data sources and design your analytics. Do help get you started, we provide a gallery of pre-built templates and sample reports that you can download, explore, customize, and connect to your Dynamics 365 Marketing instance.