Press "Enter" to skip to content

Day: September 19, 2018

Interpreting The Area Under The Receiver Operating Characteristic Curve

Published 2018-09-19 by Kevin Feasel

Roos Colman explains what a Receiver Operating Characteristic (ROC) curve is and how we interpret the Area Under the Curve (AUC):

The AUC can be defined as “The probability that a randomly selected case will have a higher test result than a randomly selected control”. Let’s use this definition to calculate and visualize the estimated AUC.
In the figure below, the cases are presented on the left and the controls on the right.
Since we have only 12 patients, we can easily visualize all 32 possible combinations of one case and one control. (Rcode below)

Expanding from this easy-to-follow example, Colman walks us through some of the statistical tests involved.  Check it out.

Comments closed

Building A Neural Network In R With Keras

Published 2018-09-19 by Kevin Feasel

Pablo Casas walks us through Keras on R:

One of the key points in Deep Learning is to understand the dimensions of the vector, matrices and/or arrays that the model needs. I found that these are the types supported by Keras.

In Python’s words, it is the shape of the array.

To do a binary classification task, we are going to create a one-hot vector. It works the same way for more than 2 classes.

For instance:

  • The value 1 will be the vector [0,1]
  • The value 0 will be the vector [1,0]

Keras provides the to_categorical function to achieve this goal.

This example doesn’t include using CUDA, but the data sizes are small enough that it doesn’t matter much.  H/T R-Bloggers

Comments closed

Histomaps

Published 2018-09-19 by Kevin Feasel

Jennifer Lyons shows us a few places where we can use histomaps to good effect:

Histomaps can be a good option when we are looking to visualize qualitative trends over time. The trick is that you need to be using two mutually exclusive variables. In Spark’s case he used time and power. In the example below, I am using time and staff’s satisfaction with their work environment. Imagine you are collecting open-ended survey data every quarter during your grant term. You code a person’s response into the mutually exclusive category of met, partially met, and not met regarding their expressed satisfaction.

Read the whole thing.

Comments closed

T-SQL Tuesday Trigger Roundup

Published 2018-09-19 by Kevin Feasel

Steve Jones has a bunch of links about triggers this month:

This month was my turn to host T-SQL Tuesday. I chose Trigger Headaches or Happinessas the topic, and I am glad that there have been quite a few responses.

I started the review almost immediately,and here are a few highlights. I separated these based on how I first thought of them after reading the post. If you think I’ve mischaracterized one, let me know.

In case you don’t want to add, it’s 9 to 13, so triggers are a headache.

It’s a good roundup of cautionary stories, tips, and tricks.

Comments closed

Storing Constants For MDX Calculated Measures

Published 2018-09-19 by Kevin Feasel

Chris Webb walks us through an interesting performance problem when using Analysis Services multidimensional:

All it does is take the value of the Sales Amount measure at the lowest granularities of the Customer, Date and Product dimensions, multiply it by 0.08 to find a tax value, and because [Tax Amount] is a real, non-calculated measure, the result of the calculation aggregates up through the cube. [I know that I don’t have to aggregate the result of this specific calculation but remember that this is a simplified example – in the real case I did have to write the calculation using Scope statements – and anyway the best way of handling a basic multiplication like this would be with a measure expression]

The performance was sub-second for my test query and I was happy, but then I realised that the same tax rate was being used in other calculations and may change in the future, so I thought I would store the value 0.08 in a calculated measure:

Chris walks through several iterations of this before landing on a solution which is both reasonable and fast.

Comments closed

What sys.dm_exec_query_stats Can Miss

Published 2018-09-19 by Kevin Feasel

Matthew McGiffen takes us through a scenario where sys.dm_exec_query_stats did not give a complete view of what was running on SQL Server:

I got less than 50 rows back so figured I had everything covered, but the total elapsed time across all the queries was less than ten minutes, I knew the server hadn’t been rebooted for about a month so potentially that could be as little as 20 seconds of query execution a night. Even if the full 10 minutes was from the last 24 hours that still didn’t account for the long run times.

So, I convinced myself it wasn’t the queries and started looking at other things they could check. It was a long list. Lots of theories revolved around the idea that something else was running on the host at the same time.

Click through for the rest of the story.

Comments closed

Permissions Needed To View Permissions

Published 2018-09-19 by Kevin Feasel

Kenneth Fisher shows us what we can do to grant a low-privilege user the ability to view permissions for other users:

Every now and again you’ll have a user that needs to be able to see what permissions other users have. Not change them, just look at them. In the cases I’ve seen it’s usually a manager or something similar reviewing the database permissions. Or maybe someone doing an entitlement review (checking to make sure everyone has the permissions they need, and just the permissions they need).

And, if you’ve made it this far, you read the first line and you know that the permission required is VIEW DEFINITION. What’s interesting is that this permission is usually used to grant someone the ability to look at the code behind T-SQL code objects. SPs, Views, Functions etc. But, it turns out that principals also have the VIEW DEFINITION permission.

Kenneth has a few notes for this as well, so check it out.

Comments closed

Removing The Windows Container Service

Published 2018-09-19 by Kevin Feasel

Melody Zacharias shows us how to remove the Windows container service from a machine:

This week I wanted to try out a new product from a vendor.  I thought I may use it for a demo so wanted to be able to run it on my laptop.  You never know when you go to do a demo and cannot access Azure, so I try to always put my demos on my laptop as a backup; just in case….

The product required me to remove all previous versions of windows containers on my machine.  The vendor recommended I use this posh command.

Remove-WindowsFeature Containers

I was fairly sure I did not have any but wanted to check to make sure.

Melody walks through a few tricky issues, including the difference between the command on Windows 10 versus Windows Server.

Comments closed

Running Google Translate Inside SQL Server ML Services

Published 2018-09-19 by Kevin Feasel

David Fowler shows us how to use SQL Server Machine Learning Services to call the Google Translate API via Python:

In my recent post, Installing External Modules into SQL Server’s Python I had a look at just how simple it is to import external modules into Python so that they can be used within SQL Server.

In this post I’d like to show you a little something to demonstrate how we can integrate one of these modules into SQL Server and just how powerful this can be.

This is really just for fun and it may not really be something that you’d want to put out into production but when I happened to notice that there’s a Python module that interfaces with Google Translate, I wondered to myself if it’d be possible to write a procedure that could take a string and automatically translate it into our native language.

There are a couple of setup steps but once you get past them, it’s easy going.

Comments closed