Best Practice, CRM, Data integration, Dynamics 365, KingswaySoft, Scribe

Part 6: Data Integration – Return of the Best Practices

Recap Best Practices: Part 2

Welcome back!

My previous blog discussed the use of Staging Databases as part of integration design and some tips and pointers whilst building a staging database (if you decide to use one that is).

My background with data integration is using either out of the box/free tools or 3rd party data integration software (like Scribe or KingswaySoft) and the methods for designing and building data integration have evolved over time. This blog will detail the best practices when dealing with performance issues that I have encountered over the years.

You can apply some (if not all) of these principles for bespoke data integration tools, such as:

  • Web Services
  • Console Applications
  • Utilising a Service Bus (like Azure for integration messaging)

This will be my final* post when discussing best practices with data integration, most of which can be applied when using any tool to complete the integration.

*Star Wars did not end with a trilogy.

Part 3 : Performance Issues and Improving Performance

Performance Issues

Integrating with Dynamics 365 can be a painful learning curve, some of the issues (that I would see as performance related) I have encountered are:

  • Understand of Entity Relationships (I mean who loves integrating with the Activity and Pointer Entities??) which have lead to poor profile design operations being completed by the integration.
  • Data import speed (e.g. records per second or records/second) slowing over time.
  • Network Latency/Connection errors which can arise at any time during the integration.
  • SQL Timeouts due to high load/queries on the D365 database
  • Error exceptions such plugins/workflow failing due to lack of resource or Dynamics 365 providing us with as little detail as possible
  • Server Resources (source related)
  • Database Log File growth (staging database – source)
  • SQL Locks (source)
  • And the list goes on

Additionally, big factor to consider with data integrations and loading data directly into Dynamics 365 is that this can only be achieved via two supported methods:

  • Directly through the Dynamics 365 interface, manually entering though forms, bulk editing or uploading a file using the out of the box import tool.
  • Via the API using one of the many free, 3rd party or custom tools available which connect directly to one of the Dynamics 365 endpoints.

There is no supported method for loading data directly into the Dynamics 365 database, and if anyone asks you to do this, just say NO. So even if you are using a Staging DB no SQL – SQL transfers are NOT allowed.


With Dynamics 365 Online, Microsoft have protected customers by this method of tomfoolery by restricting access (or even visibility) to the Dynamics 365 Database!


All Dynamics 365 data integrations must be made through the API, which means they:

  • Adhere to the Security Model of the User connecting to Dynamics and performing data load operations via their Security Roles.
  • Trigger any system events or custom automation (bespoke) when record operations occur (e.g. create, update, delete etc.), such as:
    • createdon is populated
  • Back-end relationships are created in the database with other records in the system
    • When records are created, CRM populates the createdby, owner and so on.
  • Caching of data/sharing of records stored to the PoA table.
  • And all additional other magical things system operations Dynamics 365 completes (using the Force!) (such as creating an Audit logs trail etc.).

Performance Implications

Considering the above items, we can see:

  • Firstly why loading data has to be (and can only be) loaded through the API.
  • Why performance maybe slowed by the Dynamics 365 and flowing through the application Layer to the database layer.

Whilst investigating why performance is being impacted, it also makes sense that we investigate ways of improving the performance of data integrations with the aim of improving:

  • Data load times and record/second.
  • The quality and reliability of you integrations

I will walk though a common data load scenario which will highlight a few of the performance issues are listed above and are usually encountered when completing a data migration to Dynamics 365.

I will then review the migration and make suggestions/ list my best practices on how to overcome or help mitigate these issues.

Sales Load Example

For this example, I will walk through a very basic scenario of importing data without using any special tips or best practices; this also assumes that the the data integration tool is importing 1 record at a time (discussed later on). This is a direct source to target data import and all values are made up for the purposes of this example.

If you have the requirement to load a medium size data set (e.g. the last 5 years worth of data) for a customer, and say:

  • 15,000 Accounts
  • 100,000 Contacts
  • 6000 Opportunities
  • 12,500 Opportunity Products

Dynamics 365 Online data migration from a data source.

We will use the example Sales load highlighted in my first blog, the load order would be as follows:

  1. Accounts
  2. Contacts
  3. Opportunity with grouped Opportunity Products


Account Load

The loading of the Account data into Dynamics 365 will be relatively painless; it is a simple profile to just perform a Create operation into the CRM database. The Account data set is relatively small and this will probably complete within about 20-25 mins.

When measuring import speed, you will probably hear people talking about records/second (which a 3rd party tool will calculate for you). The average Account import speed here is about 10-15 records/second.

Contact Load

The next step is to load the Contact data into Dynamics 365, this profile is slightly more complicated in the fact that we have to also search for an existing Account to firstly set as the Parent Account ID and also update it with the Primary Contact value.

So instead of the one load operation to Dynamics 365, we have 3 operations with each operation requiring round trips to and from  Dynamics 365 to the data integration tool:

  • Create (Contact)
  • Search and Update (Account and Contact)
  • Update (Account)

Because of the additional work the profile has to do, the import speed may go down to an average of 3-4 records/second as the profile is having to do more work (roughly 3 times). This will take approximately 7 hours to import 100,000 Contact records.

Opportunity Load

This process is actually grouping and loading Opportunities and child Opportunity Product records together and loading these in the same data profile and contains 4 primary operations:

  • Search (Account)
  • Create (Opportunity)
  • Search (Product)
  • Create (Opportunity Product)

With the added complexity of grouping related data together (Opportunity and Opportunity Products) at the source, the profile may only achieve an average maximum of 1-2 records/second; with a maximum of 6000 rows the load time will be around 50 minutes.


Example Review

Reviewing the Sale Process load example it can seen that increasing the complexity of a data profile can reduce the performance of the import speed.

