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!