Overview: Data Exchange Framework and Sitecore Connect for Microsoft Dynamics 365 for Sales

Cover Image for Overview: Data Exchange Framework and Sitecore Connect for Microsoft Dynamics 365 for Sales

Overview

If you're here, congratulations: you're probably working on an ambitious and exciting project. This post is a high level overview involving the following technologies:

  • Sitecore 9.3
  • Data Exchange Framework 4.0 (docs, downloads)
  • Sitecore Connect for Microsoft Dynamics 365 for Sales 4.0 (docs, downloads)
  • xConnect / xDB

What is DEF?

You use the Data Exchange Framework to model processes that synchronize data between multiple systems.

At the practical level, DEF is comprised of:

  • Core database items
  • Master database items
    • Settings
    • Templates
    • Branch templates
  • DLLs
  • Web.config adjustments regarding <dependentAssembly>
  • Sitecore config files

Pipelines

Pipelines are how data is exchanged between systems. The pipeline batches are what actually run. Each pipeline batch has 1 or more pipelines, and each pipeline has one or more pipeline steps.

DEF Pipelines

Pipeline batches can be triggered in many ways:

  • Manually
  • Programmatically
  • Marketing Automation AKA "Data Exchange Framework Activity for Marketing Automation" (see downloads page)
  • Scheduled tasks
  • Web based push notifications
  • Remote SDK / Tenant Service (docs)

The remote SDK is worth expanding upon here. The Sitecore docs say:

Data Exchange Framework Remote SDK allows pipeline batches, pipelines and other framework components to run outside of the Sitecore server. This can significantly reduce the load on your Sitecore server by moving much I/O and other processing to a separate machine.

When using the remote SDK, Sitecore's role in synchronization processes is limited to configuration. Sitecore items are used to configure framework components, but the framework components themselves run within the application domain that is using the remote SDK.

What is Sitecore Connect for Microsoft Dynamics 365 for Sales?

This connector extends DEF to facilitate synchronization of data between Dynamics and Sitecore. Both Dynamics 365 and on-premise versions of Dynamics are supported.

The Dynamics connector is comprised of:

  • Master database items
    • Settings
    • Templates
    • Branch templates
  • Sitecore config files
  • Web.config adjustments regarding <dependentAssembly>
  • DLLs
  • JSON file (called the "xConnect collection model")
  • Staging database (used for quick and efficient temporary storage during the synchronization process)

For comparison, another similar connector is "Sitecore Connect for Salesforce CRM".

Note: going forward, I will often use the term DEF to refer to both DEF and the Dynamics connector.

What does the Dynamics Connector Do Out of the Box?

Once you install the Dynamics connector and create a new Data Exchange Tenant, you will see the following out-of-the-box pipeline batches:

Dynamics connector out-of-the-box pipeline batches

In short:

  • Pulling Dynamics contacts into Sitecore
  • Pulling Dynamics marketing lists into Sitecore
  • Pulling Dynamics "activities" into Sitecore (more on this in a following section)
  • Pushing xConnect contacts to Dynamics
  • Pushing xConnect email events to Dynamics

When Should I Use DEF / Connectors?

It might be a good idea to consider using DEF when:

  • You want to manage and modify sync operations via items in the Content Editor
  • You want to adjust connections on the fly with minimal or no code changes
  • You have relatively simple architecture, such as XP Single (where CM and CD are the same instance) sitting on a virtual machine
  • You want a lot of functionality in a short amount of time
  • You are okay with using most of the functionality as-is, but doing custom overrides or additions when needed
  • You want inspiration and ideas for your own custom implementation

In practice, you are almost certainly going to need a highly technical individual (developer) to get these running and configured properly. Although DEF is highly robust, it's not quite as simple as a content author installing a package and configuring a few items. It doesn't take much for your use cases to require some level of special configuration or customization.

You probably shouldn't use DEF when:

  • You want full control
  • You need a high level of test coverage
  • You have a simple use case
  • You don't need a comprehensive visual interface for managing your connections / flows

DEF is an impressive feat of engineering, but remember this quote by Tony Hoare:

Inside every large program is a small program struggling to get out.

Taking a fully custom code approach will always be more lightweight. There is also far more documentation, tutorials, and code samples for custom code as compared to content for configuring / customizing DEF. When I first started working with DEF and the Dynamics connector, I was surprised at how often Google searches returned no results. For that reason, my posts should allow me to lay claim to numerous long tail keywords. 😉

