Forecasting Sales data with Excel mining - Step by Step Guide

  • Article

Mining is a service and can be coded. This means that it is enterprise ready. Mining can be done using

  • Excel
  • SSDT
  • PowerShell and
  • SSMS

Analysis Services service should be "ON" for mining to run

Using a combination of basic PowerPivot and Mining Plugins for Excel 2013, let us create a forecasting model.

  1. Open Excel 2013
  2. Click on PowerPivot Tab

 

  1. Click on Manage – Manage helps to import relevant data. A new window opens up
  2. Click on “From Database” and choose SQL Server

 

  1. Complete the data connection wizard
    1. Type in name of the server (Could be the same as name of your machine or localhost)
    2. Use Windows authentication or use SQL Server Authentication. Example: sa and pwd
    3. Connect to ContosoRetailDW and Test Connection
    4. Click Next to pick necessary tables. Choose the first radio option to select a set of tables
    5. Choose FactSales
    6. Select related tables (6 related dimension tables will be added)
    7. When you finish the wizard, data will be imported to PowerPivot i.e. memory
    8. Fact Sales has data for the year 2007, 2008 and 2009. Based on available memory choose a smaller dataset example: year 2009 only
    9. Click on Pivot Table and select flattened Pivot Table. A flattened Pivot table, removes sub-total

  1. Select Calendar Month on rows and SalesAmount on Columns
  2. A warning pops up asking you to create relationship between the tables
    1. Create relationship between Tables and columns of DimDate and FactSales as shown below

 

  1. Create Count of Sales Amount and Average Sales amount

    1. Drag Sales amount to columns again
    2. Select Value field settings from the drop down
    3. Choose Count
    4. Repeat process a to c above to create average

 

  1. Resultant Pivot table will be as below

 

  1. Select and paste as values (so that there are no formulae in the numbers)
  2. Go to design and set selected data area as a table

  1. Click on the data mining tab
  2. With all cells with data selected, choose forecast from the ribbon
    1. Data range will be automatically selected
    2. Choose all columns for input i.e we use 3 variables here – Sum of sales, Count of sales and average sales
    3. A new chart pops up, where you can increase or decrease the number of prediction steps
    4. You can also toggle using Abs between absolute values and percentages.
    5. With percentages all 3 lines are visible clearly, where as with absolute the scale is such that only sales will be seen.
      Count and Average when displayed on a chart with the same scale as sales amount will be almost invisible

 

    1. Click on document model to see parameters used

 

  1. Click on Trace to see queries used
  2. ART xp is used for short term forecasting and ARIMA is used for long term forecasting
  3. Forecasted values use cross prediction ie predict based on all columns available as variants
  4. 65000 such parameters can be used in a model

Forecasting is used in medical field to trace temperature, Blood pressure and forecast for the future. It is used in mutual funds as an analyzer before investments are made.