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 BigQuery'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 BigQuery

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.     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:     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):    

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 BigQuery table, but it’s destructive. That means the component will remove any data that was in the target table beforehand. So you should treat the target table as a transient staging table, and have your orchestration job do two things:

1. Perform the Dynamics CRM Query
2. 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.

Useful Links

Microsoft Dynamics CRM component in Matillion ETL for BigQuery Integration information Component Data Model