Important: Question Your Assumptions

Make sure that your team has an excellent understanding of what data you want to sync between your systems and why. Challenge assumptions about your requirements, all the way to first principles.

For example, while Dynamics is powerful and can be customized to do virtually anything, that doesn't mean that you should. Microsoft and community support may be limited for your use case. Dynamics may be more cumbersome to work with and may cost more for storage and processing as compared to using other more specialized tools.

One real life example is that although Dynamics supports use cases involving "Contacts" or "Customers", Microsoft has specialized offerings which may be more suitable for your requirements. Particularly, they offer a "Customer Insights" or "Customer Data Platform".

Based on how Microsoft describes CDPs in general, it sounds like something worth researching if you haven't already:

A customer data platform (CDP) is marketing technology that pulls together customer data from multiple sources, then shares it with other applications.

Microsoft's CDP offering encompasses:

  • Personalization
  • Developing a wholistic view of customers
  • Unifying fragmented data
  • Deriving actionable insights
  • Measuring customer activity (multi-channel activity from apps, websites, mailing lists, etc.)
  • Ingesting data from multiple sources
  • Contact merging (de-duping)

Important: Understand Your Data Flows

It's crucial that you understand the entire lifecycle of your sync process. It's not enough for a technical analyst on your team to come up with a flow diagram. The flow diagram also needs to be informed by what you actually end up building / configuring in your pipelines.

Questions to ask:

  • What should happen if x step fails?
    • Retry?
    • Send to log?
    • Alert someone?
    • Wait?
    • Fire event?
    • Fail and exit?
  • What should happen if x step times out or takes longer than expected to process?
  • What should happen if Dynamics goes down?
  • What should happen if xConnect goes down?
  • What should happen if CM goes down?
  • What should happen when a data entity does not pass a data validation as it's being processed?

DEF provides many avenues by which you can:

  • Send information to logs
  • Retry failed steps
  • Override default functionality
  • Detect failed validation conditions

For example, certain types of pipeline steps contain configurations such as:

Object Resolution Configuration

PII Considerations

If PII is a concern, make sure you understand Sitecore's default settings when it comes to storing / displaying / searching for contact information:

By default, it is not possible to search for personally identifiable information (PII) such as contact name. However, an administrator can enable indexing of PII sensitive data. By default, search is also disabled for anonymous contacts, because this operation consumes a lot of processing power due to the amount of data. However, it is possible for an administrator to enable indexing of anonymous data. You can only search for an exact name or email address. You cannot search for part of the name or email address.

Questions to ask:

  • Should contact info be hidden in the Experience Profile UI?
  • Should PII be stored in my index?
  • Should Contacts be searchable?

What is xConnect?

From the docs:

xConnect is the service layer that sits in between the xDB Collection database and xDB index, and any trusted client or device that wants to read, write, or search contact and interaction data.

In other words, xConnect/xDB is what collects / manages / stores contact analytics information. I often refer to xConnect/xDB simply as xConnect or XC. As long as you've installed Sitecore properly and you're not having any certificate issues, with only a bit of setup, xConnect generally works beautifully.

When syncing XC with Dynamics, it's important to understand the "core collection model" that ships with XC:

xConnect Core Collection Model

Put into words, what we care about are these things:

  • We have many Contacts
  • Contacts can have many Identifiers (username, Dynamics ID, etc.)
  • Each Contact can have many Interactions (in the context of this post, Interactions correlate to web sessions)
  • Each Interaction has one or more Events
  • There are various built-in Event types

All of this data is going to be stored and split within your xDB Collection database shards.

Installation

First make sure xConnect is working with no major errors in the logs and ensure that your xConnect certificate setup isn't going to cause problems down the road. See this post for more information.

