Reporting on Custom Contact Fields in MDM using Power BI

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.

Measuring Customer Loyalty using Dynamics CRM

I recently received an invitation from my mobile phone company to participate in a survey by responding to five simple questions by text message.

The first question:

How likely are you to recommend Company X to your friends and colleagues? Reply with a number between 1 and 10 where 10 is ‘Extremely Likely’

This question is used as part of the tool called Net Promoter Score which is a customer loyalty metric developed by Bain Company consultant Fred Reichheld. Customers who choose a score of 9 or 10 are labeled promoters. Those who choose a score of 1 to 6 are categorized detractors, while those who select 7 or 8 are deemed passively satisfied. The net promoter score (NPS) measures the difference between the percentages of customers who are promoters and detractors. For example, if 60% of your customers are promoters and 20% are detractors, your net promoter score is 40. An NPS that is positive (i.e., higher than zero) is considered to be good, and an NPS of +50 is considered excellent. Reichheld’s research suggests that changes in a company’s net promoter score correlate with changes in its revenues i.e. increasing NPS is a leading indicator of positive revenue growth. In other words, it can be used to predict positive revenue growth.

There are several survey tools that work with Dynamics CRM including Mojo Surveys and ClickDimensions. Once you have your survey responses collected, you can present this information in a graphical format for easy access by your sales, marketing and service teams.

I used the following steps to accomplish this.

1. Create two custom entities
1. Customer Loyalty Survey: This entity contains the following fields.
1. Name
2. Survey Date
3. Total Responses (integer)
4. Total Promoters (integer)
5. Total Detractors (integer)
6. Net Promoter Score (decimal, may be positive or negative, between -100 and 100)
2. Customer Loyalty Survey Response: This entity contains the following fields.
1. Name
2. Customer Loyalty Survey (lookup to the above entity)
3. Response (integer between 1 and 10)
4. Comments (if any)

2. Create a Workflow to summarize survey results
1. Trigger: When Customer Loyalty Survey Response is created
2. Step: Calculate and update Total Responses, Total Promoters, Total Detractors and Net Promoter Score
   Note: Net Promoter Score = (Total Promoters – Total Detractors) / Total Responses

3. Create a chart to display Net Promoter Score by Survey

After you import survey responses into Customer Loyalty Survey Response entity, the results including NPS are automatically calculated by the workflow created above. The results are then available for viewing as view and chart similar to a screenshot below.

NPS can be incorporated as a Key Performance Indicator (KPI) in a dashboard or a balanced scorecard for your sales, marketing and service teams.

Leverage “Quick Find” for “Quick Data Entry”

As the name of the feature suggests, users of Dynamics CRM can use “Quick Find” to quickly and easily search for the records in the entity. You can configure the Quick Find view for a specific entity by defining “view” columns and “find” columns. CRM searches for the text you enter in the search box (including any wild cards) in the “find” columns defined for the Quick Find view.

In addition to this functionality, you can also use this feature for quick data entry. The following example illustrates this.

In this example, the company uses CRM to provide services to publicly traded companies. Companies are stored in the Account entity. Each company has been populated with its unique Ticker Symbol. Account Quick Find view has been configured with Ticker Symbol as one of the “Find” columns. The following screenshot shows the sample company data.

With the Ticker Symbol defined as one of the “Find” columns for the Quick Find view, you can now enter the unique Ticker Symbol anywhere in CRM where you need to enter the Company name. This includes Advanced Find and forms where the Account (lookup) needs to be entered.

For example, when you enter “MSFT” in the Account (lookup) field in the Opportunity form and tab out, CRM populates “Microsoft Corporation” in the field.

 

Because Ticker Symbols are unique across the companies, CRM finds exactly one record based on the Ticker Symbol you enter. This technique can also be used for the other unique fields such as Account Number, Short Name or Alias.

Sending Direct Email and Automated Email to records in a custom entity in Dynamics CRM

Prior to Dynamics CRM 2013, you could not send direct email to records in a custom entity as you could for the standard entities such as Account, Contact, Lead and User.

With the introduction of the following option on the entity configuration when you create a custom entity, you can now enable the entity for sending Direct Email.

