Press "Enter" to skip to content

Day: May 17, 2021

Plotting Correlation Analyses in R

Finnstats shows a few techniques for plotting correlation in R:

Correlation analysis, correlation is a term that is a measure of the strength of a relationship between two variables.

Pearson’s Product-Moment Correlation

One of the most common measures of correlation is Pearson’s product-moment correlation, which is commonly referred to simply as the correlation, or just the letter r.

Correlation shows the strength of a relationship between two variables and is expressed numerically by the correlation coefficient.

Click through for examples from several packages. H/T R-Bloggers.

Comments closed

Errors and Return Codes in SQL Agent Powershell Job Steps

Ron the Polymath has a framework:

PowerShell job steps offer a lot of advantages, but when things don’t work as expected, it can frustrating to understand why. Things like when a non-zero exit code reports the step as successful. Some important points I found with PowerShell steps (especially the first item):

Read on for those interesting points, for a block of Powershell code you can use to track errors, and a SQL Agent job template to boot.

Comments closed

Counts of Last-Known States of Items with DAX

Phil Seamark has an interesting problem:

The requirement was simple enough. Take the following dataset and, for any given day, produce a count of each possible State using the last known State for any given TestID. The dataset contains six unique Test IDs (A through F). At any given point in time, we first want to establish the last State for each TestID. We also want to group this by day and produce a count value for each possible State. Note, a given TestID can have more than one event in a day, and we only care about the last one.

I’m particularly interested in this because I find a lot of merit in the event-based structure in Phil’s input dataset, but it can be tricky going from that to data in a shape the customer likes.

Comments closed

To Cloud or Not to Cloud

That is the question, according to Guy Glantser:

This is not a regular blog post. I was looking for an old blog post that I wrote several years ago, and while searching, I found an even older blog post that I wrote back in 2009. It had the same title that you see here – To Cloud or Not to Cloud?

In 2009 the cloud was already a thing, but it was the early days. Microsoft’s cloud, Azure, wasn’t even announced yet until February 2010. The cloud has seen a tremendous advancement over the years. It’s interesting and also amusing to read what I wrote 12 years ago about the cloud. Some things are still true today, while others are completely irrelevant.

So here it is…

It’s good to reflect back on these thoughts to see how the industry has shifted. Issues which were show-stoppers may be completely eradicated by this point, while others remain trade-offs without an ideal answer.

Comments closed

What Helps with Readability of T-SQL Code

Erik Darling comes out of the gates with a hot take:

One line I see over and over again — I’ve probably said it too when I was young and needed words to fill space — is that CTEs make queries more readable.

Personally, I don’t think they make queries any more readable than derived tables, but whatever. No one cares what I think, anyway.

I’m going to split the middle of Erik’s take. Yes, using common table expressions by themselves doesn’t make a query easier to read. And yes, a good formatting technique helps a lot in readable code. Once that’s taken care of, I do think that common table expressions can be a bit more readable than their equivalent subqueries, for the reason that they do a better job of separating logically distinct segments of code. In those situations, I can read through each common table expression, getting a feeling for what they’re doing and let them tell a story with a top-to-bottom progression. This technique is most effective when you need several common table expressions for a query. By contrast, when creating subqueries (which I tend not to do much) or derived tables with APPLY (which I do so often, I get the employee discount), the story’s a little more disjointed, as the eyes seem to bounce more frequently between the main query and the subqueries. And don’t get me started on subqueries in the SELECT clause—those are the equivalent of somebody telling a story and saying, “Hang on, now I have to tell this story so that you get what I’m talking about.”

But going back to my agreement, if your code looks terrible, it doesn’t matter what constructs you use—it’s not very human-friendly.

Comments closed

Finding Text in a Stored Procedure

Chad Callihan has a way to search the text of stored procedures:

Story time. Let’s say a database server is receiving a new release that include a change to a stored procedure. All of the databases are supposed to get the changes but one way or another there are problems with the release and it has to be stopped part of the way through. Maybe some changes got rolled back but others weren’t rolled back. We don’t have accurate logging of what databases have been updated but we want to know if a stored procedure is on the old version or received the new version.

I’ve used this technique quite often. The only downside is that if you have a lot of procedures and don’t specify the object ID, search can get a bit slow.

Comments closed

The Basics of Finding Blocking

Alex Stuart has a way to find blocked processes:

So we need monitoring and alerting on it. Enterprise monitoring tools can do this, and do it well – but if you don’t have one, or don’t have enough licenses for your entire estate, you’ll need to roll your own. (OK, or copy someone else’s if you don’t need the learnin’). This post will demonstrate a basic method for detecting blocking and alerting based on a given threshold.

Read on for the process.

Comments closed