Press "Enter" to skip to content

Author: Kevin Feasel

Power Query Templates

Published 2023-09-06 by Kevin Feasel

Reza Rad looks at a new feature:

Have you ever considered exporting your entire Power Query Editor project as a single object? Have you thought about what benefits this would bring for you? Things such as version control and team development can be on the horizon, bringing the ability to migrate between tools and services easily. Fortunately, such functionality exists, called the Power Query Template. In this article and video, I will explain what this is, how it works, and the importance of such a feature.

Reza is, on the whole, quite pleased with it.

Comments closed

Scale-Out Read-Only Databases in Azure SQL DB

Published 2023-09-06 by Kevin Feasel

Etienne Lopes begins a new series:

“As part of High Availability architecture, each single database, elastic pool database, and managed instance in the Premium and Business Critical service tier is automatically provisioned with a primary read-write replica and one or more secondary read-only replicas.”

Read on to see how you can add support for read-only, scale-out replicas to an existing Azure SQL Database. Just know how much that bill is going to be.

Comments closed

Debugging SQL Server Stored Procedures

Published 2023-09-06 by Kevin Feasel

Erik Darling lays out a good opinion:

This can really save your hide when you hit a reproducible issue. Proper error handling is part of proper debugging, too.

I generally detest PowerShell, but I love that some commands have a -WhatIf parameter to test what would happen. For normal SQL Server queries, you don’t get that.

But if you’re writing dynamic SQL that may execute database commands (backup, checkdb, settings changes, etc.), you should also include a parameter that prevents dynamic SQL execution so you can debug without worrying about wreaking havoc.

Read the whole thing. I might handle the specific mechanisms of debugging slightly differently, but Erik’s packed this post full of good advice.

Comments closed

The READPAST Query Hint

Published 2023-09-06 by Kevin Feasel

Chad Callihan gives us a hint:

Have you ever heard of the READPAST query hint? I would say it’s kind of a sibling to NOLOCK. With NOLOCK, you’ll get data back faster but risk dirty reads. With READPAST, SQL Server will skip right past locked data altogether. This can either be helpful or, if you don’t know how it’s used, incredibly dangerous.

Read on to see what it does. I’m not sure I’ve ever used READPAST before, but I’m also using read committed snapshot isolation whenever I can, so that would limit the utility of the READPAST hint. I don’t have the visceral disgust with READPAST that I do with NOLOCK, though I suppose the reason for that is just how uncommon the former is, whereas every bad code base I run into is inundated with NOLOCK everywhere.

Comments closed

BotChat BiWeekly

Published 2023-09-05 by Kevin Feasel

Mala Mahadevan starts a newsletter:

I do my best to find trustworthy sources to learn from, but you know how it is – sometimes it’s tough to tell what’s legit. So, if you ever see me post something that seems a bit off, please cut me some slack. These aren’t necessarily my opinions, just things that caught my eye.

What I learn is just my take on what I heard or read. It might not always jive with what the original speaker or writer means, or understand. I don’t use any fancy AI bots like ChatGPT to help me out. I just quote stuff and break it down in my own words.

Mala focuses on a pair of videos. I snuck into the newsletter with a few bomb-throwing statements, particularly around anthropomorphism (the assignment of human or human-like qualities to non-humans). Anthropomorphism is extremely common in language. It’s all well and good as metaphor, but once you start to believe it for real, that’s when you end up in trouble.

Comments closed

A Currency Conversion Pattern for DAX

Published 2023-09-05 by Kevin Feasel

Teo Lachev pulls out the money clip:

Currency conversion is a common requirement, such as when implementing analytics on top of ERP sales data recorded in multiple currencies. The most flexible approach is to allow the user to select a currency and perform the conversion at runtime, ideally by centralizing the conversion code in Power BI calculation groups. However, this approach has several shortcomings:

Read on for those shortcomings, as well as an approach Teo has to improve things.

Comments closed

Row-Level Security Setup in SQL Server

Published 2023-09-05 by Kevin Feasel

Ben Johnston configures row-level security in SQL Server:

Implementing RLS is as much a business endeavour as technical. Without a business case to implement RLS, there is no reason for the extra effort and testing involved. This is where driving out business requirements and making sure the solution fits the problem is important. Non-technical members of the team or business partners likely won’t know what RLS is or if it should be used. Differential access to the same data, replacing systems (or proposed systems) with multiple reports based on user groups, and multi-tenant access are possible indicators that RLS may be a useful tool. There are always multiple ways to solve a problem. If RLS would simplify the design and make it more robust, that’s when I start to seriously consider it for a design. It does help if the business is aware of RLS and have used it in other projects or databases, but having the business essentially design the system is dangerous too. Use all of the information available during planning sessions and design the system that best fits the need of the business and the skills of the technical team.

Read the whole thing. I’m particularly interested in this series, as I’ve liked row-level security and didn’t find any trivial workarounds or exploits, but also hate how slow it is once you’re dealing with large datasets.

Comments closed

Monitoring Azure SQL Transaction Log Usage with Powershell

Published 2023-09-05 by Kevin Feasel

Jose Manuel Jurado Diaz has a script for us:

Database administrators and IT professionals often need to be proactive in monitoring resources, especially when working in cloud environments like Azure SQL. One critical resource that requires monitoring is the transaction log of a SQL Server database. If it fills up, it can hinder database operations, leading to potential application downtime.

In this article, we’ll discuss a PowerShell script that monitors transaction log usage in Azure SQL databases and sends an email alert if a database exceeds a specific threshold.

There is a reference in the script to Check-LogUsage, which appears to be a different function, likely related to the linked T-SQL statement Jose posted.

Comments closed

Australian Azure Downtime After-Action Report

Published 2023-09-05 by Kevin Feasel

Brent Ozar shares some thoughts:

Note that 11:34, the decision was made to shut down infrastructure without Microsoft failing your databases over elsewhere. If you were an Azure SQL DB or Cosmos DB user, and you weren’t paying for replicas in another data center, it was up to you to follow Microsoft’s disaster recovery guidance.

Controversial opinion: I actually love that and I think it’s great.

That is definitely a controversial opinion, but it’s also one I agree with. Read on for more of Brent’s thoughts.

Comments closed

The Value of KPIs and Cards in Power BI

Published 2023-09-05 by Kevin Feasel

Kurt Buhler and Stepan Resl give you a card:

When a user arrives at your report, they should be able to answer their most important questions in a few seconds. To do this, we typically put the most critical information in the top-left of the report (where we often look first). This information should provide a high-level overview, whereas additional details should be placed at the bottom of the report, behind interactions, or on later pages.

An effective and popular way to call attention to important numbers in Power BI is by using cards and KPI core visuals.

Read on for several examples and a breakdown of how they work best.

Comments closed