Press "Enter" to skip to content

Curated SQL Posts

The NOT IN Operator in R

Published 2024-11-05 by Kevin Feasel

Steven Sanderson does not want these things:

In R programming, data filtering and manipulation are needed skills for any developer. One of the most useful operations you’ll frequently encounter is checking whether elements are NOT present in a given set. While R doesn’t have a built-in “NOT IN” operator like SQL, we can easily create and use this functionality. This comprehensive guide will show you how to implement and use the “NOT IN” operator effectively in R.

Read on for examples of how to use %in% and its corollary ! (...) %in%.

Leave a Comment

Fabric List Connections API in Semantic Link Labs

Published 2024-11-05 by Kevin Feasel

Sandeep Pawar has an update for us:

In you case you missed it, List Connections Admin API is now live in Fabric. It was shipped in Semantic Link Labs v 0.7.4 a few weeks ago but at the time of the release it was still private. This API returns all the connections set up in the tenant and requires admin privileges. I still can’t find documentation on it so wait for the official details. Note that this API is different from item – list connection API which lists connections used by an item.

Read on to see what you can get from it.

Leave a Comment

Bullet Charts in Power BI Reports

Published 2024-11-05 by Kevin Feasel

Kurt Buhler is number one with a bullet (chart):

A report visual is useful when it displays information in a meaningful context. This context refers to other relevant data that helps someone interpret figures in a visual and use it to make decisions or take actions. The most common way to provide context is to compare actuals to a target.

There are many ways to compare actuals to a target, and in this article, we will explain in detail one way to do this by using a popular variant of the bar chart known as a bullet chart.

Kurt lays out several ways to implement a bullet chart in Power BI, so check that out.

Leave a Comment

Column Eviction in Power BI and Direct Lake

Published 2024-11-05 by Kevin Feasel

Paul Turley talks about fashion:

One of the core best practice guidance principals for Power BI modeling is to avoid including columns that aren’t absolutely necessary for analytic reporting. Every column uses precious memory and especially long, unique values that don’t compress very well. When consulting clients bring me large models that require expensive capacity licensing and pose report performance issues, my first inclination is to see what column data can be carved out of the model; and perhaps moved to another table for a drill-through report.

The product team came up with a very clever way to reduce the in-memory footprint of a Direct Lake semantic model: hold a popularity contest! The semantic model engine will only keep columns in memory based on their hotness. I mean this literally…

Read on to learn a bit more about the algorithm in play and how it differs from a naive Least Recently Used cache.

Leave a Comment

Showing SSRS Reports in Modern .NET Apps

Published 2024-11-05 by Kevin Feasel

Sebastiao Pereira solves a problem:

Report Viewer was originally developed for the .NET Framework. As the industry shifts towards .NET Core, developers who have traditionally relied on this tool have faced challenges displaying reports within their applications due to compatibility issues. Is it possible to display a report from SQL Server Reporting Services (SSRS) in a .Net Core Application?

Click through for the answer. As a quick note, we had .NET Core, but then Microsoft renamed it to .NET with .NET 6, so instead we have to differentiate .NET Framework (Windows-only, heavy SDK) with .NET (nee .NET Core, cross-platform, less heavy). I’d rate Sebastiao’s solution a workaround, but one that I doubt Microsoft will ever provide a better solution for, given the heavy de-emphasis on Reporting Services over the past several years.

Leave a Comment

Enabling System Tables on Databricks

Published 2024-11-04 by Kevin Feasel

Chen Hirsh wants to see system tables:

This post is about two things that are dependent on each other. First, it explains how to enable system tables (other than the one enabled by default) and second, how to use these system tables to view data about workflow runs and costs.

Please note that Unity Catalog is required to use these features. And a premium workspace is required for using dashboards.

Click through to learn more about what system tables are and what you can get from them.

Leave a Comment

Administrative Tasks in Azure Database for MySQL Flexible Server

Published 2024-11-04 by Kevin Feasel

Rajendra Gupta gives us a checklist:

The tip, Azure Database for MySQL, explored various deployment models for Azure MySQL and their features. Further, we deployed an Azure MySQL flexible server using the Azure portal. This tip will explore the tasks and operation items required for a MySQL flexible server. Let’s check it out.

Read on for notes regarding what Microsoft gives you up-front as well as what you, as an administrator, would still need to cover.

Leave a Comment

The Power of pg_dump

Published 2024-11-04 by Kevin Feasel

Robert Haas talks up pg_dump:

I wrote a blog post a couple of weeks ago entitled Is pg_dump a Backup Tool?. In that post, I argued in the affirmative, but also said that it’s probably shouldn’t be your primary backup mechanism. For that, you probably shouldn’t directly use anything that is included in PostgreSQL itself, but rather a well-maintained third-party backup tool such as barman or pgbackrest. But today, I want to talk a little more about why I believe that pg_dump is both amazingly useful for solving all kinds of PostgreSQL-related problems and also just a great piece of technology.

The core value proposition of pg_dump is that the output is human-readable text. You’ll get DDL commands that you can use to recreate your database objects, and you’ll get COPY commands (or INSERTs, if you so request) that you can use to reload your table data. That is not really an advantage if you’re just trying to back up and restore an entire database cluster, because converting all of your data from PostgreSQL’s internal formats into text and back again is going to use a bunch of CPU resources. If you instead take and restore a physical backup, you can avoid all of that overhead.

Read on for scenarios in which pg_dump can be quite useful.

Leave a Comment

Notes on Bursting and Monitoring in Microsoft Fabric

Published 2024-11-04 by Kevin Feasel

Joey D’Antoni puts in a lot of work and then coasts for a few hours:

Bursting and smoothing is a concern for a lot of Fabric admins—it’s somewhat unpredictable in terms of how large the initial burst is, and how long the smoothing process takes. One problem that a number of both MVPs and customers have observed, is Fabric effectively being unavailable for things like dashboard refreshes for long periods of time. Microsoft describes this process in docs as the following:

“When a scale factor is over 1, it means that burstable capacity is being used to meet the demands of the workload. It also means that your workload is borrowing capacity units from a future time interval. This is a fundamental concept of Microsoft Fabric called smoothing.

Smoothing offers relief for customers who create sudden spikes during their peak times, while they have a lot of idle capacity that is unused. Smoothing simplifies capacity management by spreading the evaluation of compute to ensure that customer jobs run smoothly and efficiently.”

Read on to learn more about this process and how it can be tricky for administrators to manage.

Leave a Comment

Lexing DAX with PyDAX

Published 2024-11-04 by Kevin Feasel

Sandeep Pawar reviews a DAX lexer:

The power of open-source and GenAI. Klaus Jürgen Folz recently open-sourced the PyDAX library, which parses DAX expressions to extract or remove comments, and identify referenced columns and measures. I used that library to create some demos for myself and then shared the notebook along with instructions with Replit agents to build an app for me.. 15 minutes & 3 prompts later I had a fully functional app. Give it a try : https://daxparser.replit.app/

Read on to learn more, including why I referred to PyDAX as a “lexer” and a few more notes of relevance.

Leave a Comment