R Tools For Visual Studio

Matt Willis has a two-parter on R Tools for Visual Studio.  First, an introduction:

Once all the prerequisites have been installed it is time to move onto the fun stuff! Open up Visual Studio 2015 and add an R Project: File > Add > New Project and select R. You will be presented with the screen below, name the project AutomobileRegression and select OK.

Microsoft have done a fantastic job realising that the settings and toolbar required in R is very different to those required when using Visual Studio, so they have split them out and made it very easy to switch between the two. To switch to the settings designed for using R go to R Tools > Data Science Settings you’ll be presented with two pop ups select Yes on both to proceed. This will now allow you to use all those nifty shortcuts you have learnt to use in RStudio. Anytime you want to go back to the original settings you can do so by going to Tools > Import/Export Settings.

Next is executing an Azure Machine Learning web service within RTVS:

Whilst in R you can implement very complex Machine Learning algorithms, for anyone new to Machine Learning I personally believe Azure Machine Learning is a more suitable tool for being introduced to the concepts.

Please refer to this blog where I have described how to create the Azure Machine Learning web service I will be using in the next section of this blog. You can either use your own web service or follow my other blog, which has been especially written to allow you to follow along with this blog.

Coming back to RTVS we want to execute the web service we have created.

RTVS has grown on me.  It’s still not R Studio and may never be, but they’ve come a long way in a few months.

Databases In Source Control

Robert Sheldon walks through core concepts of source control:

The source control system can’t merge the two file versions until Barb resolves the conflict between black bear and brown bear (the additions of wolf and fox still cause no problem).

When conflicts of this nature arise, someone must examine the comparison and determine which version of bear should win out. In this case, Barb decides to go with black bear.

It’s worth considering the risk associated with this merge process. Barb’s commit fails, so she can’t save her changes to the repository until she can successfully perform a merge. If something goes wrong with the merge operation, she risks losing her changes entirely. This might be a minor problem for small textual changes like these, but a big problem if she’s trying to merge in substantial and complex changes to application logic. This is why the source control mantra is: commit small changes often.

The article is more of an intro to source control, but if you aren’t familiar with how source control works, it’s a great read.  Regardless, the best thing you can do for yourself is to get your database code in source control.  That opens up the possibility for safer refactoring of code.

CRISP-DM

Steph Locke explains the CRISP-DM model for data mining projects and applies it to data science projects:

Within a given project, we know that at the beginning of our first ever project we may not have a lot of domain knowledge, or there might be problems with the data or the model might not be valuable enough to put into production. These things happen, and the really nice thing about the CRISP-DM model is it allows for us to do that. It’s not a single linear path from project kick-off to deployment. It helps you remember not to beat yourself up over having to go back a step. It also equips you with something upfront to explain to managers that sometimes you will need to bounce between some phases, and that’s ok.

This is another place in which “iterate, iterate, iterate” ends up being the best answer available.

More On Columnstore Batch Mode

Sunil Agarwal talks about batch mode processing with columnstore indexes:

While these results may not appear as dramatic on my laptop, the picture below shows the performance gains with Window Aggregates on a Server class machine with large DW database. The orange bar represents the query speed up we got with Window Aggregate operator in BatchMode. The highest speed up we saw was 289x!!

Batch mode is generally a huge benefit for data warehousing environments.

USE HINT In SQL Server 2016 SP1

Lori Brown documents the hints available with SQL Server 2016 SP1’s USE HINT syntax:

USE HINT ( hint_name ) Provides one or more additional hints to the query processor as specified by a hint name inside single quotation marks. Hint names are case-insensitive. USE HINT can be utilized without having to be a member of the sysadmin server role.

It’s an interesting list.

Getting Effective Permissions

Jana Sattainathan explains how to get a user’s effective permissions:

Microsoft has provided a function named fn_my_permissions that gets the permissions for you. It has multiple classes of objects for which you can get permissions. We already know about SERVER, DATABASE and OBJECT but there are a lot more. To get that list of classes, let us use fn_builtin_permissions function

The one thing I wish this function did was let a sysadmin see another user’s effective permissions without using EXECUTE AS; I’d like to be able to apply that function to sys.logins and get an exploded list for reporting.

Upgrading SSRS From 2008R2 To 2014

Dave Turpin needed to perform an upgrade of a Reporting Services installation:

So let me share with you my biggest take away from this project:  EVERYTHING ABOUT USER CREATED REPORTS IS STORED IN THE SQL Server Reporting databases.  So if you are reading this post you probably are about to move an instance of SSRS, and may be concerned about the many, many reports involved.

Based on my one experience with this, there is need to move individual reports.  If you follow the process carefully, all of the existing reports will be re-created on the new machine.  It’s not quite magic, but it sure feels like it when everything shows up on the new system.

Read on for the solution Dave came up with.

Basics Of Azure Analysis Services Management

Bill Anton walks through some of the basics of managing an Azure Analysis Services cube:

The quickest win – from an ROI perspective – for Azure AS is the ability to pause the instance during extended periods of inactivity – for example, at night, when there aren’t any users running reports.

This can be achieved via the Suspend-AzureRmAnalysisServicesServer cmdlet we saw in the previous post.

Read on for a few tips of this ilk, including resizing the server.

Joins Galore

Kevin Feasel

2017-01-13

Syntax

Lukas Eder has a comprehensive guide to joining data using SQL:

Alternative syntaxes: NATURAL JOIN

An more extreme and much less useful form of "EQUI" JOIN is the NATURAL JOIN clause. The previous example could be further “improved” by replacing USING by NATURAL JOIN like this:

SELECT *
FROM actor
NATURAL JOIN film_actor
NATURAL JOIN film

Notice how we no longer need to specify any JOIN criteria, because a NATURAL JOIN will automatically take all the columns that share the same name from both tables that it joins and place them in a “hidden” USING clause. As we’ve seen before, as primary keys and foreign keys have the same column name, this appears quite useful.

There is a high likelihood that you will learn at least one new thing here; for example, check out lateral joins (which SQL Server practitioners know as something else).

Turning On SQL Authentication

Kenneth Fisher hits a frequent cause of login failure:

No go. Next thing to check is the password. The error I’m getting does indicate the incorrect password so it’s a distinct possibility. Now when I moved the server principal I made sure to copy the password hash from the old server so the password should be the same. However, I’ve made mistakes before, and odd things happen, so I decided to change the password just in case. Before I did I backed up the original password just in case.

The solution is something that I’ve seen a lot of sysadmins forget to do in their setup processes.

Categories

February 2019
MTWTFSS
« Jan  
 123
45678910
11121314151617
18192021222324
25262728