Press "Enter" to skip to content

Author: Kevin Feasel

The CHOOSE Function in SQL Server

Published 2025-04-22 by Kevin Feasel

Louis Davidson chooses the form of our destroyer:

I preface a lot of what I write with whether or not it is for a “practical” use. One of the Simple Talk authors used this function in a forthcoming article. and I realized I hadn’t heard of it before (or I forgot about it… which is not completely unlikely.) The practical use was to generate some data and have at least a little variety to the values.

The CHOOSE function has this syntax.

CHOOSE(item_to_choose, item1, item2 [,item3]…[itemN])

In fairness to Louis, CHOOSE() was one of those additions to SQL Server 2012 that we mostly forgot about. It’s similar in that vein to IIF() if you weren’t living in Excel at the time. In any event, Louis takes CHOOSE() through its paces, showing some useful scenarios and stretching the limits to see what happens.

Comments closed

Dynamic Pareto Analysis in Power BI

Published 2025-04-22 by Kevin Feasel

Marco Russo and Alberto Ferrari get us to 80%:

The Pareto analysis is an analytical technique used to identify the most impactful elements within a dataset, based on the principle that a small proportion of causes often leads to a large proportion of effects. For example, the ABC Classification in DAX Patterns is also based on the Pareto principle. However, typical implementations often face limitations. The analysis based on the Pareto principle commonly uses categorical axes, such as customer names or identifiers, making it impossible to leverage a continuous axis on a Power BI line chart. A categorical axis creates a scrollbar on the line chart when there are too many data points, limiting the ability to compare the distribution of data points in different categories within the same line chart.

This article shows how to overcome this limitation by introducing a numeric axis reflecting each item’s position based on the selected measure.

Read on to see how it all works.

Comments closed

Designing a Microsoft Fabric Workspace

Published 2025-04-22 by Kevin Feasel

Ron L’Esteve lays it out:

When planning a Data Platform for your organization on Microsoft Fabric, you need to consider workspaces during your design process. Proper workspace design is critical for the organization and consumption of Fabric items. Understanding how to effectively manage Microsoft Fabric workspaces can streamline your processes.

If your workspaces are not set up in a way that aligns with functional business verticals or environments (such as Dev/UAT/Prod), you will end up spending a significant amount of time and effort re-factoring this technical debt to meet the desired organizational structures. While it seems trivial to simply move a report, pipeline, or other workload from one workspace to another, the inbuilt dependencies can often be complex. With efficient planning and design efforts, these problems can be avoided.

Click through for Ron’s advice.

Comments closed

Query Memory Utilization of Distinct Count Measures in Power BI

Published 2025-04-21 by Kevin Feasel

Chris Webb does the math:

The series of blog posts I wrote last year on semantic model memory usage, in particular this post on the query memory limit and the “This query uses more memory than the configured limit” error in Power BI, gets a lot of traffic. Since writing that post on the query memory limit I’ve written a few follow-ups on common mistakes that lead to increased query memory usage, such as this one on measures that never return a blank. Today’s post is sort of in that series but it isn’t about a design mistake – it’s just to point out that distinct count measures can be surprisingly memory-hungry.

Read on for Chris’s findings and the explanation, as well as a couple of potential workarounds if you find yourself in this situation.

Comments closed

Spring Cleaning for Lakehouse Tables with VACUUM

Published 2025-04-21 by Kevin Feasel

Chen Hirsh says it’s time to do a bit of cleanup:

Delta tables create new files for every change made to the table (insert, update, delete). You can use the old files to “time travel” – to query or restore older versions of your table. This is a wonderful feature, but over time, these files accumulate in your storage and will increase your storage costs.

Read on for a primer of the VACUUM command, how frequently you might want to run the command, and how much data you want to save. This example is specifically around using Databricks, but the mechanisms work exactly the same for other lakehouses like Microsoft Fabric.

Comments closed

Building a Microsoft Fabric Notification Bot for Teams

Published 2025-04-21 by Kevin Feasel

Stepan Resl gets the message:

Power BI provided a wide range of notification options, each with its own advantages and disadvantages, often requiring us to combine them to achieve the desired result. Historically, we primarily relied on Subscriptions, Alerts, Outages Emails, and Refresh Error Emails. Combining these options was complex and inefficient. With the introduction of the ExecuteQuery endpoint in the Power BI REST API, entirely new possibilities opened up. By sending a DAX query to a selected dataset and processing the returned data, we can create virtually any custom notification, enabling data-driven notifications. This endpoint also came with built-in support for Microsoft Power Automate and Logic Apps, significantly simplifying the process due to their native connectors to other services like Exchange, SharePoint, and Microsoft Teams (including support for Adaptive Cards).

Read on for a bit of history, why a custom solution was necessary for Stepan, and the process of building out that solution.

Comments closed

ReplaceInName in dbatools’ Backup-DbaDatabase

Published 2025-04-21 by Kevin Feasel

Jess Pomfret discovers a new flag:

Recently I was reading the docs for `Backup-DbaDatabase` and found a parameter I didn’t realise existed, but is so useful when you want to automate backups, but keep control of the file names.

Click through to learn more about the specific feature, as well as a reminder that it’s a good idea to read through the documentation. Not all documentation is good, but the work people have put into dbatools means that there is often a good example involving most of the available parameters. It turns out that there are, in fact, two examples that use -ReplaceInName in the documentation for Backup-DbaDatabase, so you get not only a description of the parameter but also two specific examples of how to use it.

Comments closed

Troubleshooting a Slow Mapping Data Flow in Azure Synapse Analytics

Published 2025-04-21 by Kevin Feasel

Reitse Eskens has the need for speed:

The issue was quite straightforward. The client has a mapping data flow in Synapse that processes a few hundred to a few thousand rows but takes 15 minutes to complete. The low number of rows compared to the time necessary is a cause for concern.

The data extraction needs a staging storage account where the data is written into TXT files. The second step of the mapping data flow reads the TXT files and writes them out in delta format, which is Parquet files.

The source is an S4Hana CDC table, the target of which is a regular Azure storage account.

Read on for Reitse’s summarization of the troubleshooting and testing process, as well as what ended up working for this customer.

Comments closed