R: Connecting to SQL Server Database

You can query data from a SQL Server database directly from R using the RODBC package.

install.packages("RODBC")

First you need to form a connection

library(RODBC)
##connection string
cn <- odbcDriverConnect(connection="Driver={SQL Server Native Client 11.0};server=localhost; database=SSRSTraining;trusted_connection=yes;")

We use the odbcDriverConnect() function. Inside we pass a connection = value

Driver = {SQL Server Native Client 11.0};  — this is based on the version of SQL Server you have

server=localhost;  — I used localhost because the SQL Server was on the same computer I was working with. Otherwise, pass the server name

database=SSRSTraining; — name of database I want to work with

trusted_connection=yes; — this means I am able to pass my Windows credentials.

If you don’t have a trusted connect pass the user Id and password like this

uid = userName; pwd = Password;

Note each parameter is separated by a semicolon

Query the database

> ##passes query to SQL Server
> df <- sqlQuery(cn, "select * FROM [SSRSTraining].[dbo].[JobDataSet]")
> head(df)

    Name              Job Hours Complete
1  Sally Predictive Model     1        n
2 Philip      Maintanence    10        n
3    Tom    Ad-hoc Report    12        y
4    Bob             SSRS     3        y
5 Philip         Tableau      7        n
6    Tom         Tableau      9        n

using sqlQuery() – pass through the connection string (cn) and enclose your query in ” ”

 

 

 

Advertisements

R Shiny: Introduction

Shiny is an R Studio application that allows you to how host your R code on a website. This is a great method for creating a dashboard or deploying a predictive model. The best part is, it is free. There are, however, paid services associated with R Studio and Shiny if you want to get professional support and such.

In this lesson, we are going to learn to build a Shiny server application step by step.

Let’s start at the beginning. First off, to use Shiny, you need to have R Studio installed on your computer. Once you have that, you will need to install the Shiny package

install.packages(‘shiny’)

Next, we are going to learn about the three parts of a Shiny server application.

Below is the basic framework of a Shiny app:

library(shiny)

ui <- fluidPage("Test")
server <- function(input, output) {}
shinyApp(ui = ui, server = server)

Let’s break this down:

ui: ui is the user interface. This is the code that makes up the web page the user will launch and interact with. Anyone familiar with HTML, this is the basic markup. This is where you set the wording, font, colors, what kind of object will be on the page, etc

server: server is the brains of the operation. This is where you load and manipulate any data being used in your page. Again, if you are a web developer, think Java Script here.

shinyApp: this is the code that launches your App.

If you run the code above, you will see something like this

2018-03-19_11-06-29

Note the IP address in the upper left. If you copy this into a browser of your choice, it will run there too.

2018-03-19_11-07-28.png

You will see the same address in the R Console. As long as the R Shiny server is running, you will see this Listening command. For those not familiar, the address 127.0.0.1 is called a pingback address. This is the internal address of the NIC card on your machine. This means the server is running on the machine you are on. The :4654 is the port my computer grabbed. This can be different for you. The easiest way for me to explain the port without going too deep into network theory is: think of it like a TV channel. From your computer, you could technically be running hundreds of these R Shiny servers. The port (TV channel) is what tells the computer which one of these web pages you want to visit.

If you happen to be on a network, you can go to another computer and type in your host computers network IP address (you can get it by going to a cmd console and typing ipconfig). It will be the address I have the arrows pointing to in my example

2018-03-19_13-33-33.png

Then, using my example, type http://10.171.46.91:4654 into the second computer’s browser and you should see your Shiny website.

Step 2

Let’s see if we can make our website more interesting, let’s start easy, we will format the text.

First close out your shiny window to end the session.

Change your ui line to match the one below.

ui <- fluidPage(h1(“Test”))

Now run your code again

library(shiny)
ui <- fluidPage(h1(“Test”))
server <- function(input, output) {}
shinyApp(ui = ui, server = server)

2018-03-19_13-38-26

Step 3

Okay, let’s make it a little more interesting by adding some detail to our page. We will do so by using panel methods in our fluidPage()

library(shiny)

ui <- fluidPage(
  titlePanel("Title Panel"),
     sidebarPanel("Sidebar Panel"),
      mainPanel("Main Panel")
    )
