Press "Enter" to skip to content

Author: Kevin Feasel

SQL ConstantCare Population Analysis

Published 2022-12-29 by Kevin Feasel

Brent Ozar looks at the ConstantCare customer base:

Companies are leapfrogging right past SQL Server 2017. I’m going to hazard a guess that SQL Server 2017 came out too quickly after 2016, and didn’t offer enough features to justify upgrades from 2016.

Does that offer us any lessons for SQL Server 2022? Is 2022 going to be a 2017-style release that people just leapfrog over? Well, as I write this, it’s late December 2022, and I’m not seeing the widespread early adoption that I saw for 2019 where people had it in development environments ahead of the release, learning how to use it.

It’s an interesting analysis of a particular market segment: users of the ConstantCare service.

1 Comment

Importing Excel Files from SharePoint into Power BI

Published 2022-12-29 by Kevin Feasel

Gogula Aryalingam gets some data:

I’ve been asked the question many times, especially when I teach Dashboard in a Day: How do you import data from a bunch of Excel files stored on a SharePoint folder?

It’s a fairly simple process. It’s just that you need to know that you first connect to the SharePoint site (which could have quite a large number of folders and files, and several document libraries. You will need to know the path of your files, and then you keep filtering till you get to your content. I do this in two steps:

Read on for those steps.

Comments closed

Closures in Scala (and All FP Languages)

Published 2022-12-28 by Kevin Feasel

Pallav Gupta explains what a closure is:

Objects are more flexible for certain use cases because they carry both data members and member functions, whereas a function does not have data members.

So if there is a requirement to pass data members along with functions, How will we achieve it in functional programming ?

The answer is yes, we can achieve it using a closure and a free variable.

Read on for an example.

Comments closed

Frames and Tiles in mapBliss (R)

Published 2022-12-28 by Kevin Feasel

Benjamin Smith updates an R package:

The mapBliss package is a R package which I developed which allows for users to make custom souvenir quality maps of their flights, road trips and favorite cities by utilizing the power of the leaflet and other R packages (for a full list, see the Github README here). The goal of the package is to imitate the visualization and print-ability of maps produced by businesses like Atlas.co(my original inspiration), TheLittlePen, Mapiful and MaptracksMe (among many other such businesses).

It’s an interesting-looking package.

Comments closed

Finding Blockers in Azure SQL DB + MI

Published 2022-12-28 by Kevin Feasel

Jose Manuel Jurado Diaz writes a program:

Today, I worked on a service request that our customer is looking for all blocking issues that is happening in their database. We have many articles about it Lesson Learned #22: How to identify blocking issues? – Microsoft Community Hub and in Diagnostics Settings and QDS we can collect this information but all points to that we cannot see the TSQL that is blocking and TSQL command that is blocked in an easy way. In the following script that I share as a script example we could take this one. 

First of all, please, remember that a blocking issue is normal and fundamental for any RDBMS. This script is basically when you need to understand what is happening to improve or reduce this.

I was a little surprised the answer wasn’t to use Extended Events, though this does work if you simply need something to run in ad hoc scenarios.

Comments closed

Moving Stack Overflow to Azure

Published 2022-12-28 by Kevin Feasel

Aaron Bertrand gets into the whats and wherefores:

Like many companies, Stack Overflow is trying to get out of the business of running our architecture in our own data centers; instead, we want to offload some of the more mundane parts of system administration to a cloud service offering like Azure.

I’m going to cut to the chase for the purpose of this article and concede we’ve already decided on Azure for the majority of our infrastructure and, most importantly to me, our databases.

Click through to learn what their plan is and why Aaron & co went that particular route.

Comments closed

Avoiding Problems with DAX Window Functions

Published 2022-12-28 by Kevin Feasel

Jeffrey Wang shares a few tips to avoid issues with window functions in DAX:

Several people had reported running into errors when trying the window functions on fact tables. Let’s look at an example by first adding a calculated table of three columns to the model that is defined by the following DAX expression, and then, add all three columns to a table visual as shown in Figure 1.

Read on for more. This is an area where expected behavior does differ from what you get with window functions in T-SQL.

Comments closed

Interpreting Linear Models with SHAP

Published 2022-12-27 by Kevin Feasel

Michael Mayer answers a question:

XGBoost models are often interpreted with SHAP (Shapley Additive eXplanations): Each of e.g. 1000 randomly selected predictions is fairly decomposed into contributions of the features using the extremely fast TreeSHAP algorithm, providing a rich interpretation of the model as a whole. TreeSHAP was introduced in the Nature publication by Lundberg and Lee (2020).

Can we do the same for non-tree-based models like a complex GLM or a neural network? Yes, but we have to resort to slower model-agnostic SHAP algorithms:

Read on for examples of those algorithms and an example of interpretation and analysis.

Comments closed

Row-Level Security and Data Migration

Published 2022-12-27 by Kevin Feasel

Forrest McDaniel shares an interesting case of using row-level security:

This was the situation I found myself in earlier this year – our company had absorbed another, and it was time to slurp up their tables. There were a lot of decisions to make and tradeoffs to weigh, and we ended up choosing to trickle-insert their data, but make it invisible to normal use until the moment of cutover.

The way we implemented this was with Row Level Security. Using an appropriate predicate, we could make sure ETL processes only saw migrated data, apps saw unmigrated data, and admins saw everything. To give a spoiler: it worked, but there were issues.

I would not have thought of this scenario. And given the difficulties Forrest & crew ran into, it might be for the best…

Comments closed