{"id":175,"date":"2022-01-06T11:09:13","date_gmt":"2022-01-06T11:09:13","guid":{"rendered":"https:\/\/blog.citrus-lime.com\/crmc\/?p=175"},"modified":"2022-01-06T11:09:13","modified_gmt":"2022-01-06T11:09:13","slug":"power-bi-setup-for-dynamics-365-marketing","status":"publish","type":"post","link":"https:\/\/blog.citrus-lime.com\/crmc\/power-bi-setup-for-dynamics-365-marketing\/","title":{"rendered":"Power BI Setup for Dynamics 365 Marketing"},"content":{"rendered":"\n

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

This guide will focus on how to get started with Power BI using the Dynamics 365 Marketing Template Packs.<\/p>\n\n\n\n

First, we open Power BI in our Tenant of the Microsoft Cloud here: https:\/\/app.powerbi.com\/<\/strong><\/a><\/p>\n\n\n\n

This will give us our initial view of Power BI to start from:<\/p>\n\n\n\n

\"image\"<\/a><\/figure>\n\n\n\n

Power BI in the Cloud<\/em><\/strong><\/p>\n\n\n\n

Ultimately this is where we will be publishing our Power BI Reports to \u2013 but for the moment, we can simply use this to download and install the Power BI Desktop App.<\/p>\n\n\n\n

\"image\"<\/a><\/figure>\n\n\n\n

This will be our main tool to connect to and then report on our D4M Data.<\/p>\n\n\n\n

With the Desktop App at the ready, we can then open a .pbit<\/strong> Reporting Template to connect the Model Template to our set of Interaction Data.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

These are available here: https:\/\/docs.microsoft.com\/en-us\/dynamics365\/marketing\/marketing-analytics\/analytics-gallery-email<\/strong><\/a><\/p>\n\n\n\n

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\u2019s \u2013 and this Report is a good place to do the same when taking your first steps into Power BI and D4M.<\/p>\n\n\n\n

Email Marketing Analytics Report<\/strong><\/p>

https:\/\/docs.microsoft.com\/en-us\/dynamics365\/marketing\/marketing-analytics\/analytics-gallery-email<\/a><\/p>

https:\/\/github.com\/microsoft\/Dynamics-365-for-Marketing—Power-BI-Reporting\/raw\/master\/PowerBI-Templates\/MarketingAnalyzers%20-%20Email%20Marketing.pbit<\/a><\/p><\/blockquote>\n\n\n\n

Once we have the pbit file downloaded, we can simply double click to launch the Power BI Desktop App and get started.<\/p>\n\n\n\n

\"image\"<\/a><\/figure>\n\n\n\n

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:<\/p>\n\n\n\n

\"image\"<\/a><\/figure>\n\n\n\n

For a quick primer on what the Power BI Template is looking for from us here:<\/p>\n\n\n\n

@CRMOrgUrl<\/strong><\/p>

The URL of our Dynamics Marketing App – https:\/\/mycrmorg.crm4.dynamics.com\/<\/a><\/p>

@AzureStorageAccountName<\/strong><\/p>

The name of the Azure Storage Account we connected our D4M Data to \u2013 in my example in these guides, this would be \u2018crmcsstorage\u2019<\/p>

@AzureStorageBlogContainerName<\/strong><\/p>

The name of the Container in our Azure Storage Account \u2013 in my examples here and the previous guide, this would be \u2018d4m-blob\u2019<\/p><\/blockquote>\n\n\n\n

\"image\"<\/a><\/figure>\n\n\n\n

@LoadInteractionsForNumberOfDaysBack<\/strong><\/p>

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 \u2013 defaults to 7 days for a week of back history.<\/p>

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.<\/p>

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.<\/p>

@MarketingAppId<\/strong><\/p>

This the GUID ID of the App in Dynamics that you are using to access CRM \u2013 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.<\/p><\/blockquote>\n\n\n\n

\"image\"<\/a><\/figure>\n\n\n\n

NOTE: <\/strong>If you have experience with SQL Reporting Services (SSRS), then you might notice the @ symbol notation \u2013 yep, their parameters for the way we launch the Model. (the more things change, the more they stay the same)<\/em><\/p><\/blockquote>\n\n\n\n

We then click LOAD to launch the process of the Power BI Desktop App connecting the Model, Dynamics and the Data in Azure.<\/p>\n\n\n\n

When prompted you will need to connect to your Azure Blob Storage using your Account Key \u2013 this Account Key can be obtained from your Azure Portal or the Azure Storage Desktop App<\/p>\n\n\n\n

\"image\"<\/a><\/figure>\n\n\n\n

This will then be ready to connect between Power BI on your Desktop, Dynamics in the Cloud, and the Marketing Interactions stored in Azure \u2013 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)<\/p>\n\n\n\n

\"image\"<\/a><\/figure>\n\n\n\n

When this finishes \u2013 we will have something like this presented to us:<\/p>\n\n\n\n

\"image\"<\/a><\/figure>\n\n\n\n

One thing to note \u2013 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.<\/p>\n\n\n\n

So if we want to look beyond the initial 7 days, then we need to go back and increase this duration.<\/p>\n\n\n\n

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

\"image\"<\/a><\/figure>\n\n\n\n

We need to make sure we are bringing back more than 7 days \u2013 so our Number of Opens reflects the full history of the Marketing Email in our Journey\u2019s, and not just the last 7 days.<\/p>\n\n\n\n

\"image\"<\/a><\/figure>\n\n\n\n

Viewing the Report Parameter for the Number of Days \u2013 after which, we can reload our Data and pull back a longer duration of Marketing Interactions<\/em><\/strong><\/p>\n\n\n\n

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

Publish to Power BI<\/h3>\n\n\n\n

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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

\"image\"<\/a><\/figure>\n\n\n\n

This will then show the Report in Power BI where we started this guide:<\/p>\n\n\n\n

\"image\"<\/a><\/figure>\n\n\n\n

This gives us a way of using the D4M Templates to enable a good initial layer of Reporting for our Marketing Team.<\/p>\n\n\n\n

What next?<\/h3>\n\n\n\n

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

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<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"

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.<\/p>\n","protected":false},"author":43,"featured_media":29,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_price":"","_stock":"","_tribe_ticket_header":"","_tribe_default_ticket_provider":"","_tribe_ticket_capacity":"0","_ticket_start_date":"","_ticket_end_date":"","_tribe_ticket_show_description":"","_tribe_ticket_show_not_going":false,"_tribe_ticket_use_global_stock":"","_tribe_ticket_global_stock_level":"","_global_stock_mode":"","_global_stock_cap":"","_tribe_rsvp_for_event":"","_tribe_ticket_going_count":"","_tribe_ticket_not_going_count":"","_tribe_tickets_list":"[]","_tribe_ticket_has_attendee_info_fields":false,"footnotes":""},"categories":[3,5],"tags":[],"class_list":{"0":"post-175","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-crm","8":"category-marketing"},"featured_image_src":"https:\/\/blog.citrus-lime.com\/crmc\/wp-content\/uploads\/sites\/30\/2021\/11\/Dynamics-365-Marketing-scaled.jpg","author_info":{"display_name":"jadesmith","author_link":"https:\/\/blog.citrus-lime.com\/crmc\/author\/jadesmith\/"},"_links":{"self":[{"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/posts\/175","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/users\/43"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/comments?post=175"}],"version-history":[{"count":1,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/posts\/175\/revisions"}],"predecessor-version":[{"id":176,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/posts\/175\/revisions\/176"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/media\/29"}],"wp:attachment":[{"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/media?parent=175"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/categories?post=175"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/tags?post=175"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}