server <- function(input, output) {}
shinyApp(ui = ui, server = server)

Notice your page is now segmented into a title bar, a sidebar and a main panel

2018-03-19_13-52-55.png

Okay, now let’s actually add some R data magic to this.

library(shiny)
library(datasets)
mpgData <- mtcars

ui <- fluidPage(
  titlePanel("Mpg vs Cylinder Plot"),
     sidebarPanel("Sidebar Panel"),
      mainPanel(  plotOutput("mpgPlot"))
    )
server <- function(input, output) {
  output$mpgPlot <- renderPlot({
    boxplot(mpg~cyl, data= mpgData)})
}
shinyApp(ui = ui, server = server)

Let’s break down the new code. First thing first, we are loading the built in datasets library and grabbing the mtcars data set from it.

library(datasets)

mpgData <- mtcars

Next we are going to skip down to the server section next as this is where we work with our data:

First thing you will notice is function(input, output) – this is a standard call that allows you to pass information back and forth through the server code. In our example, we are only using the output right now.

We create a variable called output$mpgPlot to which we pass the shiny method renderPlot.  Inside that method, we are simply creating a simple boxplot comparing mpg to cyl using the mtcars data set that we labeled mpgData

server <- function(input, output) {
  output$mpgPlot <- renderPlot({
    boxplot(mpg~cyl, data= mpgData)}) 
}

The boxplot we rendered as output$mpgPlot is now passed to our ui

Next, in the ui I renamed the title panel and in the mainPanel, I added the command plotOutput(“mpgData”). That will allow me to post the boxplot I created in the server to the ui web page.

ui <- fluidPage(
  titlePanel("Mpg vs Cylinder Plot"),
     sidebarPanel("Sidebar Panel"),
      mainPanel(  plotOutput("mpgPlot"))
    )

If you run the code, it should look like this:

2018-03-19_14-59-52

Step 4

Now let’s add a little interactivity to our web page. We are going to give the user the option to view the boxplots with or without the outliers taken into effect.

library(shiny)
library(datasets)
mpgData <- mtcars

ui <- fluidPage(
  titlePanel("Mpg vs Cylinder Plot"),
     sidebarPanel(checkboxInput("outliers", "Show outliers", FALSE)),
      mainPanel( 
                      plotOutput("mpgPlot"))
    )
server <- function(input, output) {
  output$mpgPlot <- renderPlot({
    boxplot(mpg~cyl, data= mpgData,
            outline = input$outliers)})
}
shinyApp(ui = ui, server = server)

This involves only two changes to our code

First we will add a checkbox to our sidebar, in the ui we will alter the sidebarPanel line to look like this

sidebarPanel(checkboxInput(“outliers”, “Show outliers”, FALSE)),

The breakdown of the syntax here is

checkboxinput(variable name, display text, default )– our default is set to FALSE so our box will be unchecked by default

 

Next, we will alter our boxplot to take into account the checkbox input

boxplot(mpg~cyl, data= mpgData,
            outline = input$outliers)

— notice the variable name is input$outliers  It is a composite of the variable from our checkboxinput method and “input” from out function(input, output) function that opens up our server.

Now run your code

Unchecked

2018-03-19_15-05-46.png

Checked

2018-03-19_15-05-29.png

SSIS Tutorial: Introduction

SSIS: Introduction

SQL Server Integration Services is the ETL tool for the Microsoft SQL Server platform. SSIS allows you to take data from various sources (from Excel files, to text files, to other databases, etc), and bring it all together.

If you are new to concept of ETL, SSIS is great place to start. Click here to learn about ETL

If you are well versed in another ETL platform, SSIS is a relatively easy system to get up to speed on.

SSIS comes as part of SQL Server Data Tools, which you should be able to install with your SQL Server installation software. You will need SQL Server Standard, Developer or above editions to run SQL Server Data Tools. SQL Server Express does not support Data Tools.

For some unknown reason, once it is installed, you will not find a program called SSIS. Maybe the engineers at Microsoft think this is funny, but in order to run SSIS you will need to look for the following program instead.

2018-02-26_14-26-37

