Monday, 12 May 2014

R mapped with Microsoft SQL in Detail with an Example



                     Mapping of R Code with Microsoft SQL with an Example

INPUT SOFTWARE VERSIONS

1. R Programming :- Version 3.0.3 (64 bit)
2. Microsoft SQL :- SQL Server 2014 (CTP2)
3. Microsoft Windows 7 (Professional)
SQL server can be connected using different platforms such as ODBC and JDBC.

ODBC stands for Open Database Connectivity which has an independent interface platform.
JDBC stands for Jave Database Connectivity in which Java application is used to access databases.

Currently, we will be using ODBC.

Details of Data set:-
1. Name:-  Bank Authentication
2. Source:- UCI repository   https://archive.ics.uci.edu/ml/datasets/banknote+authentication
3. Data set Characteristics:- Multivariate
4. Task Performed:- Classification
5. Number of Independent Variables or Attributed:- 5
6. Number of Instances:- 1072
  
Prior connecting SQL to R, few basic steps need to covered.
(Screenshots are provided for better coherence)

                                                  1. Create a Data Source using ODBC

a) Open Control Panel  >   System and Security  >  Administrative Tools
 Double click Data Sources (ODBC)



b) In User DSN tab, click ADD


c) Select ODBC Driver 11 for SQL server and Click Finish



d) Select the Server you want to connect to and provide a Name to the data source with a short description. Click Next.




e) Select the option for SQL server to verify the Authentication of Login ID. Here, I have used the option of "With Integrated Windows Authentication". Click Next.





f) Select the Default Database where your data is stored using the dropdown list under Change the default database to:. You can also change the Application intent to Readwrite or Readonly mode. Here I have selected ReadWrite mode. Click Next and Finish.





 


You can Test the Data Source and Click OK.
 Finally a new Data Source is created.