Press "Enter" to skip to content

Curated SQL Posts

Power BI Field Parameters and None Options

Barney Lawrence votes None of the Above:


Field Parameters
 are one of my favourite recent additions to Power BI. The ability to turn a single chart into potentially dozens changes the way we think about putting variations of visuals on the page. It was a real wow moment for a client recently when I showed how field parameters for 5 fields and 5 measures could produce a single report page that replaced 25 of their existing reports.

While they theoretically don’t allow you to do much that you couldn’t previously with a disconnected slicer and a lot of DAX they build it faster and without the need to get heavily in to coding DAX. Anything that lowers the difficulty bar for users trying to make the most out of Power BI is a good thing in my book.

There are a couple of issues Barney has with them as they stand now but there are workarounds.

Leave a Comment

Interpreting Kernel SHAP

Michael Mayer digs into Kernel SHAP:

In their 2017 paper on SHAP, Scott Lundberg and Su-In Lee presented Kernel SHAP, an algorithm to calculate SHAP values for any model with numeric predictions. Compared to Monte-Carlo sampling (e.g. implemented in R package “fastshap”), Kernel SHAP is much more efficient.

I had one problem with Kernel SHAP: I never really understood how it works!

Needless to say, Michael knows Kernel SHAP a lot better now, considering there’s now a kernelshap package for us.

Leave a Comment

Migrating Databases between SQL Managed Instances

Etienne Lopes performs a migration:

In this post I’m going to show a very simple way to migrate a database between two SQL Server managed Instances in Azure. I’m not a big fan of bacpac files (although I work with it when necessary) so I’ll use a different approach here. Besides, when creating a bacpac file using SSMS there are some schema validations that occur at the beginning that will abort the bacpac generation for example if the database holds three-part names inside stored procedures. While not supported in SQL Azure DB it is supported in SQL Managed Instances (as are cross-database queries), and it can be quite frustrating to experience this show stopper when using bacpac’s to migrate or copy databases between Managed Instances.

Click through for the demo. And yeah, I’ve run into limiting factors with bacpacs, such as having certificates for encrypting data (even if you back those up separately).

Leave a Comment

T-SQL Tuesday 153 Roundup

Kevin Kline musters the troops:

I received a great collection of blog posts in response to T-SQL Tuesday 153 which I kicked off on Tuesday, August 2nd – asking you to write about a conference or event that had a significant event on their life. As one of the small handful of people who attended every PASS Summit from its founding through the pandemic lockdown, I’ve witnessed so many transformational experiences firsthand.

Human beings are social creatures and though we as IT pros like to focus on hard technology skills first and foremost, I think we can all admit that having a great social experience at a conference like the PASS Summit in North America, SQLBits in the UK, or Data Platform Summit in India is at least as important as the technical learning.

Read on for a summary of several posts.

Leave a Comment

Backup Jobs and Dropped Databases

Chad Callihan reasons through a use case:

I’m a big fan of Ola Hallengren’s SQL Server maintenance scripts and would recommend that anyone working with SQL Server check them out. They have served me well over the years. As it relates to today’s blog post, maybe too well…

I recently ran into a strange situation with the DatabaseBackup stored procedure that had me scratching my head: a backup job completing successfully for a database that didn’t exist.

Confused? So was I. Let’s take a look at how it happened.

Click through for the scenario.

Leave a Comment

Adding an Existing Data Factory to GitHub

Andy Leonard has a three-parter for us. Part 1 shows you how to create a GitHub account and repo:

The unabridged topic of source control with github is beyond the scope of this post. There are a number of ways to accomplish the tasks described in this post and series. I welcome your suggestions in the comments.

This post is written to help Azure Data Factory developers get started using github.

Part 2 connects a Data Factory to the repository:

For the purposes of this demo, accept the defaults for “Publish branch” and “Root folder.” Check the “Import existing resources to repository” checkbox under the “Import existing resource” property, select the main branch in the “Import resource into this branch” property, and then click the “Apply” button:

Part 3 handles changes:

Applying what we’ve configured and learned thus far, let’s put this to work in a code-management workflow.

When it’s time to make a change, first create a new branch. I can hear some of you thinking, “Why, Andy? Why create a new branch?” That’s an excellent question. I am so glad you asked! Think of the new branch as a temporary copy of the current state of my Azure Data Factory. 

This series works from the assumption that you don’t have any real experience with Git (or GitHub) for source control, and maybe not much source control experience at all.

Leave a Comment

KQL StartOf Functions

Robert Cain continues a series on KQL:

In the previous post, Fun With KQL – DateTime Arithmetic, we had hard coded a date for the start of the year, in order to find out how much time had elapsed between it and datetime columns. I had mentioned there are ways to dynamically calculate such values.

In this post we’ll look at one way, using the StartOf... functions. These include startofyearstartofdaystartofmonth, and startofweek.

Read on to see how they all work.

Leave a Comment

From Kafka to Azure Data Explorer with Protobuf Data

Anshul Sharma and Ramachandran G do a bit of converting:

Kafka is increasingly become a popular choice of scalable message queueing for large data processing workloads. This makes it very popular in IoT based ecosystem where there is large ingress in data before data processing (or) data storage. Azure Data Explorer  is a very powerful time series and analytics database that suits IoT scale data ingestion and data querying.  

Kafka supports ingestion of data in multiple formats including JSON, Avro, Protobuf and String. ADX supports ingestion of data from Kafka into ADX in all these formats. Due to excellent schema support, extensibility to various platforms and compression, [protobuf](https://developers.google.com/protocol-buffers) is increasingly becoming a data exchange choice in IoT based systems. The ADX Kafka sink connector leverages the Kafka Connect framework and provides an adapter to ingest data from Kafka in all these formats. 

The following section aims to provide configuration to support ingestion of protobuf data from Kafka to ADX. 

Click through for the high-level architecture and a deeper dive into the process.

Leave a Comment

Improvements to Parameter Sensitive Plan Optimization

Erik Darling is not good at being on vacation:

Several weeks back, I blogged about a missed opportunity with the new parameter sensitive plan feature. At the time, I thought that there was indeed sufficient skewness available to trigger the additional plan variants, and apparently some nice folks at Microsoft agreed.

If we step back through the old demo, we’ll get different results.

Click through for those results.

Leave a Comment