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
https://www.microsoft.com/en-in/download/details.aspx?id=7294
Install for SQL Server 2012
https://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
Excel Data Connection
> Enter the SQL Server name
> Choose Windows or a SQL user credentials
> Click “Next”
Select the appropriate table or view
> Highlight the table VTimeSeries in this case
> Click “Next”
Name your data connection
> The data connection can be used for other users
> Click “Finish”
Import your data
> Choose “Table”
> Click “OK”
Sort Your Time Column
> For Excel to forecast, you must have the time column sorted oldest to newest
Forecast the Data
> Click the “Data Mining” tab
> Click “Forecast”
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”
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
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:
By Turnkey Technologies, Inc. – Microsoft Gold Certified Dynamics ERP and Dynamics CRM Partner of Missouri, Illinois, Oklahoma and North Texas.
Great post many thanks for posting