Selecting this option (as in the above screenshot), adds the Send Direct Email button to the main ribbon tab for the entity.

For example, I have a custom entity named Workorder with the “Sending email” option checked. When I select the records in the Workorders view, I see the “SEND DIRECT EMAIL” button on the ribbon which enables me to send direct email for the selected records. Using this feature, a user can send direct email to the email address on the selected records.

If you do not have an existing email address field in the entity, CRM creates one as seen in the screenshot below.

Note that after you enable this option, it cannot be disabled.

Another not so obvious and undocumented feature that becomes available when you select this option is an ability to send email to the email address on the record using a Workflow.

Prior to CRM 2013, in order to send email to the Email address on the custom entity records, you had to select the “Allow messages with unresolved email recipients to be sent” option in System Settings (shown below). You could then populate the “To” field in the email with the Email address on the custom entity record in a custom workflow activity which requires custom development.

With the “Sending email” option, you can do this without enabling the above system setting. This can now be accomplished using a standard Workflow as illustrated in the screenshot below.

Upgrade PowerPivot Data Model to SSAS Tabular

In the previous articles, I discussed the steps for building PowerPivot data models for Dynamics GP Sales and Financial (GL) data using PowerPivot for Excel. This is a great way for power users to build powerful business intelligence solutions without expensive hardware and difficult to use development tools. It is also a great way for professional BI developers to prototype a complete solution quickly.

However, there will be situations where PowerPivot based solutions may not meet your business needs. In this case, there are two upgrade paths available to migrate your PowerPivot data model. These are PowerPivot for SharePoint and SSAS Tabular. In this article, I will discuss SSAS Tabular.

Here are some of the reasons why you may want to upgrade from PowerPivot to SSAS Tabular.

Security

Tabular model databases can use row-level security, using role-based permissions in Analysis Services. For example, you can configure security to enable Sales Manager to view all sales data while Salesperson to view only his or her sales data.

Power Pivot workbooks are secured at the file level. A user who has access to the file can view all the information contained within the workbook.

Scalability

PowerPivot for Excel has an artificial file size limit of 2 gigabytes, which is imposed so that workbooks created in Power Pivot for Excel can be uploaded to SharePoint, which sets maximum limits on file upload size. One of the main reasons for migrating a Power Pivot workbook to a tabular solution on a standalone Analysis Services instance is to get around the file size limitation.

Partitioning

Partitions, in tabular models, divide a table into logical partition objects. Each partition can then be processed independent of other partitions. For example, a table may include certain row sets that contain data that rarely changes (historical sales data, for example), but other row sets have data that changes often (current year’s sales data).

Query and Reporting

Excel and PowerView are your only options if you use PowerPivot. With SSAS Tabular, you can use many other reporting tools including SQL Server Reporting Services (SSRS).

You will find detailed comparison of SSAS Tabular and PowerPivot here.

Before you upgrade your PowerPivot data model to SSAS Tabular, you will need the following:

1. SQL Server Analysis Services instance running in Tabular mode (I used SQL Server 2014)
2. SQL Server Data Tools (SSDT) (This is required if you use Visual Studio to import your data model to SSAS Tabular. Please see below for details)

There are two ways you can import your PowerPivot data model to SSAS Tabular.

Option 1:

SQL Server Management Studio option: Import directly to SQL Server using Restore from PowerPivot option as shown in the following screenshot

Option 2:

Visual Studio (SSDT) option: Import using Visual Studio (SSDT) as shown in the following screenshot

Once completed, you will be able to connect to the SSAS Tabular data model on the server and create a report in Excel and other reporting tools such as SSRS.

Here’s the PivotTable created using Excel. You will notice that the presentation of PivotTable fields is slightly different from what you see when you create a PivotTable from PowerPivot data model. For example, KPIs have their own folder. However, the end result is the same.

Because the data is no longer stored within the Workbook but is stored on the server, the file size is very small as shown in the following screenshot.

After you have migrated the PowerPivot data model to SSAS Tabular, you do not need the original Excel workbook any more. You will need to use Visual Studio to make changes to the Tabular data model and implement new features such as security and partitioning.

If you would like to learn more about SSAS Tabular, you will find step by step tutorials here.