Microsoft offers Microsoft Dynamics Marketing (MDM) Content Pack for Power BI. It allows you to easily access and analyze your data from Dynamics Marketing. The content pack uses a descriptive model on top of the OData feed, with all the entities and measures needed such as Programs, Campaigns, Marketing Contacts and Companies, Leads, Lead Interactions and Lead Scoring, Email Marketing Messages and Web Sites, behavioral observations, budgets, financial transactions, performance KPIs, and many more.
However, if you have created custom Contact fields in MDM, they are not included in the dataset. In this article, I will walk you through the steps to create a simple report that includes custom Contact fields in MDM.
For the purpose of this article, I created the following custom Contact fields in MDM. These fields will be initially populated in Dynamics CRM and synchronized to MDM via standard integration. Once populated, Marketing department can insert these fields in the email messages and templates. They can also use these fields to create marketing lists in MDM.
Name
|
Title
|
Data Type
|
MembershipNumber
|
Membership Number
|
Text
|
EnrollmentDate
|
Enrollment Date
|
Date/Time
|
LastTransactionDate
|
Last Transaction Date
|
Date/Time
|
ContactType
|
Contact Type
|
Category
Values: Member, Marketing Contact
|
MembershipLevel
|
Membership Level
|
Category
Values: Platinum, Gold, Silver
|
PointsBalance
|
Points Balance
|
Integer
|
In order to create a Power BI report that includes the above fields, you will need to use Power BI Desktop which is available as a free download.
Once you have connected to the OData feed from MDM, you will be asked to select entities (tables) you want to include in a query as shown in the following screenshot. Note: The steps for connecting to OData feed are the same as for connecting to Content Pack (see the link above).
Select the following 5 tables.
Contacts
ContactCustomFieldsText
ContactCustomFieldsInteger
ContactCustomFieldsCategory
ContactCutomFieldsDateTime
Then click “Edit” button.
MDM stores custom field values for each contact in a separate table for each data type as name/value pair. It stores each value in a separate record. For example, if the Contact has Contact Type of Member and Membership Level of Platinum, these values are stored in two separate records as illustrated in the following screenshot. One record has ContactId, Name as "ContactType" and Value as "Member". Another record has the same ContactId, Name as "MembershipLevel" and Value as "Platinum". Our goal is to retrieve these values, create separate columns (instead of rows) for each field and merge them with the Contact query.
Next, remove the ID column (first column above which is simply a unique identifier for the each row in the table) so that we can pivot the records on the Name column (see next step). We are then left with ContactId, Name and Value columns.
After removing the ID column, select the Name column (by clicking on the heading).
Click Transform > Pivot Column.
Select “Value” for Values column.
Expand Advanced Options and select “Don’t Aggregate”.
Click OK.
You should now see two columns created for each Contact for each of the Category fields as shown in the following screenshot.
Next, we will merge the above query with Contacts query.
Select Contacts query in the left hand navigation.
Click Home tab and Merge Queries in the Combine group.
Select the Id column for Contacts by clicking the heading.
Select ContactCustomFieldsCategory query from the dropdown.
Select ContactId column for the second query by clicking on the heading.
Join Kind defaults to “Outer Join” which is what we want because not all Contacts may have the matching records in the second table.
Click OK.
This adds the “NewColumn” to the Contacts table as shown below.
Next, expand the column by clicking on the button with two arrows. Select the fields you want to include and click OK.
Next, rename the columns by eliminating the default prefix.
As you can see, Power BI Query Editor records the steps you performed in the “Applied Steps”. You can delete the steps to revert to the previous state if necessary (for example, if you make a mistake).
You can use the same steps to merge the other fields (text, integer, date/time) into Contacts query.
Once you are satisfied with the query, click “Close & Apply” to apply the steps and close Query Editor.
This will take you to the Report canvas. As you can see in the following screenshot, custom fields are now available for reporting.
I also created two new columns to display date fields in m/d/yyyy format using the following DAX formula.
The following screenshot shows the report that includes some of the custom fields for each Contact. Of course, you can create charts and more complex reports including DAX calculations based on your custom data. For example, you may want to create a bar chart showing email message clicks by membership level.
Once the report has been created, you can publish it to Power BI (website) by clicking on the “Publish” button and following the prompts. The following screenshot shows the report in Power BI.
In this article, we saw how to create a Power BI report to include custom Contact fields created in MDM.