Download the installation guides for DEF and the D365 connector (links are in the #Overview section). The installation steps are relatively straightforward.

Check out my Stack Exchange question about issues connecting to Dynamics. It contains comprehensive information about how to get connected successfully.

Some of the common connection errors are:


_7
[ERROR] Metadata contains a reference that cannot be resolved
_7
_7
[ERROR] An unsecured or incorrectly secured fault was received from the other party. See the inner FaultException for the fault code and detail
_7
_7
[ERROR] The authentication endpoint Kerberos was not found on the configured Secure Token Service
_7
_7
[ERROR] CRM ConnectionString cannot be null or empty

DEF Item Serialization

I found item serialization to be helpful while working with DEF in order to keep track of the changes I was making. I recommend it while you're still getting familiar with it.

DEF items have a verbose naming scheme, and serialized item paths will hit the path length limit in Windows. If you're using TDS, see this post about how to use aliases to shorten the paths.

Otherwise, be mindful of the types of items that should be serialized and updated and which ones shouldn't. For example, pipeline batch items should be deployed once and not automatically synced afterwards because the items contain information / references to the last pipeline run (timestamps, logs, etc).

Pipeline Logging

Pipeline batches have options for the level of logging verbosity, as well as an option to include exception trace and telemetry information.

Pipeline batch log levels

By default, DEF will log pipeline results to individual files in /App_Data/Logs/DataExchange/YOUR_DEF_TENANT_NAME/.

These log files can be large, and over time you could run into disk space issues, so consider setting up a cleanup task.

If your upstream environment is in Azure, consider integrating the DEF logs with Application Insights. Sitecore has a resource for this called AppInsights logger for Tenant Service.

DEF has a Write to Log Pipeline Step which is useful for quick debugging. Like most things in DEF, you can easily customize the code that powers this pipeline step. One reason you may want to do this is to prepend / append a string to your outputs to make them easier to spot in a large log file.

Write to Log Pipeline Step Override

Filters & Filter Expressions

There are numerous built-in filters for Contacts, Marketing Lists, Activities, Events, and more. The built-in options depend on which Provider we're configuring for (Dynamics, xConnect, etc). Understanding how filters work and how to set up custom filters is crucial. Be sure to read through the docs.

Dynamics Contact Filters

Dynamics Filters for Contacts

xConnect Contact Filters

xConnect Filters for Contacts

Filter Expressions

You can use Filter Expressions to create filters with logic that's as complex as you need it to be using AND/OR logical operators. You can even nest them if you need to.

Filter Expressions

Filtering Your Test Contacts

Below is a filter expression called Contact is My Test User. This uses AND logic to check the incoming Contact from Dynamics to ensure that it matches a specific Dynamics Contact ID and Dynamics Contact type. These kinds of filters will be crucial for you during development so you can run pipeline batches rapidly without syncing all your data, which could be millions of entities.

Filtering your test contact

xConnect Event Type Filters

Here's how you would filter for the out of the box PageViewEvents. All three filters shown use the default Event Type Value Expression template {0095E139-E4CB-4F18-A07C-AE01C2062996}. xConnect Event Type Filters

Helpful SQL Queries

Modify the queries below as needed. Remember to run queries on all your shards.

Interactions


_10
SELECT *
_10
FROM [Site930_Xdb.Collection.Shard0].[xdb_collection].[Interactions]
_10
--WHERE ContactId = 'xxxxxxxx'
_10
UNION
_10
_10
SELECT *
_10
FROM [Site930_Xdb.Collection.Shard1].[xdb_collection].[Interactions]
_10
--WHERE ContactId = 'xxxxxxxx'
_10
_10
ORDER BY Created DESC

Contacts


_9
SELECT *
_9
FROM [Site930_Xdb.Collection.Shard0].[xdb_collection].[Contacts]
_9
_9
UNION
_9
_9
SELECT *
_9
FROM [Site930_Xdb.Collection.Shard1].[xdb_collection].[Contacts]
_9
_9
ORDER BY Created DESC

Contact Identifiers

Identifiers are stored as VARBINARY(700). The query below will convert the value to plain text for easier querying.


_17
SELECT *
_17
FROM (
_17
SELECT ContactId, Source, Identifier, CONVERT(VARCHAR(400), Identifier) AS IdentifierPlainText
_17
FROM [Site930_Xdb.Collection.Shard0].[xdb_collection].[ContactIdentifiers]
_17
) AS x
_17
--WHERE ContactId like UPPER('XXXXX')
_17
--WHERE UPPER(x.IdentifierPlainText) = UPPER('XXXXX')
_17
_17
UNION
_17
_17
SELECT *
_17
FROM (
_17
SELECT ContactId, Source, Identifier, CONVERT(VARCHAR(400), Identifier) AS IdentifierPlainText
_17
FROM [Site930_Xdb.Collection.Shard1].[xdb_collection].[ContactIdentifiers]
_17
) AS x
_17
--WHERE ContactId like UPPER('XXXXX')
_17
--WHERE UPPER(x.IdentifierPlainText) = UPPER('XXXXX')

You can also view the values in plain text by copying the cell value, removing the 0x portion, and pasting into a hex-to-text decoder.

Contact Data Deletion

Once a contact has been loaded into Sitecore, the only way to remove them is programmatically or via SQL (not recommended except in local environments).

This Sitecore-endorsed repo has an example of how to implement programmatic deletion of contacts.

To remove all contacts locally run the following SQL commands (not recommended if you are storing XC contact IDs in Dynamics as the relationships will be broken):


_35
DELETE
_35
FROM [Site930_Xdb.Collection.Shard0].[xdb_collection].[ContactIdentifiers]
_35
_35
DELETE
_35
FROM [Site930_Xdb.Collection.Shard0].[xdb_collection].[ContactIdentifiersIndex]
_35
_35
DELETE
_35
FROM [Site930_Xdb.Collection.Shard0].[xdb_collection].[ContactFacets]
_35
_35
DELETE
_35
FROM [Site930_Xdb.Collection.Shard0].[xdb_collection].[InteractionFacets]
_35
_35
DELETE
_35
FROM [Site930_Xdb.Collection.Shard0].[xdb_collection].[Interactions]
_35
_35
DELETE
_35
FROM [Site930_Xdb.Collection.Shard0].[xdb_collection].[Contacts]
_35
_35
DELETE
_35
FROM [Site930_Xdb.Collection.Shard1].[xdb_collection].[ContactIdentifiers]
_35
_35
DELETE
_35
FROM [Site930_Xdb.Collection.Shard1].[xdb_collection].[ContactIdentifiersIndex]
_35
_35
DELETE
_35
FROM [Site930_Xdb.Collection.Shard1].[xdb_collection].[ContactFacets]
_35
_35
DELETE
_35
FROM [Site930_Xdb.Collection.Shard1].[xdb_collection].[InteractionFacets]
_35
_35
DELETE
_35
FROM [Site930_Xdb.Collection.Shard1].[xdb_collection].[Interactions]
_35
_35
DELETE
_35
FROM [Site930_Xdb.Collection.Shard1].[xdb_collection].[Contacts]

How Interactions & Events are Stored in xDB

Below is a typical example of what you'll see in an Interaction record's Events column.

This Interaction was generated by visiting a single page in a private browsing tab and then ending the session immediately after.

Note how the top PageViewEvent is the parent of the second custom event and also the parent of the last event. Expect to see many events containing Request took xxx ms to complete while developing. Sitecore automatically fires these.


_40
[
_40
{
_40
"@odata.type": "#Sitecore.XConnect.Collection.Model.PageViewEvent",
_40
"CustomValues": [],
_40
"DefinitionId": "9326cb1e-cec8-48f2-9a3e-91c7dbb2166c",
_40
"ItemId": "197dcf8c-5191-4a77-90a1-babf352e7b2f",
_40
"Id": "3a2b4f6c-afc4-410b-9a6f-076636633d4f",
_40
"Timestamp": "2022-08-04T16:12:56.7227459Z",
_40
"Duration": "PT9.969S",
_40
"ItemLanguage": "en-US",
_40
"ItemVersion": 1,
_40
"Url": "/",
_40
"SitecoreRenderingDevice": {
_40
"Id": "fe5d7fdf-89c0-4d99-9aa3-b5fbd009c9f3",
_40
"Name": "Default"
_40
}
_40
},
_40
{
_40
"CustomValues": [],
_40
"Data": "https://site930.sc/",
_40
"DataKey": "Url",
_40
"DefinitionId": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx",
_40
"ItemId": "197dcf8c-5191-4a77-90a1-babf352e7b2f",
_40
"Id": "1db93df8-7794-45ca-babd-f2cbb2ac3c05",
_40
"ParentEventId": "3a2b4f6c-afc4-410b-9a6f-076636633d4f",
_40
"Text": "Custom Event",
_40
"Timestamp": "2022-08-04T16:12:56.8044802Z"
_40
},
_40
{
_40
"CustomValues": [],
_40
"Data": "5770",
_40
"DataKey": "5,771",
_40
"DefinitionId": "dc6f6aff-6aa9-423f-a824-49f9ee741aa9",
_40
"ItemId": "197dcf8c-5191-4a77-90a1-babf352e7b2f",
_40
"Id": "93dd6880-39fb-4d12-844d-38d1242ad439",
_40
"ParentEventId": "3a2b4f6c-afc4-410b-9a6f-076636633d4f",
_40
"Text": "Request took 5,771ms to complete",
_40
"Timestamp": "2022-08-04T16:12:59.7504543Z"
_40
}
_40
]

Conclusion

There's still much to demonstrate. More posts on DEF / Dynamics / xConnect will follow. Hope this one helped you.

LAHWF,

Marcel


More Stories

Cover Image for NextJS: Access has been blocked by CORS policy

NextJS: Access has been blocked by CORS policy

> CORS is almost as much of a nuisance as GDPR popups

Cover Image for NextJS: Short URL for Viewing Layout Service Response

NextJS: Short URL for Viewing Layout Service Response

> Because the default URL is 2long4me

Cover Image for Super Fast Project Builds with Visual Studio Publish

Super Fast Project Builds with Visual Studio Publish

> For when solution builds take too long

Cover Image for How to Run Old Versions of Solr in a Docker Container

How to Run Old Versions of Solr in a Docker Container

> Please don't make me install another version of Solr on my local...

Cover Image for Script: Boost SIF Certificate Expiry Days

Script: Boost SIF Certificate Expiry Days

> One simple script that definitely won't delete your system32 folder

Cover Image for On Mentorship and Community Contributions

On Mentorship and Community Contributions

> Reflections and what I learned as an MVP mentor

Cover Image for Azure PaaS Cache Optimization

Azure PaaS Cache Optimization

> App Services benefit greatly from proper configuration

Cover Image for Content Editor Search Bar Not Working

Content Editor Search Bar Not Working

> Sometimes it works, sometimes not

Cover Image for JSS: Reducing Bloat in Multilist Field Serialization

JSS: Reducing Bloat in Multilist Field Serialization

> Because: performance, security, and error-avoidance

Cover Image for NextJS: Unable to Verify the First Certificate

NextJS: Unable to Verify the First Certificate

> UNABLE_TO_VERIFY_LEAF_SIGNATURE

Cover Image for Sitecore Symposium 2022

Sitecore Symposium 2022

> What I'm Watching 👀

Cover Image for Don't Ignore the HttpRequestValidationException

Don't Ignore the HttpRequestValidationException

> Doing so could be... potentially dangerous

Cover Image for JSS + TypeScript Sitecore Project Tips

JSS + TypeScript Sitecore Project Tips

> New tech, new challenges

Cover Image for On Sitecore Stack Exchange (SSE)

On Sitecore Stack Exchange (SSE)

> What I've learned, what I see, what I want to see

Cover Image for Ideas For Docker up.ps1 Scripts

Ideas For Docker up.ps1 Scripts

> Because Docker can be brittle

Cover Image for Add TypeScript Type Checks to RouteData fields

Add TypeScript Type Checks to RouteData fields

> Inspired by error: Conversion of type may be a mistake because neither type sufficiently overlaps with the other.

Cover Image for NextJS/JSS Edit Frames Before JSS v21.1.0

NextJS/JSS Edit Frames Before JSS v21.1.0

> It is possible. We have the technology.

Cover Image for Tips for Applying Cumulative Sitecore XM/XP Patches and Hotfixes

Tips for Applying Cumulative Sitecore XM/XP Patches and Hotfixes

> It's probably time to overhaul your processes

Cover Image for SPE Script Performance & Troubleshooting

SPE Script Performance & Troubleshooting

> Script never ends or runs too slow? Get in here.

Cover Image for Year in Review: 2022

Year in Review: 2022

> Full steam ahead

Cover Image for Tips for Forms Implementations

Tips for Forms Implementations

> And other pro tips

Cover Image for Tips for New Sitecore Developers

Tips for New Sitecore Developers

> Because learning Sitecore can be hard

Cover Image for Symposium 2022 Reflections

Symposium 2022 Reflections

> Sitecore is making big changes

Cover Image for Hello World

Hello World

> Welcome to the show

Cover Image for Early Returns in React Components

Early Returns in React Components

> When and how should you return early in a React component?