Make the most of your CRM data–Create a Data mart

Useful Business intelligence is becoming more and more of a vital requirement, and the ability to see the health of your business is a key to success. Many Line of business (LOB) systems, including Microsoft Dynamics CRM sit on a vast amount of data, but pulling them out in a useful intuitive way, and combining the results from different, disparate sources is not always easy.

As a good first step, creating a Data mart has to be the way to go.

What is a Data mart?

A Data mart has been defined as ‘A body of historical data in an electronic repository that does not participate in the daily operations of the organisation.’

The key to the definition above is that the information contained within the data mart is separate to the information in the LOB system. This is actually quite important, as running queries and calculations against a live database can cause performance issues – I know because it’s happened to me. Running them against a completely separate Data mart wouldn’t cause these issues.

Data marts therefore are not live views of the business data – transactions as they occur during the day. The information stored in Data marts is usually updated at set intervals, thus keeping the overhead on the LOB system to a minimum.

So what are the advantages?

Apart from the already mentioned performance benefits, the major motivation to create a Data mart is that it is there to do one thing and one thing only – provide business intelligence. It doesn’t have to have all the information that the LOB database holds, just the data relevant to the required intelligence.

Once that data has been created in our Data mart, we then have the opportunity to create all sorts of views, functions and Stored Procedures (SP) against it – specifically with the mind set of getting BI out.

I blogged a couple of weeks ago about creating a custom function that would take two dates – for example a created and resolved date, and calculate the days in between, but take out the weekends (much like Excels NetWorkDays function. This was useful to run against cases to see how many workdays a case was open for. Once this was created as a custom function, we then called it against the created and resolved dates and saved the results as a view.

As another example we wanted a view that just showed a count of cases opened and closed and grouped by account and month. This would then be easy to show in a trend chart highlighting cases opened/closed by month on a year by year basis. Trend analysis is useful to see where there might be a support shortfall at certain times of year, or perhaps a training need.

While much of what I have said can be pulled out of the LOB system, creating a Data mart with all the associated views, SP’s, and custom functions gives you greater flexibility, and a more structured BI offering.

I won’t go in to the ins and outs of creating the Data mart in this blog, as I may save that for another time, but do a quick search and you’ll see several approaches to this.