Week 10: Do you Know? Series of the Week

Weekly, the Dynamics GP team is posting a ‘Did you know?’ series.  We give tips, tricks, and general comments around features and functions that already exist in Microsoft Dynamics GP. 

Our challenge to you is…how much do you really know about Dynamics GP?

This week I’m going to break from tradition slightly as our topic isn’t really a feature directly in Dynamics GP.  Our topic this week is actually a feature in Excel and SQL Server that our Dynamics GP Customers and Partners absolutely MUST know about called the Data Mining Add-ins!

If you haven’t been able to tell already, we here at the Inside Microsoft Dynamics GP blog are pretty big on the Excel reports that are available in Microsoft Dynamics GP 10.0 (see previous blog posts about Excel here). They provide real-time data (no more manually exporting or refreshing reports), they can be shared with the entire organization (with flexible security of course!), and they let everyone analyze their Dynamics GP data with one of the business world’s favorite tools when it comes to slicing and dicing information.  If those aren’t reasons enough for you to deploy these reports immediately, I think this cool Add-in for Excel and SQL Server may be the tipping point!

Once the Data Mining tools are installed, you’ll find a couple of new Toolbars in Excel that let you do some REALLY great analysis of your data.  Here is a screenshot of the Analyze tab that comes with the SQL Server 2008 version of the add-in.  The one button I have showed a number of times recently is the “Shopping Basket Analysis” option.

This feature churns through your detailed sales history and picks out the items in your inventory that tend to sell with one another…highlighting the most commonly matched pairs of items that are good candidates for cross-selling and/or promotional bundling.  It even computes the “lift” for each combination of items to help you understand the potential impact to your bottom line if these items were paired more often.  Let me show you an example of the output from this particular feature on an actual Dynamics GP Report (the SalesLineItems Default.xlsx report):

In the example above, let’s say a Home Electronics business wanted to analyze their sales history to see which items were commonly being sold together.  Now look at Row 5 on the screenshot above.  The output is telling them that the item “Receiver 2.1 Channel Model 03” sold 23 times, with 13 of these sales also including item “LCD Television Model 01” (for a linked sales percentage of 56.52%).  The average value of these combined sales was $2,272, so if combined together for all 23 sales, the overall value (or “lift”) would be over $52k. The business now has a firm understanding of which items commonly sell together, which may help them train their sales staff on the best cross-selling opportunities, or perhaps create some promotional bundles to help boost sales.

That is the type of analysis companies typically pay loads of money for with complex and expensive Business Intelligence solutions.  If you are running Excel 2007 and Dynamics GP 10.0 with SQL Server 2005 or 2008, you can have this type of analysis today…FOR FREE!

You can download the Add-in for your specific version of SQL Server here:
Add-in to Excel 2007 for SQL Server 2008
Add-in to Excel 2007 for SQL Server 2005

I should point out that only the SQL Server 2008 version of the Data Mining Tools contains the Shopping Basket Analysis feature I described above.  They added some new analysis features in the 2008 version, this being one of them. However, there is a whole slew of other analysis tools I haven’t even had a chance to play around with yet that I am guessing our Dynamics GP customers would find very beneficial.  Download them and try them out today!

PS: Partners, if you want to demo this to your prospects or customers, here is a handy DemoMate file you can leverage!

AndyW