Power BI Setup for Dynamics 365 Marketing

   Words by Paul McQuillan

   on 22/04/2020 07:30:00

Assuming we have followed the previous guide to connect the Interaction Data from Dynamics 365 Marketing to our Azure Storage, we can connect this Data to Power BI to start producing detailed analytics for Dynamics 365 Marketing.

This guide will focus on how to get started with Power BI using the Dynamics 365 Marketing Template Packs.

First, we open Power BI in our Tenant of the Microsoft Cloud here: https://app.powerbi.com/

This will give us our initial view of Power BI to start from:

image

Power BI in the Cloud

Ultimately this is where we will be publishing our Power BI Reports to – but for the moment, we can simply use this to download and install the Power BI Desktop App.

image

This will be our main tool to connect to and then report on our D4M Data.

With the Desktop App at the ready, we can then open a .pbit Reporting Template to connect the Model Template to our set of Interaction Data.

We can obtain a variety of pre-built Templates for Dynamics 365 Marketing as these come with all the relevant Connections to connect to the right sources of data, and Reports to then present that data via Power BI.

These are available here: https://docs.microsoft.com/en-us/dynamics365/marketing/marketing-analytics/analytics-gallery-email

I would suggest starting with the Email Marketing Analytics Report, as many organisations start using Dynamics 365 Marketing from an initial Email Marketing standpoint and then move into the more detailed Interaction Data for Web Tracking, Events and detailed Customer Journey’s – and this Report is a good place to do the same when taking your first steps into Power BI and D4M.

Email Marketing Analytics Report

https://docs.microsoft.com/en-us/dynamics365/marketing/marketing-analytics/analytics-gallery-email

https://github.com/microsoft/Dynamics-365-for-Marketing---Power-BI-Reporting/raw/master/PowerBI-Templates/MarketingAnalyzers%20-%20Email%20Marketing.pbit

Once we have the pbit file downloaded, we can simply double click to launch the Power BI Desktop App and get started.

image

This will open the Model in our Desktop App, which will immediately then ask how we want to connect to Dynamics and the Interaction Data in Azure:

image

For a quick primer on what the Power BI Template is looking for from us here:

@CRMOrgUrl

The URL of our Dynamics Marketing App - https://mycrmorg.crm4.dynamics.com/

@AzureStorageAccountName

The name of the Azure Storage Account we connected our D4M Data to – in my example in these guides, this would be ‘crmcsstorage’

@AzureStorageBlogContainerName

The name of the Container in our Azure Storage Account – in my examples here and the previous guide, this would be ‘d4m-blob’

image

@LoadInteractionsForNumberOfDaysBack

This controls how many days history of interactions to read back from your Azure Storage into the Power BI App running locally on your PC – defaults to 7 days for a week of back history.

In Big Data terms, this is the quantity of data to read back from your Data Warehouse (in Azure) into your local Reporting Cube to work with.

The higher the number of days, the longer the next step will take in reading the data from the cloud to your local dev studio.

@MarketingAppId

This the GUID ID of the App in Dynamics that you are using to access CRM – the drop down should give a selection of the Apps it can read from your Tenant of Dynamics, but you can also find this in the Request String when accessing Dynamics 365 Marketing.

image

NOTE: If you have experience with SQL Reporting Services (SSRS), then you might notice the @ symbol notation – yep, their parameters for the way we launch the Model. (the more things change, the more they stay the same)

We then click LOAD to launch the process of the Power BI Desktop App connecting the Model, Dynamics and the Data in Azure.

When prompted you will need to connect to your Azure Blob Storage using your Account Key – this Account Key can be obtained from your Azure Portal or the Azure Storage Desktop App

image

This will then be ready to connect between Power BI on your Desktop, Dynamics in the Cloud, and the Marketing Interactions stored in Azure – this connection period can take 5 to 10 minutes to suck the data from Azure and build the initial model and takes a spot of patience. (or in the IT world, time to offer a round of coffees)

image

When this finishes – we will have something like this presented to us:

