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 ” ”
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
Note the IP address in the upper left. If you copy this into a browser of your choice, it will run there too.
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
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)
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()
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
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.
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.
— 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.
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 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.
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
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)
Inside the Business Intelligence Templates, select Integration Services. I always just select Integrations Service Project, I’m not a big fan of the Wizard
Next step: Name your project
Now you are in SSIS. Here are the 3 main windows you will be starting with.
From right to left:
SSIS Toolbox
Package Designer
Solution Explorer
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.
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.
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
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”);
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.
Now click the OK button – again I guess Save was too much to type
Now right click on your package (green arrow) and click Execute Package
Your message will pop up in a Message box window
Click OK on the messagebox and Click the red square to end the package execution.
Congrats, you have just built and executed your first SSIS Package.
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.
With out data loaded, we can search through the data using key words and wildcards
But for something cool, click on the Patterns tab. This shows interesting patterns Splunk has sniffed out on its own.
Click on any of the Interesting Fields Splunk created from your data to see some interest quick stats.
You can dig deeper into the Stats by picking one of the canned reports that appear in the pop-up window.
You can run visualizations from these reports.
Of course you can change the visualization type from a simple menu
The Statistics tab shows numeric stats based on the report/query you are working with.
If while interacting you find search you want to keep or share it, you can save the settings in it as a Report
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.
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.
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]
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
Try your code in our online Python console:
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
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.
Append()
If you want to add an item to then end of a list, you can use the Append() function
Del
To delete a item from a list, use the Del command.
Remove()
Remove works like Del, except instead of using index values, Remove() uses the values stored in the list.
Try your code in our online Python console:
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.
Len()
Len() returns a count of the number of items in a list.
Try your code in our online Python console:
If you enjoyed this lesson, click LIKE below, or even better, leave me a COMMENT.
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
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.
Here is what happens if I try using Print instead of Return. Notice the division problem errors out.
Try your code in our online Python console:
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.
Try your code in our online Python console:
If you enjoyed this lesson, click LIKE below, or even better, leave me a COMMENT.