Business Intelligence + Dynamics CRM, getting the Data

2 July 2014

Being able to view dashboards and mine you Dynamics CRM data is of utmost importance to most organisations. Dynamics CRM has great features allowing you to visualise your data using Dashboards, charts and other graphics.

Sometimes though Dynamics CRM data needs to join up with data from other systems. This often requires you to extract that latest data from Dynamics CRM and load it closer to your data warehouse.

This blog quickly demonstrates using DS3 to extract a set of entities from Dynamics CRM and schedule the continuous download of changes.

Open DS3 Automation Server and create an empty project.

New Project

This empty project will hold the steps that will extract the data from each entity in CRM.

Open the Project and add a Data Synchronisation Step.

Add Step

Create and document the step and then click Save. An empty integration project will be created so you can open and get editing easily.

Create Step

Once you have saved the step, open the integration definition by clicking 'Open File'.

This will launch DS3 Developer when we can define the integration source Dynamic CRM entity, SQL Table Target and mapping.

Navigate through you connections tree and drag and drop the account entity onto the Data Source (alternatively you can right-click and select 'Connect to DataSource (A)'

Drag Drop

All the Account entity fields in Dynamics CRM will now be available for integration.

Data Source A

Tick beside each field you want extracted from the entity, this will add them to the source schema.

Add Columns

Using the tools menu you can as DS3 to create a SQL Table from the exact list of fields you have chosen.

Open the Tools menu and click the 'Create SQL Table' option.

Create SQL Table

The SQL Table Wizard will appear. This will guide you though creating a SQL Table with the same schema as the source CRM Entity.

SQL Wizard

At the end of the wizard, DS3 will use the newly created table as the target.

Target

To verify if the integration is working, click on the Compare A->B and you can then preview the records that will come across.

Compare

Now you can just save the project back to the Automation server by clicking 'Save'.

Back in the automation server you can just run the project by click 'Start Project'

Start Project

You can view the progress on the 'Reporting' page in real-time.

Reporting

... and when it completes successfully.

Success

Drilling down into the logs will show us some detail on the integration such as 9916 records were missing from the SQL Server and it took 25.225 seconds to insert them.

Log

We keep repeating the process of connecting to the Dynamic CRM entities and creating target tables until we have a set of entities to bring into the Data Warehouse.

Repeat

We now have to set a schedule to run these extracts. The Automation server 'Triggers' projects based on an event. We can set these events or triggers to fire based on many different criteria. We are going to use the simplest 'Time of Day'.

Create a new Time Trigger from the 'Triggers' menu.

Triggers

Set the time to 11 pm every week night and call it 'Late Night Weekday Trigger'.

Time Trigger

Navigate to the 'Dynamics CRM Data Extract for Reporting' project click Add Trigger from the sub menu. Select the late night trigger.

Whenever we need to download an additional entity, we can just add a new integration to our steps and it will be included in the synchronisation.

| |