Business Intelligence + Dynamics CRM, getting the Data

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.

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.

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

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

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

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

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.

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

At the end of the wizard, DS3 will use the newly created table as the 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.

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'

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

... and when it completes successfully.

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.

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.

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.

Set the time to 11 pm every week night and call it 'Late Night Weekday 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.