image

One thing to note – this will bring back the quantity of Marketing Interactions based on the number of Days we specified when first connecting our Model up to D4M.

So if we want to look beyond the initial 7 days, then we need to go back and increase this duration.

In Dynamics 365 Marketing Terms, this means that if we want to see all of the Email Opens or Clicks for this Insight Chart:

image

We need to make sure we are bringing back more than 7 days – so our Number of Opens reflects the full history of the Marketing Email in our Journey’s, and not just the last 7 days.

image

Viewing the Report Parameter for the Number of Days – after which, we can reload our Data and pull back a longer duration of Marketing Interactions

If in doubt, and looking to avoid getting into the technicalities of Power BI Parameters, we can always re-open the pbit file again and repeat these steps with a larger number of days.

Publish to Power BI

Before we look at how to make changes to the Report in Power BI, we can Publish up to our use of Power BI so we can see and share the report in our Cloud.

From the Power BI Desktop App, we can click Publish and this will publish up to our Power BI area of our Tenant in the Cloud.

image

This will then show the Report in Power BI where we started this guide:

image

This gives us a way of using the D4M Templates to enable a good initial layer of Reporting for our Marketing Team.

What next?

We can look at either surfacing the Power BI Report within Microsoft Teams, or making our changes to the Report to suit how we may want to report on Marketing Email Performance.

We have been an early adopter of helping organisations get the best from Dynamics 365 Marketing and so have a wealth of information, articles and handy tips on our site here.

Share this Article

Search Articles

Filter Articles

CRM Tech DocMan

Recent Articles

Dynamics 365 Marketing vs ClickDimensions It’s time to pause, reflect and acknowledge a new era of inclusivity and collaboration. Part 2 - How to get the most from a Technology Expert – Asset Management Hub Property & Asset Management Hub Part 1 – Balancing CRM and Asset Management Scopes - Asset Management Hub Creating a Multi-Lingual PowerApps Portal How to Set Up a Microsoft Teams Site Using DocDrive365 Microsoft Teams - Adding a Microsoft Teams URL to a Dynamics Appointment Dynamics 365 Marketing – Customer Voice Survey Not Appearing In Emails? Using SQL Management Studio to connect to the Dynamics DB Calling a Power Platform AI Builder Model via oData How to use DocDrive365 to integrate permissions between Business Units in Dynamics with Sites in SharePoint Getting started with the Power Platform AI Builder. Power Apps Portal Information Hub DocDrive365 Security: Day One - Getting Started with Dynamics to SharePoint Permissions Part 5 - Power Apps Portals: How To Connect Azure B2C With Linked-In Part 4 – Power Apps Portals: Styling Azure B2C for Power Apps Portals The 3 Phases for Using Multi-Select Option Sets in Flow with Microsoft Forms Part 3 – PowerApps Portals: Azure B2C and Power Apps Portals – User Flow for Signup and Signin Part 2 - Power Apps Portals: New Application Registration in Azure B2C for our Power Apps Portal Part 1 – Power Apps Portals: Creating a New Azure AD B2C Tenant The Automation Bot: Launching Contextual Flow from Teams Creating a New Bot for Teams Debugging your Teams Bot using Ngrok Adding a Microsoft Teams URL to a Dynamics Appointment
Contact Us

Want expert advice or a demo?

Get in touch now and see how we can help your business grow.

  • Name
  • Email Address
  • Phone Number
 
Close

Understanding Your Challenges

Our strong understanding of CRM and emerging technologies within the Microsoft environment means we deliver the right solutions for you.

Proven Real-World Solutions

As a leader in the field of Dynamics solutions, our pedigree developing and delivering real-world solutions is unsurpassed.

Long Term Support

We provide support beyond our design, implementation and 'go-live' delivery using Sprints and continual updates to our AppSource apps.

CRMCS | Design by Thinktank Marketing | Citrus-Lime Limited

To improve your experience today and in the future, this site uses cookies. Read our full Privacy Policy & Cookie information here I Understand