Featured Post

Dynamics GP 2018 is now Released

It is officially published that Microsoft Dynamics GP 2018 is available, the download link is provided below: Product download page ...

Wednesday, February 4, 2015

BI Essentials Series | Deploying SQL Data Mining for MS Office (Part 1)

 Another essentials series is seeing a light today that is related to the insight of your data, Business Intelligence series for Dynamics GP.

The importance of this series comes from the fact that any ERP system needs some sort of an intelligent reporting layer that plays the role of interpreting and analyzing your raw data. Remember, when talking about BI, data cleansing comes as a required prerequisite to be considered initially, this is the main purpose of the previous Data Cleansing Essentials series


The first topic of this series is “SQL Data Mining for MS Office”, a very important add-on that is deployed on the SQL server level to provide important analysis and reporting capabilities for your ERP system, which is Dynamics GP specifically in our series.

SQL Data Mining Prerequisites:

  • Microsoft Office Excel (2010 or 2013, 32 or 64), make sure to have the appropriate Excel version. Here is the download link
  • Data mining add-in requires a connection to one of the following editions of SQL Analysis services (Enterprise, Business Intelligence or Standard)

SQL Data Mining Deployment:

After downloading the files from the link above, you need to run the .msi files, and go through the installation wizard.

Click “Next”

Setup 1

Accept the terms in the license agreement to proceed, click “Next”

Setup 2

Choose the feature to be installed as part of the add-in, click “Next”

Setup 3

Proceed with the installation, click “Install”

Setup 4

Once the installation is completed, click “Finish”

Setup 5


Now, open Excel, and a window related to SQL Data Mining add-in will pop up providing three choices as follows:

  1. Download an evaluation version of Microsoft SQL Server and setup Analysis services on my computer
  2. Use an existing instance of Microsoft SQL Server 2008 (or later) analysis services that I administer
  3. Connect to Microsoft SQL Server 2008 (or later) analysis services instance to which I have non-administrative access

SQL Data Mining Add-in for Office 2010

Click on option 2, you will be asked to run the Server Configuration Utility.

SQL Data Mining Add-in for Office 2010 2


Next post will take you through the Server Configuration Utility, which main purpose is to connect to the SQL Analysis Services. Meanwhile, you will find a new tab added to Microsoft excel “Data Mining”, including the following categories:

  • Data Preparation
  • Data Modeling
  • Accuracy and Validation
  • Model Usage

Data Mining Tab

Additionally, you can check the Excel sample data file (DMAddins_SampleData.xlsx) on the following path (C:\Program Files\Microsoft SQL Server 2012 DM Add-Ins\DMAddins_SampleData.xlsx). The excel file includes various cases which will help you understand the benefit of this add-in.


Best Regards,
Mahmoud M. AlSaadi

No comments:

Post a Comment