Press "Enter" to skip to content

Curated SQL Posts

Dealing with Many-to-Many Relationships in Power BI

Published 2025-10-14 by Kevin Feasel

Boniface Muchendu handles a many-to-many relationship:

Many-to-many relationships in Power BI are one of the most frequent challenges faced by new and intermediate users. These relationships can cause incorrect totals in visuals, confusing results, and slower report performance. In this guide, we’ll explore what many-to-many relationships in Power BI are, why they’re problematic, and how to fix them using the most effective methods available.

Click through for an enumeration of the problem as well as a couple of ways to resolve it.

Comments closed

Table Switching in SQL Server

Published 2025-10-14 by Kevin Feasel

Rebecca Lewis swaps in a bag of sand while snatching the golden idol:

Use ALTER TABLE … SWITCH to move very large tables instantly.  Yes.  I said instantly.  ALTER TABLE .. SWITCH doesn’t copy the data or physically move it.  It just reassigns the page ownership.  This means that only the metadata with the data pointer changes, and that’s why it completes in milliseconds and barely touches the transaction log.

There are some strict conditions around when you can use this technique, but it’s as powerful as Rebecca mentions.

Comments closed

API Interaction with OneLake Tables

Published 2025-10-14 by Kevin Feasel

Matthew Hicks makes an announcement:

Microsoft OneLake is the unified data lake for your entire organization, built into Microsoft Fabric. It provides a single, open, and secure foundation for all your analytics workloads – eliminating data silos and simplifying data management across domains.

The preview of Microsoft OneLake Table APIs, a new way to programmatically manage and interact with your data tables in OneLake! These APIs open the door for developers and data engineers to integrate OneLake seamlessly into their workflows, enabling powerful automation and interoperability with open table formats.

Read on to see what’s available in the initial preview. It’s interesting that they started with Iceberg rather than Delta Lake.

Comments closed

User-Defined Input Screens for Power Apps

Published 2025-10-14 by Kevin Feasel

Jon Vöge lets users choose their own adventures:

To give a more specific example, I faced this requirement with a customer who used a Power App for Key Account Management data.

Key Accounts would be sorted into different categories, and each category of Key Accounts had slight variations in the input fields required. And the real kicker was that new Categories of accounts would frequently be added, meaning a new set of questions for this new category of accounts had to be defined.

In the ideal world, I wanted to remove the Power Apps developer from the equation, and let the users themselves define new questions as the need arises.

How would you solve this?

Click through for a solution using Power Apps.

Comments closed

Diagnosing a Partition Job Failure after Migration to an AG

Published 2025-10-13 by Kevin Feasel

Mike Lynn describes a customer issue:

Quick Summary

A client noticed one of their reporting tables wasn’t logging any new information after the first of the new month.

Context

This environment ran on SQL Server 2019 in an Always On Availability Group configuration hosted on AWS EC2 servers. This is roughly 30-45 days after the servers were migrated from a SQL Server Failover Cluster Instance in AWS on EC2 to the new AG setup.

Read on for the problem, the discovery process, and the solution. I like reading this sort of report specifically to focus on the process. One of the best skills you can develop in any technical field is the practice of methodical behavior: review and understand the error message (perhaps with the assistance of a search engine or tool of choice), then work logically through possible issues until you discover the cause. It sounds obvious when I describe it that way, but far too often, people flail about and try a variety of arbitrary things because they don’t really understand the issue and hope that doing this one thing will fix whatever problem is happening.

Comments closed

Trying the Graph Database in Microsoft Fabric

Published 2025-10-13 by Kevin Feasel

Chris Webb gives the graph database in Microsoft Fabric a try:

The new Fabric Graph database is now rolling out now and should be available to everyone within the next few weeks if you can’t see it already. The key to learning a new data-related technology is, I think, to have some sample data that you’re interested in analysing. But if you’re a Power BI person why would a graph database be useful or interesting? Actually I can think of two scenarios: analysing dependencies between DAX calculations and the tables and columns they reference using the data returned by INFO.CALCDEPENDENCY function (see here for more details on what this function does); and the subject of this blog post, namely analysing Import mode refresh job graphs.

Read on for Chris’s example.

Comments closed

Reviewing Security Drift in SQL Server Reporting Services

Published 2025-10-13 by Kevin Feasel

Andy Brownsword checks out who has access to what permissions:

Item level roles are what we’re digging into here. Before we start, it’s worth defining a simple security model so it’s applied consistently. Let’s be real, the instance might not have a long term future but let’s do it right at least, eh?

Read on for a few high-level suggestions, details on what permissions do not carry over from parent objects, and more.

Comments closed

Migrating from Apache Airflow 2 to 3 on Amazon MWAA

Published 2025-10-10 by Kevin Feasel

Anurag Srivastava, et al, perform a migration:

Apache Airflow 3.x on Amazon MWAA introduces architectural improvements such as API-based task execution that provides enhanced security and isolation. Other major updates include a redesigned UI for better user experience, scheduler-based backfills for improved performance, and support for Python 3.12. Unlike in-place minor Airflow version upgrades in Amazon MWAA, upgrading to Airflow 3 from Airflow 2 requires careful planning and execution through a migration approach due to fundamental breaking changes.

This migration presents an opportunity to embrace next-generation workflow orchestration capabilities while providing business continuity. However, it’s more than a simple upgrade. Organizations migrating to Airflow 3.x on Amazon MWAA must understand key breaking changes, including the removal of direct metadata database access from workers, deprecation of SubDAGs, changes to default scheduling behavior, and library dependency updates. This post provides best practices and a streamlined approach to successfully navigate this critical migration, providing minimal disruption to your mission-critical data pipelines while maximizing the enhanced capabilities of Airflow 3.

Read on to see what has changed between these two major versions of Airflow, recommendations on what to look out for, and a step-by-step migration guide.

Comments closed

Resolving Write Conflicts in Microsoft Fabric Data Warehouse

Published 2025-10-10 by Kevin Feasel

Twinkle Cyril has a conflict:

Fabric Data Warehouse (DW) supports ACID-compliant transactions using standard T-SQL (BEGIN TRANSACTION, COMMIT, ROLLBACK) and uses Snapshot Isolation (SI) as its exclusive concurrency control model. All operations within a transaction are treated atomically—either all succeed or all fail. This ensures that each transaction operates on a consistent snapshot of the data as it existed at the start of the transaction, which means.

Read on to see what this means, as well as what happens when multiple writers interfere with one another and how to avoid these sorts of issues. My Kimball-coded brain says that, if you have a data warehouse, you should have one data loading process. In that case, it’s not easy for the single data loading process to get tripped up on its own.

Comments closed