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


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!