I’m using 2015, but for most of what I am doing here, any version should be compatible.

When you launch data tools, you will notice it run in Visual Studios

2018-03-06_10-37-28

To start an SSIS job, you will either need to open an existing project, or create a new one. In this example, I will create a new one.

File ->New -> Project  (or Ctrl+Shift+N)
2018-03-06_10-37-54

Inside the Business Intelligence Templates, select Integration Services. I always just select Integrations Service Project, I’m not a big fan of the Wizard

2018-03-06_10-38-37

Next step: Name your project

2018-03-06_10-40-08

Now you are in SSIS. Here are the 3 main windows you will be starting with.

From right to left:

SSIS Toolbox

2018-03-06_10-43-40

Package Designer

2018-03-06_10-44-00

Solution Explorer

2018-03-06_10-44-19

Packages

Inside solutions, packages are the collections of jobs or scripts found inside a project. It is inside the package that you will build out your ETL job.

For our first lesson, we are just going to build a simple package. Your new solution should have opened with a new package when it opened. If it is, right click on the green arrow to rename it, if not, right click on the red arrow to create a new package.

 

2018-03-06_10-44-19_1

I renamed my package First_Package, you can name your package whatever you choose.

This first package will simply just display a pop up message. In the SSIS Toolbox, go to Script Task and drag it into the package designer window.

2018-03-06_13-09-46

2018-03-06_13-25-47

Double click on the Script Task Box in the Design window

Note in my example, I have C# set as the scripting language. The other default option is Visual Basic. If you are more comfortable with that, feel free to use it. I prefer C# mainly because I spent more time working with it.

You don’t need to know any C# for this tutorial. This is literally a single line of code assignment. I will cover more C# in the future.

Click Edit Script… to continue

2018-03-06_13-26-45

Note, this step can take a minute or so for the script editor to appear. Don’t panic if your computer appears locked up.

Once the script editor opens, don’t panic by all the code you see. Luckily Microsoft has done most of the ground work for us. We only need to scroll down until you see:

public void Main()

Now place your cursor below //TODO: Add your code here

The code you need to type for this script is:

MessageBox.Show(“This is my first SSIS Package”);

2018-03-06_15-12-02

Now I know you will be looking for a save button. But again, our friends at Microsoft might have been drinking when they coded this. Instead, just click the upper right X to close out the whole window –

I know – why would they do it that way? how much effort would a save and close button have cost them? I don’t know. It just is what it is. Just click the X and move on with your life.

2018-03-06_15-13-33

Now click the OK button – again I guess Save was too much to type

2018-03-06_15-14-08

Now right click on your package (green arrow) and click Execute Package

2018-03-06_10-44-19_1

Your message will pop up in a Message box window

2018-03-06_15-17-49

Click OK on the messagebox and Click the red square to end the package execution.

2018-03-06_15-18-09

Congrats, you have just built and executed your first SSIS Package.

 

Splunk: Overview of functionality

Splunk is great at analyzing large sets of unstructured data quickly and easily. To simulate working with a stream of data, I am using a log file I pulled from a server. You can download it here: event

Load this into Splunk through data upload button. You can just hit next through the remaining upload screens. The default setting work just fine for us.

2016-11-30_08-57-31

With out data loaded, we can search through the data using key words and wildcards

2016-11-30_09-03-51

But for something cool, click on the Patterns tab. This shows interesting patterns Splunk has sniffed out on its own.

2016-11-30_09-13-33

Click on any of the Interesting Fields Splunk created from your data to see some interest quick stats.

2016-11-30_09-16-40.jpg

You can dig deeper into the Stats by picking one of the canned reports that appear in the pop-up window.

2016-11-30_09-30-07

You can run visualizations from these reports.

2016-11-30_09-30-28

Of course you can change the visualization type from a  simple menu

2016-11-30_10-15-25

The Statistics tab shows numeric stats based on the report/query you are working with.

2016-11-30_09-30-58

If while interacting you find search you want to keep or share it, you can save the settings in it as a Report

2016-11-30_09-18-05.jpg

When you save the report, Splunk gives you a default option to add a Time Range Picker that will let you pick a time range next time you run the report.

2016-11-30_09-18-20

