Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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

Total article views: 3705 | Views in the last 30 days: 157
 
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.

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

BLOG

Predictions for 2013

I’m on holiday, but I have a set of predictions for 2013 coming on Jan 1. This is a little...

Tags
algorithm    
data mining    
linear    
microsoft    
regression    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones