Predictive Analytics: Decision Tree, Random Forest and SVM

I’ll be taking statistical learning class next semester. So I just wanted to get a head start on some of the algorithms.  I’m currently enrolled in an online class called Predictive Analytics offered through coursera and this is pretty much my “partial” answers to one of the assignments. I hope to use this code as a template for future assignments. The code uses decision tree, random forest and support vector machine on flow cytometer data to compare accuracy of three models.

A flow cytometer delivers a flow of particles through capilliary. By shining lasers of different wavelengths and measuring the absorption and refraction patterns, you can determine how large the particle is and some information about its color and other properties, allowing you to detect it.

While there are a number of challenging analytics tasks associated with this data, a central task is classification of particles. Based on the optical measurements of the particle, it can be identified as one of several populations.

Looks like CRYPTO is missing using Decision Tree! Let’s look at the accuracy of Decision Tree, Random Forest and SVM .

The accuracy using DT, RF and SVM were .854, .923, and  .921 respectively. Also, the most important variables in the data set, as suggested by Gini impurity measure were,  pe and chl_small.  The higher the index, the more important is the variable! Upon deleting one of the observations the accuracy of the svm model went up by 0.05. Here’s the confusion matrix from three models.

All the three models suggest Ultra being misclassified in higher proportion as Pico and Nano. Also, Synecho is misclassified as pico.

SQL Commands (Part – 4)

SQL Server Inbuilt Functions

String Concatenation: See inner join in the last post… Linking strings together

Select PC.Name as ProductCategory,
PS.Name as ProductSubCategory,
PRD.Name + ‘ ‘ + ‘||’ + ‘ ‘ +  ProductNumber as Combined, ListPrice, StandardCost
FROM Production.Product as PRD
INNER JOIN Production.ProductSubcategory PS
ON PRD.ProductSubcategoryID=PS.ProductSubcategoryID
INNER JOIN Production.ProductCategory PC
ON PC.ProductCategoryID = PS.ProductCategoryID
ORDER BY PC.NAME, PS.NAME

However, if you try to use + for ListPrice and StandarCost it will return a sum. To fix this use CAST function. See below! Also, combining an integer and NULL value will result in NULL.

CASE Statements:

Select PC.Name as ProductCategory,
PS.Name as ProductSubCategory,
 PRD.Name + ‘ ‘ + ‘||’ + ‘ ‘ +  ProductNumber as Combined, ListPrice, StandardCost,
cast(ListPrice as varchar(10))+ ”+ cast(StandardCost as varchar(10)),

CASE WHEN ListPrice>100 THEN PRD.Name+ ‘ ‘ + ‘||’ + ‘ ‘ + ProductNumber
WHEN ListPrice<100 THEN ‘Less’
END AS Combined2

FROM Production.Product as PRD
INNER JOIN Production.ProductSubcategory PS
ON PRD.ProductSubcategoryID=PS.ProductSubcategoryID
INNER JOIN Production.ProductCategory PC
ON PC.ProductCategoryID = PS.ProductCategoryID
ORDER BY PC.NAME, PS.NAME

Case statement can also accompanied by where and  else statement!

Select PC.Name as ProductCategory, PS.Name as ProductSubCategory,
PRD.Name + ‘ ‘ + ‘||’ + ‘ ‘ +  ProductNumber as Combined, ListPrice, StandardCost,

cast(ListPrice as varchar(10))+ ”+ cast(StandardCost as varchar(10)) as xx,

CASE WHEN ListPrice>100 THEN PRD.Name+ ‘ ‘ + ‘||‘ + ‘ ‘ + ProductNumber
ELSE PRD.Name
END AS Combined2

FROM Production.Product as PRD
INNER JOIN Production.ProductSubcategory PS
ON PRD.ProductSubcategoryID=PS.ProductSubcategoryID
INNER JOIN Production.ProductCategory PC
ON PC.ProductCategoryID = PS.ProductCategoryID
CASE WHEN PS.Name In (‘Helmets’,’Bike Racks’, ‘Bike Stands’) then ‘Accessories‘ end = PC.Name
ORDER BY PC.NAME, PS.NAME

You might be wondering what is = PC.Name doing? Just ask yourself how does WHERE clause work?

Select PC.Name as ProductCategory, PS.Name as ProductSubCategory,
 PRD.Name + ‘ ‘ + ‘||’ + ‘ ‘ +  ProductNumber as Combined, ListPrice, StandardCost,
cast(ListPrice as varchar(10))+ ”+ cast(StandardCost as varchar(10)) as xx,

CASE WHEN PC.Name= ‘Components’ and PS.Name = ‘Brakes‘ then ‘Label1‘
ELSE
      CASE WHEN PC.Name LIKE ‘COMPONENTS’ and PS.Name not like ‘ Brakes’ then ‘Label2‘
      ELSE ‘Label3‘ END
END AS NewColumn

FROM Production.Product as PRD
INNER JOIN Production.ProductSubcategory PS
ON PRD.ProductSubcategoryID=PS.ProductSubcategoryID
INNER JOIN Production.ProductCategory PC
ON PC.ProductCategoryID = PS.ProductCategoryID
ORDER BY PC.NAME, PS.NAME

 

Custom Sorting

In the output above if you want  to order your items by Clothing, Accessories, Bikes and only want three of those products how can you accomplish it?

ORDER BY CASE WHEN PC.Name = ‘Clothing‘ THEN 1
WHEN PC.Name = ‘Accessories’ THEN 2
WHEN PC.Name = ‘Bikes’ THEN 3 END

New Table: How can you put an ouput of select statement directly in a table?

SELECT Name, rowguid, ‘test‘ as TEST into mytest
from AdventureWorks2008R2.Production.ProductModel

The keyword here is into. Notice I will have third column named TEST with values test. If I don’t give column a name then this method does not work.

Convert: Converts one data type to other like cast but also allows flexibility for style.

SELECT ModifiedDate,
CAST(ModifiedDate as DATE) as DateOnly,
CAST(CAST(ModifiedDate as DATE) as Datetime) as LostTime,
CAST(ModifiedDate as time) as TimeOnly,
CAST(ModifiedDate as varchar(20)) as VarcharDate,
CONVERT(VARCHAR(30),ModifiedDate,101) as DDMMYYYY
from Production.Product

In the covert function 101 yields date in DD-MM-YYYY format, there are other options as well. Check out
http://technet.microsoft.com/en-us/library/ms174450(v=sql.110).aspx . By the way, if we have our dates in varchar format, it allows us to use a number of string functions.

Commonly Used Aggregate Functions:

SELECT max(ReorderPoint) as MAX,
MIN(ReorderPoint)as MIN,
AVG(ReorderPoint)AS Average,
sum(ReorderPoint)AS Sum,
count(ReorderPoint) as TotalCount
from Production.Product

In order to use aggregate functions you will need a group by clause.

Select PC.Name as ProductCategory,PS.Name, SUM(StandardCost)as TotalCost
FROM Production.Product as PRD
INNER JOIN Production.ProductSubcategory PS
ON PRD.ProductSubcategoryID=PS.ProductSubcategoryID
INNER JOIN Production.ProductCategory PC
ON PC.ProductCategoryID = PS.ProductCategoryID
GROUP By PC.Name, PS.Name
ORDER By 1

I am interested in total count of product subcategories greater than 2. How would you find total count of product sub-category for Bib-Shorts and Bottle and Cages only?

Select PS.Name as ProductSubCategory,
count(Prd.Name) as TotalNumber
FROM Production.Product as PRD
INNER JOIN Production.ProductSubcategory PS
ON PRD.ProductSubcategoryID=PS.ProductSubcategoryID
INNER JOIN Production.ProductCategory PC
ON PC.ProductCategoryID = PS.ProductCategoryID
WHERE PS.Name IN (‘Bib-Shorts’, ‘Bottles and Cages’)
GROUP by PS.Name
HAVING COUNT(prd.name)>=2
ORDER BY 1

Remember Where comes before Group By and Having comes after. How do you find duplicates in table?
SELECT EmployeeID, count(*) FROM tablename GROUP BY EmployeeID HAVING count(*)>1

COMMON DATE-TIME FUNCTIONS:

It is a great idea to understand what input a  function takes in and what output it returns. Why? Sometimes you may want to concatenate three things by using CAST statements if the output are integers. What if there’s another function that simply outputs what you need in varchar. Here’s how you can quickly see what a function takes as an input and what it returns. Parameters are user driven input.

Examples:

SELECT GETDATE(), GETUTCDATE(), SYSDATETIME(), CURRENT_TIMESTAMP

SELECT DAY(GETDATE()),MONTH(getdate()),YEAR(GETDATE()),
                 DATENAME(MONTH,GETDATE()), DATENAME(WEEKDAY,GETDATE()),
                 DATENAME(W,GETDATE()), DATEPART(WEEKDAY,GETDATE())

SELECT DATEDIFF(HOUR,GETDATE(),GETUTCDATE()), DATEADD(DAY,5,GETDATE()),
                 DATEADD(WEEK,-10,GETDATE()), ISDATE(CURRENT_TIMESTAMP),
                 ISDATE(‘02/22/0D3‘)

Some questions you might consider asking is.

1. Why use UTC?
   It takes care of differences in timezones  if you are a part of global company.
2. What’s the difference between YEAR and YEAR of DateTime function? The output type!
3. If you are importing data from Mysql to SQL SERVER and if it(SQL SERVER) doesn’t incorporate that date range, how can you handle such case. In other words, how can you replace a junk stuff like 01/22/0001 to 01/01/9999 ?
   SELECT CASE WHEN ISDATE(Mydate)=0 then ‘01/22/9999‘ ELSE Mydate
4. If you want to create a table for users in JAPAN and you are in USA (EasternTime)? How can you achieve this?  USA time to UTC to JAPAN time.

STRING FUNCTIONS:

String functions perform an operation on a string input value and return a string or numeric value. Some commonly used string functions along with their examples are as follows:
SELECT TOP 5
[Name]as Name,
UPPER(Name) as UpperName,
LEN(Name) as A,
LTRIM(Name) as B,
REPLACE(Name,’ ‘,’**’) as C,
STUFF(Name,1,4,’^%^$’) as D,
SUBSTRING(CAST (Name AS Varchar(12)),2,LEN(Name)) as E, /**You don’t have to use                                                                                    CAST for SQL Server 2008 and Beyond**/
CHARINDEX(‘a’,Name ) as F ,
CHARINDEX(‘a’,Name,3 ) as G ,
REPLICATE(‘C’,3) as H, /**Adds specific character**/
[ProductNumber] as ProductNumber,
REPLICATE(’00’,20-LEN(Name))+ Name,
LEFT(Name,3)

FROM [AdventureWorks2008R2].[Production].[Product]

/**IF YOU EVER GET AN ERROR THAT SAYS STRING OR BINARY DATA WILL BE TRUNCATED ***IT HAS SOMETHING TO DO WITH THE LENGTH OF YOUR DESTINATION Column!*****USE Alter Table to alter the length***/

Note:  STUFF function is very similar to REPLACE but a start location and an end location is                                 specified for replacement.

RANKING FUNCTIONS:

Four commonly used ranking functions are Rank , Dense_Rank, Row_Number and Ntile. They all             have the same syntax.

SELECT FrenchProductSubcategoryName,
ProductCategoryKey,
SpanishProductSubcategoryName,
RANK() Over( Order by [ProductCategoryKey])
  FROM [AdventureWorksDW2008R2].[dbo].[DimProductSubcategory]

SELECT FrenchProductSubcategoryName,
ProductCategoryKey,
SpanishProductSubcategoryName,
RANK() Over( Order by [ProductCategoryKey], [SpanishProductSubcategoryName])
 FROM [AdventureWorksDW2008R2].[dbo].[DimProductSubcategory]

SELECT FrenchProductSubcategoryName,
ProductCategoryKey,SpanishProductSubcategoryName,
Dense_RANK() Over( Order by [ProductCategoryKey])
FROM [AdventureWorksDW2008R2].[dbo].[DimProductSubcategory]

         Select * From
(
         SELECT
FrenchProductSubcategoryName,
ProductCategoryKey,
         Row_number() Over( Partition by ProductCategoryKey
         Order by [ProductCategoryKey]) as random
FROM [AdventureWorksDW2008R2].[dbo].[DimProductSubcategory]
) a
         Where a.random = 1
         /**Row_Number can identify duplicates **/

 

Reading Contents of Zip Files from SAS

If you read by last post, I showed how one can download several files from a URL address. In this post, I will use macros to read the contents of the zip files downloaded earlier.

In order to do so, I made use of dopen function that opens a directory and returns a directory identifier value. If the directory contains no file, the program stops. A do loop is then used to loop through the directory identifier values starting from 1 and then it is passed to dread function that returns the name of the directory using an output statement. After all the iterations are over dclose function is used to close the directory. Proc print was used to view the name of files inside. This entire process was enclosed in a macro called insidezip and %DO loop was used to loop through each of the zip files and all their contents were read.

In case you are wondering where the files are coming from, just look at the six %Let Var statements in my earlier post! I am assigning the location of those files as:

%Let VAR2 = “C:\Users\adhikas\Desktop\GroupProject\IndividualProject\H1B_efile_FY02_text.zip” ;
..                      ..                         ..                   ..                     ..
..                      ..                         ..                   ..                     ..
%Let VAR_file = 7;
and so on..

SAS Macro to download files from url addresses

In this post, I’ll show how you can use SAS Macro to download a bunch of files with similar names into a specified folder. Let’s begin!

At first a single proc http statement was used to download 2002 zip data file using get method in a specified directory. Using a filename statement with zip access method I was able to access the zip file that was downloaded. This process was to be repeated five additional times for accessing data sets from year 2003-2007 for which I wrote a macro called download_zip_files. The macro uses the data to determine the path and logic of execution and makes use of a simple dynamic technique called the %DO loop. In addition, several %LET statements were used to indicate the url addresses of each file and where they were to be stored after downloading. So for first iteration, the output directory along with file name and extension was specified in out option of proc http, using double ampersand to access the value from %LET statements, to where the corresponding zip file was downloaded. This process was repeated for five times. 

I’m sure there’s another way of achieving the same thing by minimizing %LET statements. If you know how to do so feel free to post on the comment section below.

Building Predictive Models (GLM vs. PCA)

This post aims to build predictive models using Generalized Linear Model (GLM) and Principal Component Analysis (PCA). In SLR, the error terms are assumed to be normally distributed, therefore the expected value of response follows the same distribution as error terms. What if the response is binary or if it follows some other arbitrary distribution? This is where GLM kicks in.

PCA is a statistical procedure that aims to reduce correlated predictors to linearly uncorrelated ones called principal components. The first principal component is the linear combination of x-variables that accounts for maximum possible variability in the data. There’s a lot more into it, but that’s the basic idea. Let’s focus on building two models using these two methods.

At first a data set is split into two parts – a training and a test set. You build your model off the training set and evaluate your test set. Follow along the code below to understand what’s going on.

I have used table as a list. However things like accuracy, sensitivity, specificity etc between two models can also be compared.

Note: A part of the code above comes from one of the question in PML class offered in coursera by JHU.

Unzipping and Renaming files using X command in SAS

At times, writing macros to unzip files in SAS can be be tedious and messy. One way around this is by using X command. It allows users to submit windows command or enter operating environment mode without ending the SAS session.

In order to make use of it, a file de-compressor was downloaded. Then I specified the location of 7za.exe, zip files and where to extract them by using X command in SAS. All the zip files were extracted. The code is shown below. 

It is also worth noting of other option such as SASZIPM.  I had initially used a data step to read the file from filename url and made use of SASZIPM for unzipping files but later came across a paper which emphasized not to use it while dealing with huge .csv and .txt files because it tends to employ all computer memory. If I were to go this route I had to decompress the files and compress it with software like winzip, which is more compatible with SASZIPM.

X command can also be used to to rename files. Here’s how.

Reading HTML file from a webpage into SAS

Few weeks back I was working on one of my group projects for my programming class. We had two data sets of interest but merging them was difficult. One had state abbreviations and the other one had state names. We needed a third one with both the field names so that they could be merged. This can be done easily if you have a third data set in a text or csv file but it’s boring that way. How about reading that info from a webpage? After searching quite a bit in google I found a third data set in ancestery.com  (http://www.rootsweb.ancestry.com/~obituary/abbrev1.html). Here’s the code that I used to read that info into SAS. 

At first all the info from the website is brought into a single column and state names and their abbreviations are parsed using substr function. This file is then written in the location specified above so that it could be brought into SAS to carry out an inner join. Remember to carry out an inner join, using proc sql, so that unwanted state names are left out. Here’s what the first ten observations looks like.

SQL Commands (Part-3)

JOINS:

Inner Join: Gives you common result from two or more tables. Intersecting part of a Venn Diagram.

SELECT * from Person.Address — Count number of records

SELECT * from Person.StateProvince — Count number of records

Notice: The result set of first SELECT statement only has StateProvinceID but not StateName.

Select AddressLine1, City, PostalCode, B.Name as StateName from Person.Address as A
Inner Join Person.StateProvince as B
ON A.StateProvinceID = B.StateProvinceID  — Count number of records and Compare

Few rows are eliminated because not all records from Person.Address table has StateProvinceID.

Three Joins!

Select PC.Name as ProductCategory,
PS.Name as ProductSubCategory,
PRD.Name as ProductName, ProductNumber
FROM Production.Product as PRD
INNER JOIN Production.ProductSubcategory PS
ON PRD.ProductSubcategoryID=PS.ProductSubcategoryID
INNER JOIN Production.ProductCategory PC
ON PC.ProductCategoryID = PS.ProductCategoryID
ORDER BY PC.NAME, PS.NAME

Left Join: All matched records from Person.[Address] table + Person.[StateProvince]. No match will yield NULLS.

Select AddressLine1, City, PostalCode, B.Name as StateName from Person.Address as A
LEFT Join Person.StateProvince as B
ON A.StateProvinceID = B.StateProvinceID

Right Join: All matched records from Person.[StateProvince] table +Person.[Address] . No match will yield NULLS.

Select AddressLine1, City, PostalCode, B.Name as StateName from Person.Address as A
RIGHT Join Person.StateProvince as B
ON A.StateProvinceID = B.StateProvinceID

Full Outer Join: Will contain all records from both tables. Missing matches will yield NULLS

Select AddressLine1, City, PostalCode, B.Name as StateName from Person.Address as A
FULL OUTER Join Person.StateProvince as B
ON A.StateProvinceID = B.StateProvinceID

If you are retrieving two additional columns – A.StateProvinceID and B.StateProvienceID how will results differ in each join cases? If you can answer this, you are good for joins! Also, Full Join is not available in MySql.

Tip: If you want all the columns from A, you can use SELECT A.* from Person.[Address]. If you want all columns from A and B then A.*,B.* will do the trick. The following statements are same.

SELECT * from Person.Address

Select A.* from Person.Address as A
LEFT Join Person.StateProvince as B
ON A.StateProvinceID = B.StateProvinceID

Self Join: When you join the same table with itself.

SELECT *from Person.StateProvince A
INNER JOIN Person.StateProvince B
ON A.StateProvinceID=B.StateProvinceID

If you want to identify who the manger is for certain employee then you can use Self Join, provided everything is in the same table. Doing this prevents duplicate records.

Cross Join: Also called Cartesian product (each row from first table is combined with each row of second table)

Select City, PostalCode, B.Name as StateName, B.StateProvinceID from
Person.[Address] as A
CROSS JOIN Person.[StateProvince] as B

If you use Where clause in cross join, it acts like an inner join. Also note that our output will have more than almost 2 million records! Each record from Address table was cross joined with each record of StateProvince table.

SQL Commands (Part-2)

Before creating a table it is crucial to understand different properties of columns. After all table is collection of different columns. In order to create a column you need a column name, corresponding data type and whether it takes NULL or not. Make sure to be very careful while naming columns. For example if you name your column Emp ID and run a SELECT statement on it, then SQL server interprets ID as an alias of Emp. To Prevent this you could use square brackets [Emp Id]. However, it’s good practice to avoid spaces while naming columns.

Data Types:

1. Numbers  – int, bigint, small int, tiny int
2. Numbers with Decimal – numeric, decimal
3. Strings – char, varchar, nchar, text, varchar
4. Date/ Time – date, datetime, datetimeoffset, smalldatetime, datetime2
5. Money – money
6. Numbers with Floats – float, real

Appropriately defining data types can boost performance. For example using smallint for DepartmentID is lot better than using bigint or int. Departments are not going to grow a whole lot. Therefore, rangebetween 0-255 will do the trick.

More Details: http://msdn.microsoft.com/en-us/library/ms187752.aspx

SELECT *FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE=’decimal’

Change decimal to smallint, real etc to see what happens!

In functions, there are specific string functions which can be used only on specific data types. Char reserves the space while varchar does not. In other words, char is greedy! nvarchar also reserves the space like char but supports uni-code data.

char(500) – Uses 500 characters no matter what.
varchar(500) – Essentially takes up to however much space you need. Unused spaces are discarded. Non uni-code. Supports real English data. If you have Hebrew characters coming in than go with nvarchar. You can do varchar(max) as well.

So what’s the difference between char and nchar? You got it! The cool thing about text data type is, it supports line breaks. Date data type only stores date. Datetime store both date and time together. You can find more information on data types here. Things like binary strings are not used in Data Warehouse as it is difficult to retrieve them using queries.

Identity Column
Also known as a surrogate key or incremental column. For example you may not have a real employee id in your company and would like to create employee ids in database level. This serves primarily for joining tables. Once created you can specify start and incremental values. There’s no need to input any data. It automatically generate new ids. The way you will do so is simply this way –  [EmpId] [int] identity(1,1). There’s no need to specify nulls.

Create table [dbo].[Employee](
[EmpID] [int] identity(1,1),
[Name] varchar(20),
[Salary] [int]

)

Insert into [dbo].[Employee](EmpID, Name, Salary)
values (1,’Laura’, 50000)

This will throw in the following error. To prevent all this, simply don’t use EmpID and 1. 

Insert into [dbo].[Employee]( Name, Salary)
values (‘Laura’, 50000), (‘Mary’,80000), (‘Joe’,100000)

Select *from [dbo].[Employee]

Also, take a look at first snapshot. How do you know  EmpID has auto incremental going on? The columns don’t show golden colored key or anything for indication. To find out just right lick the EmpID column and click properties. Alternatively, if you use scripts to create table or insert values you can notice the identity column nature. If you delete old data and insert values again than it will start from 4,5,6 for  EmpID. If you truncate the table the identity starts from 1 and increases by 1. Delete does not reset the identity. However, in real life scenario, you can’t simply truncate.

What if you’d like to change the starting number in your identity column?

DBCC CHECKIDENT (‘dbo.employee’, Reseed,10)
Insert into [dbo].[Employee](Name, Salary) values (‘L’, 50000), (‘M’,80000), (‘J’,10000)
SELECT *from [dbo].[Employee]

Notice it starts from 11, because there’s already an existing row!

CREATING TABLES

It’s really easy to do all this things. SQL Server automatically generates scripts for doing so. You can right click a table, script table as and use create button to come up with the structure for creating tables.

Create table [dbo].[Employee](
[EmpID] [smallint] identity(1,1),
[Name] varchar(20) not null
[Salary] [int])

ALTERING TABLES

ALTER table [Employee]
ADD DeptID int NULL

ALTER table [Employee]
ALTER Column DeptID tinyint

ALTER table [Employee]
DROP Column DeptID

Remember: You can’t change the order of the columns! If you add a column and run a SELECT statement you will see the new column has NULL values. How can you add a column without a null? Follow along.

ALTER table [Employee]
ADD Presence varchar(15) NUll

Update [Employee]
SET Presence = ‘TRUE‘

ALTER table [Employee]
ALTER COLUMN Presence varchar(15) not null

Now compare DeptID with Presence. See the difference? Did we achieve our goal? We did. Can we add different values for each row? Yes we can be using conditions.

Update [Employee]
SET Presence = ‘L names’
Where Name like ‘L%’

 

Subqueries: It is simply a nested query i.e query within a query.

Non Correlated Query: Query Independent of Main Query.

SELECT * FROM dbo.Employee
WHERE EmpID in (
SELECT EmpID from Employee Where Presence = ‘L names’
)

Correlated Subquery: If queries are interdependent for output. This example below comes directly from technet.microsoft.com as it’s much more easier to understand than the one I came up with.

SELECT DISTINCT c.LastName, c.FirstName, e.BusinessEntityID FROM Person.Person AS c
JOIN HumanResources.Employee AS e ON e.BusinessEntityID = c.BusinessEntityID
WHERE 5000.00 IN
(SELECT Bonus FROM Sales.SalesPerson sp WHERE e.BusinessEntityID = sp.BusinessEntityID) ;

This query retrieves one instance of each employee’s first and last name for which the bonus in the SalesPerson table is 5000 and for which the employee identification numbers match in the Employee and SalesPerson tables. The first SELECT STATEMENT results in the table below:

Overall,

SQL commands (Part 1)

Schema

What is Schema?  Schema is partition of a table. In Dbo.employee (table name is employee) and DBO is schema. What is schema in -HumanResource.Dept and HumanResource.Employee? HumanResource.HumanResource schema contains related tables. If schema is not specified then, dbo is set by default. In other words,  Select *from dbo.a  is same as Select *from a . However this is not true for HumanResource schema.

It is always a good idea to create schema first. You need to right click somewhere and create new schema. Find it! It needs to be created manually. Right click security tab inside Test database. You should be able to do after this.

SQL commands

Click on ‘New Query’ and Select Right database ‘Adventureworks2008R2‘ instead of Test as shown below.

In the window, type in commands as shown below and understand the output. If this is too advance make sure to go through tutorials available at w3schools. Here we go.

SELECT *FROM HumanResources.Shift

Let’s quickly describe following terms:

Keys	Description
Primary Key	Won’t allow duplicates and uniquely identifies a row. No nulls. Creates Clustered Index.
Composite Key	Combinations of two or more columns in order to identify a row uniquely.
Foreign Key	Points points toward Primary Key in another table. Grey in color.
Unique Key	Similar to Primary Key but allows one null value. Prevents duplicate values in column. Creates non clustered index.

There are other keys like candidate, alternate etc. Refer to the diagram below.

Keep in Mind: In  SSIS, you need to load parent table first before loading the child table. Child table is dependent on parent table. A lot of times remembering a fact like this will do good.

SELECT STATEMENTS

SELECT TOP 100 * FROM Sales.SalesPerson
SELECT PostalCode FROM Person.[Address]
SELECT Distinct PostalCode FROM Person.[Address]
SELECT Top 100* FROM Person.[Address]Where StateProvinceID=79
SELECT *FROM Person.StateProvince ORDER BY Name DESC
SELECT Top 100* FROM Person.[Address]Where StateProvinceID=79 AND AddressLine2 IS NOT NULL

SELECT Top 100* FROM Person.[Address]Where StateProvinceID=81 Or AddressLine2 IS NOT NULL ORDER BY City DESC

SELECT * FROM Person.[Address]Where StateProvinceID LIKE ‘%9’ or City like ‘%bo%’

SELECT * FROM Person.[Address] Where City LIKE ‘%Lake%’
UNION All
Select * FROM Person.[Address] Where City LIKE ‘%CAl%’

Remember: Any number of Union All is possible.

SELECT AddressID FROM Person.[Address] Where City LIKE ‘%Lake%’
UNION
SELECT AddressID FROM Person.[Address] Where City LIKE ‘%CAl%’

Remember: Union sorts the data whereas Union All does not. Union is costly operation! UNION ALL REQUIRES same number of columns AND COLUMN names should match.

SELECT * FROM Person.[AddressType]

EXCEPT
Select * FROM Person.[AddressType] Where [AddressTypeID]=3

SELECT * FROM Person.[AddressType]
INTERSECT
Select * FROM Person.[AddressType] Where [AddressTypeID]=3

INSERT STATEMENTS

Run this in sequence.

CREATE TABLE [Person].[CoolPeople](
[ContactTypeID] [int] NOT NULL,
[Name] [dbo].[Name])

SELECT * FROM Person.CoolPeople
INSERT INTO Person.CoolPeople (ContactTypeID,Name)VALUES (‘1′,’Sam’)

SELECT * FROM Person.CoolPeople

INSERT INTO Person.CoolPeople SELECT * FROM Person.CoolPeople
SELECT * FROM Person.CoolPeople

Notice: Two ways of using INSERT statements.

DELETE, DROP, TRUNCATE STATEMENTS:

DELETE [Person].[CoolPeople]

INSERT INTO Person.[CoolPeople] (ContactTypeID,Name)
VALUES (1,’Sam’),(2,’Jack’),(2, ‘Flora’)

DELETE [Person].[CoolPeople]Where ContactTypeID =2

Truncate table [Person].[CoolPeople]
DROP table [Person].[CoolPeople]

Differences: Truncate  removes all rows from table and does not use ‘where’ clause. It acts on object level and we cannot do a rollback for truncate. It is faster than delete command. Delete on the other hand simply deletes the rows specified. Drop removes the entire table from database. Operations can’t be rolled back.

Database Basics

Run SQL Server Management Studio. Select Database Engine as Server Type, . for Server Name  and Windows Authentication for Authentication type. What you see after you connect is server.  Do not create tables, views, objects (triggers, functions) etc in system databases. SQL Sever does not guarantee that tables, objects and views will be updated as you upgrade the version later on.

Creating Database

Right click on Databases click New Database. Name it Test. You will see two files.. Hit Ok.
1. Test
2. Test_log

The idea here is that you want to populate your Test database with different things like tables and views. But before we do that let’s look into two things. Let’s find out the location of Test & Test_Log and differentiate between an .mdf and .ldf file. Just go to Program Files and Click on Microsoft Sql Server then MSSQLL10_50.MSSQLSERVER. The snapshot shows the location of the database we just created.

What is the difference between .mdf and.ldf file?
MDF file, known as the primary database file, contains the schema and data. A LDF file contains the logs. A database may also use secondary database file, which normally uses a .ndf extension.

Backing Up and Restoring Database

Right click your database Test. Click on Tasks and then Backup tab.

If you want to restore, the way to do it is simply by right clicking the database you want to restore. Click  Task and Restore. It will Rollback to where you last backed up. You can even rollback to specific date and time. More later.

Attaching Database

Let’s attach our database three  ‘Adventureworks’ file to server. To do so, right click Databases and select ‘attach’ file. Now locate the mdf files  and you are all set.

SQL SERVER Background Info (Part-3)

I hope by now you have SQL Server installed. In this post I will quickly talk about database design and give you a quick overview of different things.

Database Design (Quick Snapshot):

Your database initially is empty or there may be a need to add data at some point. Therefore, you need to be able to design your database efficiently. Database Design is simply a way of getting tables you need right. So department table will have things like department id , department name, department location etc. This is a job of data architect. The design process has three phases in general.

1. Gathering requirements from BA. Verifying relationship and creating conceptual diagrams.
2. Coming up with logical diagram. Architect comes up with table names, dependencies and data types etc.
3. Physical model.

Usually the above process can be carried out in Erwin or SQL Power Architect. It’s relatively straight forward. In the end of the process you can generate a schema using forward engineering. Hit the ‘Generate‘ button and Connect to SQL Server (using windows authentication or whatever system you have) and your tables will be created from Erwin. You can also generate scripts for tables you have and copy paste them in SQL server. Remember database have different objects like tables, views , stored procedures, functions, trigeers etc but tables hold the real data.

Noobs 3 Best Practices For Database Design:

1. Document your ER diagram.
2. Consistency in naming.
3. Spend more time designing.

Overview:

When you install your server, you will not get any databases. You will only get system databases which are master, model, tempdb and msdb. Please do no create tables on system databases. Why? If you do, upgrading later will be a big problem. We will soon talk about attaching the database we downloaded in Part-1 in SQL Server Management Studio.

One last thing. Majority of business users are never concerned about all this stuff. All they want is access to reports. Where are the reports? In SQL SERVER REPORTING SERVICES you will have an URL where reports are hosted. You have report server name like  https://mystartup/reports/folders.aspx. In other companies they have sharepoint portal. Report is usually tested and deployed. If users want to add column then the whole life cycle is repeated. There is no Ad-hoc in this scenario. We need to be able to give users Ad-hoc capabilities. To do so, we need cubes and give users access to them. I really didn’t want to go through all this but again this will sets up a stage for my future posts.

In excel, either you can connect to SQL SERVER directly and import data in or you can use Analysis Services to retrieve cubes. In order to create a cube you need a Data mart. Data mart is simply an access layer of your Data Warehouse.  We can create a new data mart for user requirements and write an ETL using SSIS. Phew…

d3Network in R

Few weeks ago I saw my friend create network graphs in python. I am not sure if he was embedding d3.js script using d3py or simply using a library in python; but anyway, I wanted to see if R had a similar package. A quick search in google and baam… There’s a package called d3Network for making sankey, network and dendogram graphs. This package was created by Christopher Gandrud. In this post, I simply attempt to explore and write my thoughts on it..

1. d3SimpleNetwork – Exploring three products of google – Blogger, Youtube and Songza.

The above network is dynamic. You can actually move things around and click on nodes to magnify texts. WordPress doesn’t allow to embed javascript for security reasons so I just took a snapshot of the output. I think I might move this blog to a different platform. Anywho, the thought process in the network above – Youtube was paypal’s product which was acquired by google. So a table would be…

Source	Target
Paypal	Youtube
Google	Youtube
PyroLabs	Blogger
Google	Blogger      …. and so on. You would fill this in a data frame below

Here’s the code to do all this.

install.packages(‘d3Network’)
library(d3Network)
googleacq<-data.frame()
googleacq<-edit(data.frame())   ## You can input your source/target stuff  here. Remeber Only two columns
d3SimpleNetwork (googleacq, width = 400, height = 600, textColour = “black”,
linkColour = “red”,nodeColour = “green”,nodeClickColour=’orange’,
opacity = 1.1, charge = -300, fontsize = 14)

After you run the code above, you need to copy and paste the script it returns  in a text editor and save it as .html file. Or you could just open d3_example1 in a browser, by saving it as a webpage in MsWord first.  Alternatively, you can copy paste the code in javascript section of http://jsfiddle.net and hit RUN.

Thoughts: It would be cool to have an arrow in the model so that it’s much more easier to understand the flow. If you want to make more complicated force network then you can use d3ForceDirected. Nevertheless, this is a great package!

2. d3Tree – A tree graph to show my favorite songs from different Metallica Albums.

As suggested by the author, it is easier to import data from JSON file to R rather than going through list steps. Also, you can zoom and highlight different nodes.

Here’s the code to generate the tree above.
Metallica <- list(name = “Metallica”,
children = list(list(name = “Kill em all”,
children = list(list(name = “The Four Horsemen “),
list(name= ‘ Seek and Destroy’),
list(name = ‘Jump in the Fire’))),
list(name = “Ride the Lightning”,
children = list(list(name = “Creeping Death”),
list(name = “For Whom The Bell Tolls”),
list(name = “Fade TO Black”),
list(name = “Creeping Death”),
list(name = “Ride The Lightning”))),
list(name = “Master of Puppets”,
children = list(list(name = “Master of Puppets”),
list(name = “Sanitarium”),
list(name = “Orion”))),
list(name = “.. And Justice For all”,
children = list(list(name = “One”),
list(name = “Harvester of Sorrow”),
list(name = “Blackened”))),
list(name = “Metallica”,
children = list(list(name = “Sad But True”),
list(name = “EnterSandman”),
list(name = “Nothing Else Matters”),
list(name= ‘Unforgiven’),
list(name= ‘Wherever I may Roam’))),
list(name = “Load”,
children = list(list(name = “King Nothing”),
list(name = “Until it Sleeps”),
list(name = “Mama Said”))),
list(name = “Reload”,
children = list(list(name = “The Unforgiven II”),
list(name = “Better Than You”),
list(name = “The Memory Remains”))),
list(name = “St. Anger”,
children = list(list(name = “”))),
list(name = “Death Magnetic”,
children = list(list(name = “”)))))

d3Tree(List = Metallica, fontsize = 22, diameter = 800,zoom=TRUE, opacity = .8,
textColour = “#D95F0E”, linkColour = “#0000FF”,
nodeColour = “#FF3399”, height = 2000, width = 3000,)

Click d3tree_metallica  and open the file in web browser to play around. After the last line of code a series of HTML and Javascript codes are outputted. Save them in a text editor, rename as .html and open it in your browser. Alternatively, you can copy paste the code in javascript section of http://jsfiddle.net and hit RUN.

Thoughts: Like Tableau, it would be nice if labels didn’t overlap. Also, it would really be cool to be able to rotate the tree instead of just dragging it. I will further explore other stuffs like Sankey and clusterdendro. Great Package overall!

SQL SERVER Background Info (Part-2)

How are companies like Amazon, Google, Facebook getting their data? A simple answer is through user interface(UI). Users put informations in an application (eg. creating new gmail account) and data gets stored in the back-end database. Users are capable of changing data whenever they want. The database holding this application data can be updated, inserted and deleted anytime. This type of database is called Application Database. There are other several type of databases like:

1. Relational Database -Data relationship are tabular in nature. Eg. Oracle, Teradata, SQL SERVER etc
2. Object-Oriented Database – Apart from data, O-OD can handle things like photographs and videos.

Here’s a simple question. What is the main goal of Application Database? Fast access to data. The tables in this type of database are normalized and is great for OLTP ( On-line Transcriptional Processing). Denormalized tables on the other hand are great for OLAP (On-line Analytical Processing). Let’s quickly differentiate normalization/denormalization and OLAP and OLTP.

OLAP vs. OLTP

OLTP	OLAP
Running day to day business activities.	Great for problem Solving, Reporting, budgeting, forecasting etc.
Highly normalized.	Typically de-normalized
Backups are very important. Data loss is like losing a house & wife and paying for child support. Day to day will be a hell.	Data can be simpy reloaded. Kind of like breaking up after two months of relationship. You will find your way.

Normalization vs. Denormalization

Normalized Database	Denormalized Database
Optimized for inputting faster. Typically 3NF form used.	Optimized for reading faster. Eg – Star Schema, Materialized View and Cubes
SELECT, INSERT and UPDATE statements are faster.	Depends.
Require Joins. Indexing not possible.	No joins. Since data is aggreaged SELECT statements are fast and indexing can be used.

Normalization

Optimization Through Denormalization

SQL SERVER Background Info (Part-1)

So before I start writing on SQL SERVER, here’s the outline of my plan.

1. Less DML (Data Manipulation Language) commands, More on DDL(Data Definition Language) side.
2. Focus on functions, stored procedures, triggers, database structures, database design etc.
3. I plan to write on SSIS, SSRS and SSAS.
4. Little bit on database maintenance, migration, security etc.
5. Maybe little bit on MDX for cubes.

I will be using SQL Server 2008 R2. So you’ll need to download it and a database to play with. They are freely available online. Here’s the link to the database and SQL SERVER. As of today both are working fine.

http://msftdbprodsamples.codeplex.com/releases/view/93587
http://www.microsoft.com/en-us/download/details.aspx?id=30438 .

After you download, click on the setup file and follow the instructions below. You will reach a point in the video where he talks about features part. Make sure along with Database Engine, you install Analysis Services and Reporting Services. He does not do it!