Using the Microsoft Dynamics CRM component in Matillion ETL for Snowflake

Matillion uses the Extract-Load-Transform (ELT) approach to delivering quick results for a wide range of data processing purposes: everything from customer behaviour analytics, financial analysis, and even reducing the cost of synthesising DNA.

Microsoft Dynamics CRM forms part of the Dynamics family, and offers a web-based customer relationship management solution. Matillion ETL for Snowflake’s Dynamics CRM Query component provides a simple interface to this service, allowing you to easily integrate CRM data into your reporting and analytic solution. The component is self-contained: there’s no need to install additional components, and there’s no charge for using it!

Microsoft Dynamics CRM component in Matillion ETL for Snowflake

The Microsoft Dynamics CRM component negotiates with Microsoft’s CRM 2011 Web Services APIs on your behalf, and offers a large data model containing well over 200 tables and views. You can use it to connect to CRM 2011, 2013, 2015, and 2016 (version 4.0) instances - with or without IFD - as well as CRM Online Office 365 accounts.

Microsoft Dynamics CRM component in Matillion ETL for Amazon Redshift - Edit properties

All communications are performed securely over SSL/TLS by checking the server's certificate against the trusted certificate store. When configuring the component, the key properties you’ll need to have to hand are:

Your organization’s root URL
A username and password

Advanced Mode

By default the Dynamics CRM Query component operates in “Basic” mode. Therefore, you only need to choose a table and columns from dropdown lists describing the data model. But if you prefer you can choose Advanced Mode, and this is a way gain access to some extra features.

In “Advanced” mode, you specify your own query, in the form of a SQL SELECT statement. The Dynamics CRM Query component converts this into equivalent API calls on your behalf. You can use a wide range of SQL-92 features including:

Aggregate functions COUNT, COUNT_DISTINCT, SUM, AVG, MIN and MAX
String functions, such as CONCAT, CONTAINS, REPLACE and SUBSTRING
Various DATE manipulation functions
Mathematical functions including ROUND, ABS and SIGN
Joins

Joins must be on tables which are related to each other via a Dynamics CRM relationship, and they are specified using standard SQL syntax. For example, you might want to find the Names of all Accounts that have Contacts and the FirstNames of those Contacts:

Microsoft Dynamics CRM component in Matillion ETL for Amazon Redshift - SQL Query

You can also use a LEFT JOIN instead. For example to query all Accounts and the Equipment ID for the preferred Equipment defined for that account (if any):

Microsoft Dynamics CRM component in Matillion ETL for Amazon Redshift - SQL Query2

The Dynamics CRM Query component in context

The best way to use this Matillion ELT component is as part of a wider orchestration. The component loads data into a target Snowflake table, but it’s destructive. That means any data that was in the target table beforehand will be removed first. So you should treat the target table as a transient staging table, and have your orchestration job do two things:

Perform the Dynamics CRM Query
Copy the newly-loaded data into a more permanent target table

The second part is best performed by a Matillion transformation job, inside which you can use all of Matillion’s many transformation components to convert the data into the desired format, ready for use in reporting and analytics.