For low-volume data loads, the time differences can be in minutes or hours which do not impact project timelines too much.

But, consider the impact to projects where the data sets contain millions of rows (i.e. high-volume); the potential increase in project timelines can be calculated in either days or weeks which your customers will not be happy with.

Identifying performance gains will be critical in these scenarios to reduce the time it takes to integrate the data.

Improving Performance

So, how can we go about ways of improving data integration performance?

  1. Environment Optimisation – manipulating the environments around the data such as:
    • Storage location
    • Server Specs (On Premise only)
    • Network Performance
  2. Data Profile Optimisation – breaking down the steps with your data profiles.
  3. Matching Optimisation – increasing the efficiency when matching to records in Dynamics 365.
  4. Record Ownership – assigning record owners in Dynamics 365.
  5. Record Status – Saving your record statuses until last.

Environment Optimisation

There will be different environments for every single data integration, and there will be different things you can try to help improve performance without touching the data integration itself. The changes could be either hardware (e.g. increase Server Memory) or software configuration (e.g. creating a database index) optimisations.

So lets look at the environmental changes we can complete to increase performance when integrating data with Dynamics 365 – first we can split out the environments as follows:

  • Source – You may have one or more source systems if merging from multiple systems; if you have a staging database this will be your source system for your data profiles when loading data into Dynamics 365.
  • Integration Tool Resource – this maybe under your control (i.e. On-premise server)
  • Transit – Network connection between Source, Target and the Integration Tool
  • Target – Dynamics 365 (Online or On Premise).



I will use the Staging Database approach with data integrations to provide the context of the environment, if using a file then there is not too much you can do except clear down erroneous rows or the 63000 extra blank rows that Excel sometimes like to add.

