Microsoft SQL Server 2005 Data Mining Add-ins for Microsoft Office 2007 with Microsoft Dynamics CRM
SQL Server 2005 Analysis Services (SSAS) provides tools for data mining with which you can identify rules and patterns in your data, so that you can determine why things happen and predict what will happen in the future—giving you powerful insight that will help your company make better business decisions.
You can use data mining to answer questions such as:
- What will customers buy? What products sell together?
- How can a company predict which customers are at risk for churning?
- Where has the marketplace been, and where is it going?
- How can a business best analyze its Web site usage patterns?
- How can an organization determine the success of a marketing campaign?
- How can businesses prevent poor quality data from entering the system?
In March 2008, Reuben Krippner presented two breakout sessions at Convergence US on Extensibility with Microsoft Dynamics CRM.
During these sessions I covered a number of live demonstrations of how you can extend and integrate Microsoft Dynamics CRM. One of the “big hit” demos involved the use of the SQL Data Mining Add-in for Microsoft Excel 2007 with Microsoft Dynamics CRM. I have packaged up this example as a hands-on-lab for you to try for yourself. It is very important to note that as part of your investment in Microsoft Dynamics CRM the underlying SQL Server 2005 platform provides you with very powerful Analysis Services. Everything that is used as part of this example is included with SQL Server 2005 Standard and Enterprise editions and the Excel add-in is completely free of charge – incredibly powerful and your only investment is time!
For loads of background materials on SQL Server Data Mining go to http://www.microsoft.com/sql/technologies/dm/default.mspx
A few explanatory notes first! As always, the samples are supplied as is with no support or warranty provided by Microsoft. Feel free to take this utilize as you see fit. Special thanks go to Philip Rawlinson from the Microsoft Dynamics CRM UK team who set out the concepts for me to borrow and extend! This utility will work identically with either Microsoft Dynamics CRM 3.0 or 4.0.
Steps to follow:
Step 1: Install the Data Mining add–in for Excel 2007 (check that this has installed correctly by starting Excel 2007 and you will see a new section in the ribbon called ‘Data Mining’.
Step 2: Start Excel 2007
Step 3: From the ribbon interface in Excel 2007, select Data à From Other Sources à From Microsoft Query
Step 4: We need to create a data source for Microsoft Dynamics CRM – this connection will point at the Microsoft Dynamics CRM filtered views (more on this later).
Step 5: Select <New Data Source> and click OK.
Step 6: Set the name of your data source to be Microsoft Dynamics CRM. Set the driver to be SQL Server and then click the ‘Connect…’ button.
Step 7: Pick a server from the list provided or enter ‘.’ if this is on the local SQL Server. Click Options >>.
Step 8: Select your CRM database from the Database drop-down list. It should have a name like organizationname_MSCRM. Click OK.
Step 8: Click OK.
Step 8: Click OK.
Step 9: Scroll down the Available tables and columns list and expand FilteredSalesOrder. Select the following columns from FilteredSalesOrder:
- salesorderid
- ordernumber
Step 10: Scroll down the Available tables and columns list and expand FilteredSalesOrder. Select the following columns from FilteredSalesOrder:
- salesorderid
- productidname
Step 11: Click Next>
Step 12: Click OK – we will manually build the join in the next step
Step 13: Drag and drop salesorderid from FilteredSalesOrder to salesorderid from FilterSalesOrderDetail – this creates the join between the two views so that the sales order relates to the individual products purchased as part of that sales order.
Step 14: Click the Return Data button to publish this data to our Excel worksheet.
Step 15: Click OK on the Import Data dialog box
Step 16: Delete the two salesorderid columns in our worksheet as we do not need them anymore. Right-click the column-heading and select Delete.
Step 17: Click the drop-down arrow on the ordernumber column heading and select Sort A to Z
Step 18: Select Data Mining from the Excel 2007 ribbon and then click Associate – The ‘Associate’ Algorithm is a mathematical formulae that is built to detect patterns in data and lends itself to analysis for cross sell initiatives.
Step 19: Click Next in the wizard
Step 20: Click Next in the wizard.
Step 21: In this next screen you can change the parameters of the Data Mining Model
To learn more about these parameters please refer to:
http://msdn2.microsoft.com/en-us/library/ms174768.aspx
Step 22: Change the Minimum Support value to 5 for your first run to ensure you get a result set back (this is the minimum count of similar transactions to include in the model), if this also fails perhaps lower this and also the probability. Again to learn more about these parameters use the link above.
Step 23: Click Next
Step 24: Check the box marked Enable Drill Through and click Finish.
Step 25: View the results – Here we are looking for a high ‘Importance’ and then a high ‘Probability’. In the example here we can see that a rule has been detected where there is high probability of selling a ‘Road Bottle Cage’ with a ‘Road 150 Red’. Again for more information on the parameters please go to the article link above. Click the Copy to Excel button to bring the results back to your Excel worksheet.
Step 26: Click on the Dependency Network tab to see the rule set depicted graphically in terms of the associations between products purchased.
Step 27: With this insight you could now set up a marketing campaign and target those individuals where they have bought one product but not another. The possibilities are endless and this just illustrates the use of one of the data mining algorithms at your disposal, e.g. this could be used in service organisations to find the most common combinations of service issues or where one issue directly causes another.
Have fun with these samples! The world of predictive analytics awaits and is free-of-charge! J
Kind regards,