I personally recommend playing with Splunk to get a better feel for it. What is so cool about it is how quickly and easily you can produce actionable analytics.

 

Python: Working with Lists

Making a list in Python is simple: x = [1,2,3,4]

Now let us see how we can work with them.

Index

First thing to understand is that a list is indexed. Each item in the list is given a number that tells you its position in the list.

It is important to note that Python is a 0 index language. This means indexes begin at 0 not 1. So the first item in the list, is found by calling: x[0]

pythonlist

Note that x[4] returns an error. Since there are 4 items in the list, the indexes go 0,1,2,3. Index 4 is out of range.

Another interesting point to understand about indexes is that you can use a negative index. x[-2]  returns Duck

Index Range

Use a “:” to return a range of items from a list: x[start:end]. If you leave out the start or end number, the index starts or ends at the start or end of the list

pythonlist1

Update Values in a List

If you want to change a value in a list, just assign it a new value like you would with a regular variable.

pythonlist2

Append()

If you want to add an item to then end of a list, you can use the Append() function

pythonlist3.jpg

Del

To delete a item from a list, use the Del command.

pythonlist4

Remove()

Remove works like Del, except instead of using index values, Remove() uses the values stored in the list.

pythonlist5.jpg

Pop() and Insert()

Pop() simply returns the last item from a list

Insert() lets you add a item to a list, but it also lets you choose what position in the list to add it.

pythonlist6.jpg

Len()

Len() returns a count of the number of items in a list.

pythonlist7.jpg


If you enjoyed this lesson, click LIKE below, or even better, leave me a COMMENT. 

Follow this link for more Python content: Python

 

 

 

Python Functions

Working with functions will greatly improve your programming capabilities. You have already worked with a few functions: range(), float(). Now I am going to show you how to build your own.

One great thing about functions is they are reusable. Image you had written a complex formula that calculates final grade based on points earned for 25 tests and assignments. Would you want to have to rewrite that code every time you need it? No, it is much easier just to write it once and call it up when you need it.

Enough talk, let’s build our first function.

Syntax

def FUNCTION NAME (PARAMETERS):
“””DOCSTRING”””
COMMANDS
RETURN[EXPRESSION]

In the example below, we are not using Return yet. We will get to
that soon enough.

So let’s examine the function below:

  • def talkback (strg): = define a new function named talkback which takes one parameter – strg ** note strg is just a variable name I used. It could just as easily been x or y
  • “””this prints the string you submit””” = this is the docstring, we will cover this in a minute
  • print strg = this is the command portion of the function. This will print the parameter you pass to it.
  • ln[2]: talkback(“Print this line”) = this calls the function we had just created. It passes the value “Print this line” to strg, which the function then prints
  • help(talkback) — this displays the docstring from the function. Docstrings are instructions that help you to understand how the function works. This is especially important as your functions become more and more complex. Just think of docstrings as help functions.  ** note the triple quotes “”” — this allows you have multiple lines inside the quotes

pythonfunc

Now let’s look at a function using Return

This function, named summed(), is accepting 3 parameters. It then takes the values of the 3 passed parameters and adds them up (assigning them to “s“). Instead of printing the result out, like in the example above, it uses Return s to return the summed value.

The purpose of Return can be seen in ln[8]. As you can see, the summed() function is being used as a number in a division problem. We are able to do this, since the Return command effectively replaces the summed() function with a numeric value in the division equation.

pythonfunc2.jpg

Here is what happens if I try using Print instead of Return. Notice the division problem errors out.

pythonfunc3.jpg

Default Arguments and Named Arguments

Let’s look at this function below:

The first thing I want you to notice is the price = 50 in the parameters section. This is called a default argument. As you can see in ln[15], if I only pass a value for item, function prints out a price of 50 – the default value given.

Now look at ln[16]. When I pass a value for price, it overrides the default value of 50 and sets it to 25

Finally, look at ln[17]. This is naming my arguments. Notice the order of the arguments can be overridden if I pass arguments in the form of parameterName = Value.

pythonfunc4.jpg


If you enjoyed this lesson, click LIKE below, or even better, leave me a COMMENT

Follow this link for more Python content: Python