So with Staging Databases, I will make the assumption that this is a SQL database (but same tips will apply to additional database types like MySQL.

Firstly I recommend you look standard best practices for optimising a database; I will list out a few of the key ones that you can actually perform yourself.

  • Create Useful Indexes.
  • Shrinking the database – often (if using Full Recovery mode).
  • Keep on top of Database maintenance plans.
  • Make sure the following routines do not interrupt/occur when your data integration is under heavy load as this will negatively impact the read speed and queries being executed against tables with the potential of generating SQL Timeouts:
    • Backup
    • Shrink
    • Index rebuild

The following tasks may require an Customer System Administrator to complete:

  • Limit your SQL Instance Memory to 1/2 or 3/4 of the SQL Server the instance is located on, leaving the rest available for the Operating System to use.
  • Log Files on separate hard drives
  • Increase the SQL Server allocated memory (to enable the above!
Integration Tool Resource

Some integration tools (like Scribe Insight) require their own Server with its own set of resources (and SQL database); I recommend using the following as a guide when choosing a location for your data integration tool:

  • Take the recommended System Requirements, and then double them.

For bespoke solutions e.g. console apps or web services, make sure they have:

  • Decent connection to Dynamics 365.
  • Adequate memory and processing power to function under extremely high load.
  • A Service Account with appropriate security privileges and access to Dynamics 365.

If these are hosted in Azure and connecting to Dynamics 365 Online, how about moving to the same data center as where your Dynamics 365 instance is located? This will reduce network latency by reducing the number of connections it needs to make to Dynamics 365 via other servers.


For all tools and bespoke solutions console apps or web services – make sure they have a decent connection to Dynamics 365 (as stated above).

  • Ensure that you are using a secure and encrypted HTTPS connection (applies to On Premise only).
  • Make sure all IP ranges have been authorised through your Firewall for Dynamics 365

Loading data into Dynamics 365 can be affected by any number of items once the connection to D365 has been established. There are several tips I recommend completing whilst loading data into Dynamics 365:

  • Disable any automation such as Workflows and Plugin or SLA which maybe triggered on import create/update.
  • Turn off Email Synchronisation (just in case any get created and sent out).
  • Postpone some of the out of the box internal maintenance schedules in Dynamics 365 (e.g. bulk deletion tasks).
  • For On-Premise deployments, standard Database practices should be completed regularly.
  • Add more resources to the CRM and SQL servers, and split out the Front and Back End server roles onto different Servers with load balancing.

Data Profile Optimisation

We have looked at my recommended best practices from an environmental point of view, next we can look at tweaking the way your data profiles operate and suggesting improvements in logic.

Profile Load Order

The first item to think about is your profile load order, each data integration will require a profile order where the data should be ordered based on the required data to create that record type. The following diagram shows an example scenario for a typical data migration.

Example profile order for importing data into Dynamics 365

The decision could be made to either start with Leads or start with the Product Catalog (Products and Price Lists).

My best practices when planning a data integration and the profiles ordering logic are:

  • Understanding the essential “must-have” data for production go-live and build a import plan accordingly.
  • Identifying independent data sets and/or configuration data and focusing on these areas first unless it is not required for go -live or flagged as  “nice to have”.
  • Determining your key data sets which are prerequisites for other data sets (e.g. Accounts are required before you can import Opportunities or Cases).
  • Import smaller data-sets first for quick wins; if you encounter issues with a larger load and are required to be restarted/fixed then at least you have got some data in and you can concentrate of fixing the larger data-set.
  • Finish with Activities/Notes and Attachments; these are often the most complicated and difficult tasks to complete; Activities can also be related to any of the proceeding data (e.g. via the Regarding field) and so I will tackle the hardest items until last. Also – Historic Activities are most likely never going to change; these could also be imported after (not part of any critical path) production go-live.
Select Columns

When querying source data, only select the exact columns you need to process as part of your data integration. Any additional Columns queried, but are not used become a waste of resource and can impact the performance of data profiles.

So in an example source Contact SQL Query:

SELECT [FirstName], [SurName], [Email], [PostCode], [Status]



WHERE [STATUS] = ‘Active’

Instead of the below query which returns all Columns.


WHERE [STATUS] = ‘Active’

Pre-populate lookups

Populating Lookup fields often requires some element of validation to ensure that the record GUID exists; e.g:

  • Profile will first check to see if the record exists
  • Retrieves the record GUID
  • Sets the GUID in the create/update operation.

This means that for every lookup value that is set requires as part of the data integration, an extra query to and from Dynamics 365 is required before the Create/Update operation would occur. This will slow down the time it takes to complete the transaction for this row (e.g. the record/second would decrease).

On the Case Entity, you may have several lookup GUIDs to query before the Case can be created:


If we had these GUIDS before we started the import (as we should have imported the prerequisite data beforehand), then we could populate these fields without having to perform the additional query.

This would reduce the number of steps the profile has to complete to just a single Create Case step and improve the performance of data integration import speed (records/second).

For smaller data-sets, this may not be required as the data does not take much time to load; but for the larger data-sets we can see that improvements to import speed can have a positive impact on the total time it takes to load the entire data set.

To record the GUIDS of the pre-requisite data (especially configuration/Account/Contact data) I will always write back to the source data row in my staging database with the record GUID in the original profile. Or, I will build a separate profile which query Dynamics 365 (as the source) and targets the staging database. This will literally just write the GUID back to the Staging database source row.

So back to my Case import example, assuming I had captured the record GUIDS of each lookup, I could either:

  • Join to the Account,Contact and Product Tables to include the GUID columns in my Case query.
  • Perform a simple SQL Update Script to populate these values (this will take seconds).
Larger Volume Data-sets

Identify your largest data sets, some of these may be simple but some of them maybe complex. The aim is to improve the import speed (record/second) and the larger data-sets will gain the most from performance improvements.

The downside is, importing large volumes of data will eventually slow down table performance in Dynamics 365, which also will have a negative effect when trying to match to records as part of data profiles.

Reduce the number of steps in the profile by completing additional ETL transformations on the Staging Database (if available) – like pre-populating lookup fields; aim for straight inserts or straight update.

Bulk Operations

For simple – high volume profiles, many tools (even bespoke Console Apps/Web Services_ can take advantage of the Bulk methods (ExecuteMultiple) provided by Dynamics 365 where you can submit data in blocks as part of a single transaction. These are designed to increase the throughput of your data to Dynamics 365.

Matching Optimisation

Nearly all data integrations will involve some sort of matching criteria, whether this is to return related record ID’s to populate lookup fields on import or when matching for either create new or update existing records.

Matching to records will often involve matching on key fields (in most cases a single field or key, in others the matching criteria may involve multiple field values.

Single key matching example – matching to Orders:

  • OrderNumber exact match


Multiple key matching example – matching to Contacts:

  • First Name AND
  • Last Name AND
  • Email Address AND
  • Postcode all exact matches



Whilst matching to these values may be required and cannot be changed. we can actually create indexes on these columns quite easily. In Dynamics 365, you can directly add these to the Quick Find View – Find Columns for that Entity;  Dynamic 365 will create a database index for you automatically, this is extremely useful for Online environments where you can directly affect the database without touching it.

Additionally – it may be useful to also create indexes on the source columns used for matching over larger data-sets.

Alternate Keys

Where unique records are required to be matched, it may be worth considering creating an Alternate Key in Dynamics 365. Ben Hosking has written a very good article on alternate keys here. This will also create an index in the CRM database which improves lookup times.

Record Ownership

Assigning records to User or Team can take a while even through the web interface, and this may be additionally impacted by cascading relationships to the records children (if applicable) where is child is also assigned to the User or Team. So this may have a negative impact on the data import speed

Where the requirement for records to be have their ownership during a data migration/integration, I considered it best practice on larger data sets or records where cascading relationship exists (e.g. Accounts and Opportunities) to either:

  • Recommended – Split out the record assignment into a completely separate profile after the data has been integrated with Dynamics 365. This will increase the performance of data integration initially creating or updating the record; the Assign action can then be completed post load.
  • Or have an additional relationship to the User Entity (i.e. Assign Owner), populate that during the migration and then have Workflow post Migration/Integration set the Owner to that of the Assign Owner field.


Tip: Ensure Users:

  • Have been created
  • Assigned the correct Product License (if Online)
  • Security Roles have been assigned which allows them to own the record type you are integrating with.

Record Status

Completing Activities or setting record Statuses can have the same impact that assigning record ownership can have (e.g. cascade behavior). Also, depending on when the profile sets the status, further updates cannot (should not be in most scenarios) be made to those records through the data integration; especially with Activities – you will receive a hard Dynamics 365 error.

So my recommended approach is to consider having a separate record status profile as this will increase the performance of data integration initially creating or updating the record; then a profile to set the record Status executed post load (assuming no further record updates are required).



This completes the final blog*: Performance Best Practices blog for the data integration series, I hope you can start incorporating some of these principles into your data integration designs.

Thanks for reading,


*I lied, there is another post to come with more best practices!

App for Outlook, CRM, CRM features, Dynamics 365, Mobile, Unified User Interface

Introduction to the App for Outlook – Dynamics 365 July Release Preview

Recently, there has been a lot of emphasis around the Dynamics 365 July 2017 or v9.0 as its also known as; Microsoft released the Unified User Interface (or UUI) for Dynamics 365 with this released for new Office 365 tenants. This built upon the design concepts of the MOCA client with the philosophy:

Code once, deploy everywhere.

I have previously written an article regarding the UUI when V9.0 was first announced to the world here:

Unified Interface for Dynamics 365

The Present

Fast forward until now – you can finally provision trials with Dynamics 365 V 9.0. A few of my colleagues and I have been putting a lot of effort into testing the new UUI in earnest to try and help with our customers come up with a clear upgrade strategy to upgrade to the next version of Dynamics 365.

Our testing for the UUI has been broken down the testing into the following topics:

  • App for Outlook
  • Tablet/Phone Mobile Client
  • Mobile Web Client (not the “web refresh”)

Spreading the Knowledge

My colleague, Chris Huntingford had already written an brilliant introductory blog (link above or here) into our initial findings of the Web UUI, which lists a few “Awesome” features and a few of the issues we have encountered so far which we have shared for all to read.

This article received some really good feedback from the Dynamics Community which highlighted some cool items that we needed to consider or had missed, so thank you!

Additionally – I would like to thank Jason Almeida and Rob Dawson for assisting us on the continued path of testing the UUI!

The “App for Outlook” – a Consultants Story.

So moving on to business – I have been testing out the new App for Outlook on my trial demo environment with two main objectives:

  1. How do we install the preview version of the App for Outlook?
  2. What can we do with the App for Outlook?

This blog will serve as an introduction and will aim to help you understand how the App for Outlook can be deployed and installed (point 1) and a high level overview of what it can and cannot do functionally; my next blog will cover point 2 in more detail.

Firstly, you may be asking yourself – “why are we talking about this when there is the Outlook Client”?

I would like STOP you right there – this article is in reference to the new cloud based App for Outlook addin which was first released with Dynamics 2016; the Outlook Client (installed to the Users machine) has been deprecated (RIP) and will be removed from support at some point in the future.

Preview Only


As part of Microsoft UUI release with Microsoft Dynamics 365 July Release (V9.0) – the App for Outlook is only released in preview; which means:

  • Dynamics 365 (online), version 9.0 or later only.
  • Not supported – Microsoft will not provide full product support until General Availability (GA)
  • Not complete product/functionality

Full details can be found here.Prevvvvv
So whilst this is great for the Partner to get their hands dirty and do some testing for Microsoft – for new customers, they would be stuck in a scenario where the recommended email synchronisation method is not actually officially supported by Microsoft yet. They may need to go back to the Outlook Client as an interim solution until support is offered/general availability.

Ok – what do I need to get the App for Outlook running for my Users?

Good question – there are several pre-requisites to get the App for Outlook running, I will provide the basics with the necessary links for these as separately, these can be quite a detailed process to complete.

  • Switch to server side synchronisation (SSS) for all Email and Activities in your V9.0 instance – reference here.
  • Have a supported version of Exchange (Exchange 2013, 2016 or Exchange Online).
  • Have a supported version of Outlook (through Office) (2013/2016/Outlook for iOS/Android/Mac or Outlook on the Web OWA).
  • Configure the Users mailboxes for SSS.
  • Enable the preview in your environment.

How can I access App for Outlook?

Previously, with the Outlook client you were restricted to the following:

  • Supported versions of Outlook/Exchange (still the case with the App for Outlook).
  • Installation of the Outlook client on each device you wanted to use (Windows only)
  • One synchronising client if accessing with multiple devices.

For the App for Outlook, you can use a single installation (addin to your Exchange Mailbox completed at the server level) across a multitude of devices and platforms where you access your mailbox through an supported Outlook App (i.e. Outlook for iOS). You can use the App for Outlook on the following devices:

  • Outlook on the Web
  • Outlook for Desktop
  • iOS mobile
  • Android Mobile
  • Windows Mobile


What can I do with the App?

There are some major functionality differences between the App for Outlook and the Outlook Client (taken from here):

Feature Dynamics 365 App for Outlook Dynamics 365 for Outlook
Track and set regarding for email Yes Yes
Track and set regarding for appointments Yes Yes
Track and set regarding for contacts Yes Yes
Track and set regarding for tasks No Yes
One click set regarding Yes No
Shows recipients’ summary Yes No
Shows the regarding record summary in the email/appointment Yes No
Works with Outlook on the web Yes No
Works with Outlook desktop Yes Yes
Works with Outlook for the Mac Yes No
Works with phones Yes No
Open and create Microsoft Dynamics 365 record directly Yes Yes
Apply custom forms and business logic Yes Yes
Work offline No Yes
Apply email templates Yes Yes
Apply sales literature Yes Yes
Apply knowledge articles Yes Yes
Ability to monitor emails after sending Yes No
Sort, filter, format, group, and categorize views No Yes
Create Word mail-merge documents No Yes

A lot of people will focus on the negatives – so neither Task synchronisation or Offline ability will be available (with this release) of the App for Outlook.


However – Tasks would synchronise via server side synchronisation as they normally would – you just will not be able to set the regarding or access the CRM fields in Outlook for Tasks.

Whilst you will not have the ability to work offline, you now can with the mobile apps!. The new functionality (in my opinion) far outweighs the functionality missing with the ability to work on the move with your mobile device – though however, this does depend on which device you are using as shown below:


Understanding the above items is crucial when deciding whether the App for Outlook is a good fit for your customers/organisation as it will help identify whether your current Dynamic/Exchange deployment are supported* moving forwards.
As I had previously stated – my next blog will talk about the functionality of the App for Outlook – what’s cool and what’s not so cool!

Known issues

The below link highlights some already known issues of the App for Outlook – and will be kept up to date by Microsoft.

You can find the second part to this article here.

Thanks for reading – MW.

*when Microsoft fully release this for GA

Data integration, Uncategorized

Part 2: Data Integration Tools


In my previous blog – I briefly talked about data and the types of data integration. To recap – the three main types of data integration are:

  • Data Migration
  • Data Integration
  • Data Replication

Data import methods

Data can be imported into CRM via many different ways and either in a supported or an unsupported manner.

Unsupported data import

The main unsupported way some people get data into CRM is by direct database manipulation – Microsoft warns against direct insert/update/deletes directly database by using the phrase “Unsupported operation” ( ) i.e. they will not support your CRM system if you decide to play with the database.

Data imported into CRM via this method would not adhere to any security considerations (such as data restricted access for the users) and would not trigger any business logic (such as plugins, workflows etc.); basically the data skips the entire application layer of CRM.

In CRM Online – the SQL database is not even accessible so you are restricted to using the CRM Web services and this is the recommended approach (and the only approach I will ever use for data import*) for CRM on premise data integrations.

*the only exception maybe when I just need to read data out of CRM as a source data system where no update is needed

Supported data import

So when choosing a data integration tool – you will need to choose a tool that can import data into CRM in a supported manner (i.e. either through the UI or through the CRM API using custom application/web services or third-party data integration tools).

Data Integration Types vs Tools

Initially with CRM Projects choosing the correct Data Integration Tool to meet the Data Integration requirements (as these are not always known at this stage in the project), through the Diagnostics/ Analysis stages of a project the initial requirements will surface and the type of data integration will be known (i.e. Migration, Integration and/or Replication).

There are many data integration tools out there – but not all data integration tools can be used across the different types of data integration. For example, if a data integration tool is built to load data into CRM but not extract it from CRM then the tool is only really useful for data migrations/unidirectional (one-way data transfer) data integrations into CRM.

Data Integration Tools – what are available to me?

Firstly – there are many Data Integration Tools which I currently use to meet the data integration requirements of my customers; primarily these can be split into two high level categories:

  • ‘Free’ Tools
  • Cost-associated Tools (such as bespoke development or third-party data integration Products)

‘Free’ Data Integration Tools

The term ‘Free’ should be used loosely – mainly because they are free for everyone to use, but the customer is ultimately paying for your time (I hope!) to complete the data integration requirement.

There are many free data integration tools out there which can be used to aid you in meeting your data integration requirements; here are a few I used:


Microsoft has released the following tool which are included as part of an Office 365 subscription (and will only apply to CRM Online Organisations) and is currently only in preview in certain geographic locations (so I have not used it yet)

Additionally – Microsoft has released some very interesting tools that can be used as effective data integration tools (subject to cost and licensing over the next coming months):


I will endeavor to explore these tools over the next few months to gain a better understanding of them; undoubtedly some of you will already be familiar with these already!

Cost-associated Tools

Cost associated Tools are data integration tools where this is an initial outlying cost that must be covered to use the tool – such as purchasing a third-party data integration tool or designing and building a custom data integration application (such as a console app, web service, SSIS package etc.) which your company will need to allocate the required resource to build the application.

Third Party Data Integration Tools

As part of my Job function involves Data Integration – I have become certified in both Scribe Insight and Scribe Online which are developed by Scribe Software (I will do a separate blog on these products at a later date describing each product and how they compare against each other).They are my go to Data Integration tools of choice – my biggest challenge of late is deciding which tool to use! (again more of this another day).

  • Scribe Insight is a powerful data integration platform that is provided as an on premise data integration tool
  • Scribe Online is one of the market leading Cloud based data integration platform (or iPaaS – Integration Platform as a Service) which can provide a complete cloud based data integration between cloud data systems (such as CRM Online and SalesForce)

There are other tools out there; I just have not used any really so I cannot comment on their strengths and weaknesses and where they can be used to meet certain requirements as I lack knowledge.

Custom Data Integration Applications

There have been times where the purchase of a data integration tool may not meet the requirements of the type of data integration (such as a web service being called to retrieve data from CRM and transfer it to a target data source) as the data integration tool may be too slow for this type of request. This is where CRM developers are required to build custom applications to act as a data integration tool.

Examples of custom applications are:

  • Web Services – these sit in between two data systems and communicate through each others API’s
  • Console Applications – applications which sit on computer services
  • Plugins, Custom Workflow Activities calling CRM Actions
  • SSIS Jobs/packages

To summarise

In this blog I have identified some of (the many) data integration tools available for you to use (but not told you how to use them – that I will leave up to you); in my next blog – I will highlight the types of data integration and which tools (listed above) can be used to help meet the data integration requirements of your projects.

Thanks for reading! MW

Data integration, Uncategorized

Defining Data Integration Types


Over the next few weeks I will write a series of blogs relating to data (based on my personal experience) – this will involve the following topics (from the outset – I may add more!):

  • Defining Data Integration Types
  • Identifying the correct Data Integration tool for the project
  • How to plan and manage Data Integrations as part of a CRM project (including my best practices)


How many of your CRM projects have the vague Requirement of “Data”? I am willing to bet that 99% of your projects will have at least the basic requirement “Data Integration” such as importing Contacts or Account data at some point in your project (there is always that one customer who wants a vanilla system and will start afresh).

Every CRM project I have worked on in the last three years has involved an element of “data integration” – my role varies from (on a project by project basis and sometimes I perform all three roles in a single project):

  • Solution Architect/Lead Consultant
  • Functional consultant
  • Data Integration Consultant

Whichever role I am involved with in the project – my mind always considers the data requirements, the CRM solution being designed not only needs to match the business requirement and processes of the end-user – but the underlying data model should be designed to allow any historic or integrated data to be mapped correctly and maintain key existing information (such as relationships to other data). This means you need to involve data analysis early on in your project to make sure that the data can be imported into a suitable format.

Data Analysis

Analysis of source data (or data systems) early on will help you identify any potential tables and related attributes (fields and data types) early on in the project that you made need to custom build; it can also aid in mapping this data so you know what to expect further on down the line. The opposite side would allow you to see which areas of CRM have no concept in existing systems (such as Emails or Appointments etc.) and you can eliminate these areas early on.

The main output from data analysis will determine the type of Data Integration required.

Types of Integration?

The term Data Integration is loosely used when a project is incepted – what we need to define when we talk about Data Integration relating to a CRM Project is that we can split this term up into three main types:

  • Data Migration
  • Data Integration
  • Data Replication

Defining the type of Data Integration is key to determining the Data Integration tool we can use.

Data Migration

To put it simply – Data Migration is the process of transferring data from one or more data source systems or storage locations (such as a SQL Database, Spreadsheets or other files types, formats, or IT systems) into a single target data system. Once the data has been transferred from the source system to the target system, the source system is usually archived off and will no longer be used after Go-Live of a CRM project.

Some examples of a data migration:

  • Goldmine to Microsoft Dynamics CRM
  • Microsoft Dynamics CRM 2011 to Microsoft Dynamics CRM Online
  • Excel Spreadsheets to Microsoft Dynamics CRM
  • And So on…


Data Integration

Data Integration is a little more complicated and covers a broader method of data transfer – in its simplest form it might be one of the same scenarios as mentioned in the data migration section but a continuous one-way data flow from the source system to a target system (and the source system is still used). This is known as a Unidirectional Data Integration (One Way Data flow).

Some examples of a Unidirectional Data Integration (One-way data flow):

  • Back office ERP System to Microsoft Dynamics CRM
  • Website Lead Generation Microsoft Dynamics CRM Online
  • Microsoft Dynamics CRM Online to Microsoft Dynamics CRM Online (where development/UAT organisations maintain up to date data for testing and training purposes)


A Bi-directional (two-way data flow) Data Integration can be defined a data transfer between two data systems where data from each system is required to be transferred to each other.

Some examples of a Bi-Directional Data Integration:

  • Back Office ERP to Microsoft Dynamics CRM (and Vice versa)
  • Microsoft Dynamics CRM Online to Microsoft Dynamics CRM Online (where configuration data is kept up to date in all organisations – Dev or UAT and Live)


Omni-Directional Data Integrations

The same as a Bi directional data integration but where data is transferred across more than two data systems.

Some examples of an Omni-Directional Data Integration:

  • Multiple Back Office ERP systems to Microsoft Dynamics CRM to SQL Staging Database to Front End Website Database
  • Microsoft Dynamics CRM Online to Microsoft Dynamics CRM Online (where configuration data is kept up to date in across all organisations – Dev, UAT and Live)


Data Replication

What is Data Replication? Well in the CRM Online world – a customer does not have direct access to the CRM database like an On Premise customer would have access to (because – you know, CRM sits on top of a big shiny SQL DB!) – so this makes writing SQL based reports impossible (restricted to Fetch based XML SSRS reports) or can they effectively control the back up of their live database. So the term Data Replication is the process of making a copy of a target data system either into a replica database or data system. (Such as CRM Online being replicated to a SQL DB for reporting or local backup purposes).


Data Integration Tools

In my next blog – I will discuss the various data integration tools that I have used with my projects and I will mention other tools that I know of; but before we can talk about deciding which data integration tool to use – the type of data integration must be clearly defined as not all data integration tools can be used for each type of data integration.

Thanks for reading! MW

CRM, CRM tools

Microsoft Dynamics CRM: What tools should be in your arsenal?

I have been working with CRM for a little over 5 years now – throughout this time, I have discovered many useful tools that assist with my role as a CRM Consultant/Solution Architect.

Microsoft Dynamics CRM has come a long way since its infancy, confidence in the product grew and so did the size of the average project as more and more business decided to invest in Dynamics CRM. As a consequence of this – the Dynamics CRM community grew and grew with some very useful tools were developed by the Community (and later Microsoft as part of the SDK) which took scenarios which were either:

  • Repetitive and time-consuming tasks using the CRM UI (i.e. assigning 1000’s of security roles to users)
  • Functionality impossible to achieve using the CRM UI (i.e. having to resort to manipulating CRM Solution XML to perform required action such creating ribbon buttons)
  • Actions applicable to the current User (such as modifying the default Personal Option settings for all users)
  • Consistent development naming conventions (i.e. web resource naming)
  • Even Documentation??! (Meta Data Document Generator – how about every Field from every Entity in a spreadsheet please?)

Whilst each tool has its own unique characteristics and functionality – some are more useful than others depending on the scenario. They all how ever – share one thing in common, and that is that they are FREE.

I will go through a small list of the tools I recommend that you have in your arsenal as a CRM customiser or administrator (there are many more out there I am sure but I cannot list them all!).

XRM Toolbox

The XRM Toolbox is the Swiss-Army knife that all CRM Consultants/Developers/Administrators should have available to them when working on Projects. The XRM toolbox is just as it sounds – a collection of over 30 separate tools bundled together in a single windows application. The Application was conceived and built by Tanguy Touzard (Dynamics CRM MVP).

Throughout the years, the number of plugins has grown and grown and here are the ones I use the most:

  • Sitemap Editor – allows complete customisation of the CRM sitemap (Do I really need the Marketing area? Nope? Then I can disable or remove it!)
  • Iconator – tired of creating web resources for Entity icons? Easy – this can upload one or more at a click of a button. It can also allow you to set icons for Entities.
  • User Settings Utility – I often get asked if peoples Personal Options can be defaulted (i.e. 250 records, CRM home page, Formats etc.)
  • View Layout Replicator – When building custom Entities, Views are always required to be customised and created – this plugin allows you to copy the columns from System or Personal View’s into selected target (Views)
  • And finally: the ‘Ribbon Workbench’ – this up until has been a standalone tool called the ‘Ribbon Workbench’ which has finally been included as a plugin in the XRM toolbox. (I will talk about this more later on!)


Microsoft Dynamics CRM SDK

(latest version 8.1.0)

The Software Development Kit for Microsoft Dynamics CRM is updated with each release of CRM (major or Service Pack update version). This has some useful tools for the CRM community to use such as:

  • Plugin Registration Tool – The main tool for updating custom Plugins/Workflow Activities or steps in CRM, very handy indeed!
  • Configuration Migration Utility Tool – Useful for transferring configuration data from one CRM Organisation to another where customisations are near matches (i.e. Country records or records that define settings and are used in Workflows). This tool keeps the Unique ID (GUIDS) of the records across the Organisations.
  • Package Deployer – Allows CRM Solutions, Configuration data, files and custom code operations into a single package which can be deployed into any CRM environment
  • Web Resource Utility – allows Web resources to be created in CRM in a consistent and clearer manner (I find the XRM toolbox version more suitable for the less technically minded like myself!)


Ribbon Workbench

The Ribbon Workbench has recently had a makeover and is now available as an HTML version for CRM 2016 and above – it is also now featured as a plugin as part of the XRM Toolbox. Originally – this tool came packaged as a CRM Managed solution and provided CRM customisers a graphical interface in which to customise the CRM Ribbon (now Command Bar) and add/amend/hide Ribbon buttons. It is the brainchild of Scott Durrow (another CRM community MVP) will continue to be a vital tool for CRM customisers for years to come.

Ribbon Workbench_MS

CRM REST Builder

Recently – Microsoft have released a new End point to replace the recently deprecated Microsoft Dynamics CRM 2011 endpoint (; this new end point is known as the ‘Web API’ which appeared in the CRM 2016 release. This means that any new or existing code that uses the 2011 release would cease to work in later versions of CRM and would need to be re-written to use the new Web API endpoint.

I am currently working on a (long) Project which contains JavaScript which used the old 2011 endpoint; I decided I would make the change to the form scripts to utilise the new Web API (this means it will be fully supported moving forward and will not stop working when the 2011 endpoint is removed). I now just needed to learn the new Web API to use with JavaScript REST queries; it was during this learning I discovered a blog by Jason Lattimer (yep you guessed it – another CRM community MVP #somuchlovefortheseguysandgirls) where there exists a tool which does the following: “If you aren’t familiar with the tool it’s essentially a code generator that creates JavaScript to perform actions against CRM’s REST endpoints” (which Jason created!). It has also been updated to include the new Web API and is installed into your chosen CRM Organisation as a managed solution. It is now one of my most used tools for assisting me with related record queries using JavaScript (and I am not a technical developer).

CRM Rest Builder

There are many good blogs on the new API – I would be here all day listing them but I will link the ones I used:

Workflow Executor and Workflow Essentials

I work for a company called Gap Consulting LTD (all opinions are my own by the way!) based in the UK; before working at Gap I had limited or no knowledge about the tools offered freely by Gap. I now could not imagine working on a CRM project without the following tools in my arsenal:

  • Workflow Executor
  • Workflow Essentials

These tools were designed and created by my colleagues Rob Boyers and Neville Bowers.

Workflow Executor

On demand Workflows – are one of the best solutions to fixing bad data or performing business logic on multiple records at a time. The limit with CRM is the number of records you can execute a Workflow against (in a View) is restricted by your Personal Options (at a maximum this number can be 250 without CRM DB modification). In steps Workflow Executor – a simple managed solution which allows you to execute (with throttling) on demand Workflows against all records in a System or Personal View irrespective of your personal settings.

This tool has been recently re-written in HTML 5 to be compatible in a multi browser supported environment.


Workflow Essentials

Workflow Essentials extends out of the box Workflow functionality in CRM by grouping many custom Workflow Activities together in a single managed solution. All the custom Workflow Activities found in Workflow Essentials are taken from Workflow Assemblies found on CodePlex and packaged together to be supported with CRM Online (where Sandbox isolation is required).

I use this tool frequently to create complex automated business logic without the need for bespoke plugin development (such as distributing child Workflows to all related child records against a parent record).


CRM Themes

This morning I was busy choosing playing around with some themes for one of the sandbox instances I am using for my current project (you know to differentiate the environments between Development, UAT and Live). I automatically started using this website link to help me visually pick and select my colours which I would like to apply to this theme (I was bored of a blue and wanted a darker green).

This tool is great for replicating the functionality of create themes for CRM – it even gives you the Hex/colour codes for you to copy and paste (even export) to CRM. So if anyone wants a dabble at creating themes for CRM before actually making changes to CRM – have a play with this tool. (Would be cool if it was updated to allow us to upload Images and change the text displayed in the main navigation area!)

This site is hosted in Azure (assuming a Web App here) and is the invention of Guido Preite (another CRM MVP would you believe).

CRM Themes

And Finally….the greatest asset (although a little cliché)

The final asset every CRM customiser/administrator should have in their arsenal is:

The Microsoft Dynamics CRM Community

Why? The CRM Community is the greatest asset you can have in your CRM arsenal because if you have one of the following:

  • A question?
  • An idea!
  • You are stuck with a particular issue.
  • Need guidance or training or certain functionality (assuming you have done some level of googling)
  • Or just another point of view to help you solve a problem

Then ask the CRM community; they are always willing to help or assist you or will point you in the direction of the resource that you are looking for. They are where the knowledge lies, 100’s of people involved in different sectors all building on/into the same platform – your issues/questions will no doubt have been encountered before and a solution will be out there with one of your peers.

Great Resources


CRM Community

Thank you all for reading! MW


OR Statement Workflows in CRM 2013, Spring Wave Release – New functionality

One of the new features of CRM 2013 was the inclusion of Real-Time or Synchronous Workflows, this gave CRM customisers the ability to trigger a Workflow which would run instantly with out having to write Plugin automation/JavaScript. To put this into context – if a user updated a field on a record, this update would trigger a real time Workflow (defined in the activation criteria)  and could update a field on the form in real time without refreshing the page.

For further information about Real Time (Synchronous) Workflows, a blog written by Steven Foster can be found here:

Now, Microsoft has gone one step further and has  implemented one of the most requested features missing from Workflow in CRM with the Spring Wave release for Microsoft Dynamics CRM 2013.

I introduce you to the ‘OR’ statement.

The OR statement has finally been added as part of the conditional criteria for Workflows, just like it is included in the Advanced Find functionality which lets users build up their required lists and views of data based on conditional logic.

For example, a user has the requirement “Show me a list of Accounts which have the word ‘Hospital’ or ‘Clinic’  contained in the name (I have omitted Status in this example):


When building a Workflow, the customiser needs to define  four certain aspects:

  • When to start them?
  • Should they run as a background Workflow or a real-time Workflow?
  • What actions should they perform
  • What conditions should the actions perform?

(For further information about these aspects can be found in the Help section on technet

I am now going to focus on the last aspect “What conditions should the actions perform?” by using an example and including the OR statement and where/how it can be used.


For each “Active” Account record in CRM, if the Name contains either ‘Hospital’ or ‘Clinic’ or ‘NHS’ then update the Parent Account lookup field to an Account called = ‘National Health Service’ (a fairly useless requirement but will serve the purpose for our example).

Previously in CRM, we would have to build up conditional logic with multiple conditional branch’s (‘Check Condition’s) and record updates. It would look something like this:


It can be seen there is no ‘OR’ statement and conditional branching was the only was to achieve this conditional logic:

  • IF the Account Name contains Hospital then Update the Account
  • Otherwise IF the Account Name contains Clinic then Update the Account
  • Otherwise IF the Account Name contains Hospital then Update the Account
  • Otherwise stop the Workflow

Either three separate update steps to the Account are required to perform the Parent Account update or a single child Workflow (performing the Account update step) will need to be triggered 3 times (one for each conditional branch).

Logically, the Workflow performs a check  on the first ‘Check Condition’; if the record matches the criteria the Workflow executes the update step, if no match is found the workflows proceeds to the next conditional ‘Check Condition’ and checks the criteria and so on. If no match is found in any of the branch ‘Check Condition’s, the Workflow is completed.

Performance wise, this takes the Workflow longer to run as it has to run against each ‘Check Condition’ in order, until a match is found; for Accounts with the Account Name containing ‘Clinic’, the Workflow performs 2 additional ‘Check Condition’s than for an Account with the Name containing Hospital.

The OR Statement.

Microsoft allows us to perform the 3 ‘Check Condition’s above in just 1  ‘Check Condition’ using the “Group OR” button.

This allows us to group checks together with either an “AND” or an “OR” statement.



Now we can build a Workflow that can perform all the conditional ‘OR’ checks in one ‘Check Condition’.



In theory, this should increase the performance of the Workflow as it only needs to perform 1 Check Condition and 1 Update step, although it will take slightly longer to perform the check condition. From a customiser point of view, this has saved time constructing these types of Workflows as it reduces the number of Conditional Branch’s and Check Conditions we need to input which we previously had to build.

If you have updated your CRM 2013 Organisation (either On Premise or On-line), you can now have fun transforming your existing Workflows which use the Conditional Branch logic to create ‘OR’ statements into actual ‘OR’ Statement Workflows!

Thank you for reading and I hope this blog helps!




Microsoft Dynamics CRM 2013 ERD Visio’s

Part of my job as a Microsoft Dynamics CRM Consultant involves writing Project documentation for each of my customers. One of the most requested items in the documentation is an Entity Relationship Diagram or ERD for short. 95% of my previous projects are not out-of-the-box (OOB) Microsoft Dynamics CRM (CRM), and to meet the customers requirements involves at least one or more additional custom entities in addition to the OOB entities such as Account, Contacts, Opportunities/Order etc.
When I am writing documentation and creating any ERD, I always have a base Visio template showing the OOB entities and the basic relationships between them and all I have to do is add my custom relationships and custom entities. Building this template up was painstaking at first just to get the relationships/entities correct, I deemed this task as invaluable as it saved myself time each time I that I have reused them.

Earlier today, I was eating my lunch and quickly checked on the CRM forums/Facebook/LinkedIn groups and happened to stumble across a link for the Entity Relationship Diagrams (ERD) for CRM 2011, I click on the link ( and low and behold I am on the official Microsoft website and Microsoft have provided us with an ERD. You can then download a zip file containing a seperate Visio file for each main area in CRM, such as Activities, Sales, Customer service etc. This gives a breakdown of each entity involved.

I was actually amazed at this, as I know how little documentation Microsoft had provided the CRM community in the past. So, I asked myself the question: “WHAT ABOUT CRM 2013???!”,
So I Googled ‘Microsoft Dynamics CRM 2013 Entity Relationship Diagram” and hit enter (sorry for you Bing fanatics out there) and again the link appeared:

And here is a screenshot of one of the Visios:


It is dated 17/04/2014, this means it will be out of date for the Spring Wave release update with the inclusion of the new SLA and Entitlement entities (plus any other key entities supplied with the Spring Wave release) and hopefully will be updated in the near future.

Regardless of this fact, I can now compare/include these Visio’s into my template giving the customer a list/overview of entities that are provided OOB for each area in CRM, these Visios will help save time with these areas of the documents I need to produce ERD’s for and allowing me to focus more of my time on the key details of the documentation I am producing, such as the functional and technical requirements.

I hope this blog helps some of my fellow peers in the CRM community!