Press "Enter" to skip to content

Curated SQL Posts

Operating the Data Wrangler in Microsoft Fabric Notebooks

Published 2023-12-01 by Kevin Feasel

Gilbert Quevauvilliers rustles up some data:

In this blog post I am going to show you an easy way to clean your data (which is often fixing data issues or mis-spelt data) using the new feature Launch Data Wranger using DataFrames

I had previously blogged about using Pandas data frames but this required extra steps and details, if you are interested in that blog post you can find it here: Did you know that there is an easy way to shape your data in Fabric Notebooks using Data Wrangler?

In this blog post I am going to show you how I cleaned up the data in my location column.

Read on for a demonstration of what you can do.

Comments closed

Preemptive vs Non-Preemptive Waits in SQL Server

Published 2023-12-01 by Kevin Feasel

Chad Callihan interrupts this broadcast for an important message:

Have you ever been reviewing SQL Server waits and spotted any that had PREEMPTIVE in the name? There about 200 variations of PREEMPTIVE wait types out there, so I’m guessing you’ve seen at least one at some point in your career. They don’t always get the same type of attention as a CXPACKET wait or a THREADPOOL wait, so perhaps you never took the time to find out what they mean. Let’s discuss PREEMPTIVE waits as well as what NON-PREEMPTIVE means.

Read on for the answer.

Comments closed

The theme() Function in ggplot2

Published 2023-11-30 by Kevin Feasel

Jack Kennedy shows off a function:

The theme() function in {ggplot2} is awesome. Although it’s only one function, it gives you so much control over your final plot. theme() allows us to generate a consistent, in-house style for our graphics, modify the text within our plots and more. Getting comfortable with theme() will really take your {ggplot2} skills up a notch.

Theming visuals can have an outsized impact on how easy the output is to understand, so understanding how theme() works is important. Also, if your company has specific theming or marketing standards, you can usually build them with the theme() function and then save that theme for reuse later.

Comments closed

Robust Regression in R

Published 2023-11-30 by Kevin Feasel

Steven Sanderson performs robust regression:

If you’re familiar with linear regression in R, you’ve probably encountered the traditional lm() function. While this is a powerful tool, it might not be the best choice when dealing with outliers or influential observations. In such cases, robust regression comes to the rescue, and in R, the rlm() function from the MASS package is a valuable resource. In this blog post, we’ll delve into the step-by-step process of performing robust regression in R, using a dataset to illustrate the differences between the base R lm model and the robust rlm model.

The short version of rlm() versus lm() is that Ordinary Least Squares (the form of linear regression we use with lm()) is quite susceptible to outliers. Meanwhile, rlm() uses a technique known as M-estimation, which ends up weighting outlier points different from inliers, making it less susceptible to a small number of outliers wrecking the chart.

Comments closed

Microsoft Fabric and Tabular Editor

Published 2023-11-30 by Kevin Feasel

Johnny Winter is excited:

Why the excitement on my part? Well to take advantage of all the great features in Tabular Editor, you really need to be able to connect and write via XMLA, be that for doing CI/CD pipelines or by making edits directly on the dataset.

What great new features does Tabular Editor unlock that you can’t just do in the online Power BI modelling experience in Fabric… tons!

Read on to see how Tabular Editor plays with Microsoft Fabric.

Comments closed

Script Comparison with kdiff3

Published 2023-11-30 by Kevin Feasel

Steve Jones is speaking my language:

I had a customer recently ask if SQL Compare could show them the differences in two scripts they’ve written. They weren’t using version control (tsk, tsk, shame), but saw SQL Compare and the “Scripts folder” option. This isn’t used for random scripts, but I do have a better solution: KDiff3.

KDiff is an old project that is used to analyze multiple files and merge the differences. There is an archived SoundForge location, but the more modern version is here. That’s the current code location, and you can see the readme for details. To get started, download and install it.

I remember (cue “Pepperidge Farms Remembers” meme) back when kdiff3 was only available in KDE. That’s when I first learned of it, and ever since there was a Windows port, I’ve been a dedicated user. Yes, it’s an old tool, but it works really well.

Comments closed

SSMS Tips and Tricks

Published 2023-11-30 by Kevin Feasel

Vlad Drumea shares a few tips around SQL Server Management Studio:

In this post I cover my favorite SSMS tips and tricks that I’ve picked up along the years, and on which I rely on a daily basis in my workflow.

If you’re interested in my SQL Server Management Studio configuration recommendations – check this post out. This also contains how to have two rows of Query Editor tabs, so I’m not covering that here again.

You can find the latest version of SSMS on the official download page.

This is a great list of items and if you’re a daily driver of SSMS, you’ll want to check it out.

Comments closed

Reviewing an Execution Plan for a Window Function

Published 2023-11-30 by Kevin Feasel

Andy Brownsword categorizes the components:

Using last week’s sample data we can run the query below to demonstrate operators typically used for a window function:

The result of this query is a set of data with a running total of the Sales Value within each Financial Quarter.

We’ll follow the data through some of the operators in this execution plan to understand their part in the function. As with regular execution plans we’ll be working from right to left.

Read on for the key operators.

Comments closed

Power Regression in R

Published 2023-11-29 by Kevin Feasel

Steven Sanderson’s power level is over 9000:

In the realm of statistics, power regression stands out as a versatile tool for exploring the relationship between two variables, where one variable is the power of the other. This type of regression is particularly useful when there’s an inherent nonlinear relationship between the variables, often characterized by an exponential or inverse relationship.

Read on to learn more about the definition of power regression and how to perform it in R using a technique called “swole linear regression.” Or at least that’s what I think the technique should be called. Which is probably why I’m not in charge of naming things.

Comments closed