Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Data Mining Introduction Part 9: Microsoft Linear Regression

By Daniel Calbimonte,

This is Chapter 9 of my Data Mining articles. If you want to see the earlier chapters, you can read the previous articles in this series using the links below:

There is not too much documentation about the Linear Regression Algorithm. Maybe because it is too simple. This article will explain the algorithm and then we will create an easy sample to show how it works.

The linear regression is an algorithm used to predict one variable based in another one. For example, we can predict the gold prices over the time, the relationship between tobacco smoking and the mortality, the temperature and the number of people with flu.

The relationship follows a behavior similar than a line. That is why it is called linear regression.

We need some samples of data for this model and an input variable to estimate the dependent variable. Here you have an example:

Getting Started

In this sample, we are going to predict the price of a house based on the square meter size of the houses. Let say that we are selling houses and that we have a table with the meters and the price of the house.

The  table contains the following information:

Meters are the square meters (m2) and the price contains the price according to m2. For example, if the house has 35 m2, the price would be 3000 USD, if the house has 40 m2 the price will be 3500 USD. In order to create the table with data, you can use this script:

USE [AdventureWorksDW2012]
GO
/****** Object:  Table [dbo].[HousePrices]    Script Date: 5/2/2014
5:09:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[HousePrices](
        [id] [smallint] NOT NULL,
        [meters] [smallint] NULL,
        [price] [smallint] NULL,
 CONSTRAINT [PK_HousePrices] PRIMARY KEY CLUSTERED
(
        [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
INSERT [dbo].[HousePrices] ([id], [meters], [price]) VALUES (1, 35, 3000)
INSERT [dbo].[HousePrices] ([id], [meters], [price]) VALUES (2, 40, 3500)
INSERT [dbo].[HousePrices] ([id], [meters], [price]) VALUES (3, 50, 4000)
INSERT [dbo].[HousePrices] ([id], [meters], [price]) VALUES (4, 90, 6500)
INSERT [dbo].[HousePrices] ([id], [meters], [price]) VALUES (5, 100, 7000)
INSERT [dbo].[HousePrices] ([id], [meters], [price]) VALUES (6, 120, 7200)
INSERT [dbo].[HousePrices] ([id], [meters], [price]) VALUES (7, 130, 8000)

Now, we want to predict the price if we have the number of square meters using the table created. To do this, we are going to add the table to the AdventureWorks model used in earlier chapters.

1. To add the table, go to the solution Explorer and select the Adventureworks DataSource View. In the design pane right click and select the Add/Remove Tables and add the HousePrices table and save the project.

2. In the Solution Explorer, in the mining structure right click and select New Mining Structure. You will have the Data Mining Wizard.  Press next.

3. In the Create the Data Mining Structure, select the Microsoft Linear Regression.

4. In the select Data Source View, select the Adventure Works Data Source.

5. In the Specify Table Types, select the HousePrices. This is the table created with the prices per square meter.

6. The key is the id used, the meters are going to be used as the input columns. Based on the square meters we are going to predict the price. The predicted column will be the price.

7. We will live the Content type with the default values.

8. For the Create Testing Set we will use the default values.

9. Finally, you can specify any name for the Structure and Mining Model. Press finish and compile the model like we did in ealier chapters.

10. Now we have the model created in step 9 

11. In the case table select the HousePrice Table and press OK.

12. Make sure you are in Singleton Query mode.

13. Now use a Prediction Function to predict the price. Drag and drop the Price as an argument of the function.

14. Now we are going to ask the model the price of 43 m2.

15. Go to the result.

16. The price of 43 m2 is 3531 USD.

17. What is the price of a 77 m house?

18. According to the model, 5680 USD.

Summary

In this lesson, we used the linear regression algorithm to estimate values according to a variable. We can estimate different statistics, prices using this algorithm. We just need some initial values and the algorithm will detect and estimate our results according to our input. In our sample we estimate the price of a house based on a the m2.

References

http://technet.microsoft.com/en-us/library/ms174824.aspx

http://technet.microsoft.com/en-us/library/cc645871.aspx

 

This article is part of the series A Data Mining Introduction:

Total article views: 4589 | Views in the last 30 days: 9
 
Related Articles
BLOG

Predictions for 2014

I love technology predictions because they are usually so wildly inaccurate that at the end of the y...

FORUM

Pricing EDI Feed Processing System

Creating a system that will process EDI 811 (Invoices) feeds and am curious on pricing options.

BLOG

House Price Predictions @ PASS 2015

The PASS speakers were announced a quite a number of weeks ago now for PASS 2015 in October – and I ...

ARTICLE

Predictable Index Fragmentation

This technical article provides an overview of how to produce specific levels of index fragmentation...

ARTICLE

A Stock Price Correlation Matrix

Building a Stock Price Correlation Matrix Using TVPs, a CROSS JOIN and the PIVOT Function

Tags
algorithm    
data mining    
linear    
microsoft    
regression    
 
Contribute