ERP Software Logo1

Microsoft Dynamics vendors provide comparisons and opinions to professionals in the ERP/Accounting software selection process

 
 

Turnkey Technologies, Inc.

Put Your Dynamics GP Data to Work by Data Mining with Excel


Email | Print

Data Mining is the root task for deriving business intelligence from your warehouse of data. It defines the process of uncovering relationships, patterns and predictions from raw data using algorithms and other methods to display information in graphical and other easy-to-understand formats. There are multiple ways to do this, but data mining with Excel provides some of the most easily understood and readily available methods.

To start off your data mining with Excel, there are free data mining add-ins available for Excel 2007 or later:

Install for SQL Server 2008

http://www.microsoft.com/en-in/download/details.aspx?id=7294

Install for SQL Server 2012

http://www.microsoft.com/en-us/download/details.aspx?id=29061

 

Let’s take a look at the basics of data mining in Excel. There are three ways to analyze your data:

Classifying (decision trees)

Decision trees identify historically relevant, key data to help predict for the future. They do this by creating conditional “splits” of data, called nodes. Much like a flow chart, decision trees work off of if-then statements that ultimately lead to actionable insight.

Clustering (clusters)

Clustering identifies unrealized relationships between your data to help uncover trends. It works by identifying relationships in a dataset and visually representing them via a scatter plot connected by lines, much like a network diagram.

Forecasting (time series)

The time series feature takes trends in data and builds a forecast based on those trends and variables. Forecasting helps companies prepare labor and inventory for seasonal demand changes, or create proactive strategies to improve productivity during less productive quarters. For the purposes of this blog, we will look at how to set up your foundation for Forecasting in Excel.

Forecasting Data in Excel 2013

Time Series Data Source

> Click the Data tab

> Select “From Other Sources”

> Select from SQL Server

Time Series Data Source in Excel 2013 (1)

Excel Data Connection

> Enter the SQL Server name

> Choose Windows or a SQL user credentials

> Click “Next”

Excel Data Connection (2)

Select the appropriate table or view

> Highlight the table VTimeSeries in this case

> Click “Next”

Select the appropriate table or view (3)

Name your data connection

> The data connection can be used for other users

> Click “Finish”

Name your Data Connection (4)

Import your data

> Choose “Table”

> Click “OK”

Import Your Data (5)

Sort Your Time Column

> For Excel to forecast, you must have the time column sorted oldest to newest

Sort your Time Column (6)

Forecast the Data

> Click the “Data Mining” tab

> Click “Forecast”

Forecast the Data (7)

Excel Forecast Wizard will pop up

> Click “Next”

Forecast Wizard: Select Data Source

> Choose the table you added

> You can also select a data range or an external data source

> Click “Next”

Forecast Source Data (9)

Choose the Columns to Forecast

> Select the Time Stamp

> This is the time value to forecast on. Best time stamp formula is YearPeriod

> This must include 1 time column and 1 variable. I.e. Timeindex and Quantity

Choose the Column to Forecast (10)

Finish your Forecast Model

> Click “Finish”

 

Special Notes for Using Microsoft Excel

  • Make sure the dataset is polished beforehand.
  • Reduce data draw on the network by aggregating data as much as possible before selecting it in Excel.
  • Make sure datasets have the correct security steps completed.
  • If available, practice against a development SQL Server first.

 

If you'd like to learn more, feel free to reach out or give us a call:

 

Contact Turnkey Technologies Inc. (button)

 

By Turnkey Technologies, Inc. – Microsoft Gold Certified Dynamics ERP and Dynamics CRM Partner of Missouri, Illinois, Oklahoma and North Texas.

One Response to “Put Your Dynamics GP Data to Work by Data Mining with Excel”

  1. Great post many thanks for posting