Press "Enter" to skip to content

Author: Kevin Feasel

Concurrent Index Creation in Postgres

Published 2024-08-13 by Kevin Feasel

Shayon Mukherjee recommends against a particular technique:

As a developer, you might have encountered situations where creating an index in PostgreSQL fails due to lock timeouts. In such scenarios, it’s tempting to use the IF NOT EXISTS as a quick fix and move on. However, this approach can lead to subtle and hard-to-debug issues in production environments.

Click through to learn more about how concurrent index creation works in Postgres and why the use of IF NOT EXISTS might not work the way you want.

Comments closed

Highlighting a Single Data Point in Power BI

Published 2024-08-13 by Kevin Feasel

Kurt Buhler points something out:

Effective visualizations provide context so that you can interpret the numbers and what they mean to you. Is this number bad or is it good? This is particularly important for visuals that aim to provide a quick, 3-second overview, like cards, KPIs, and simple trendlines. You can provide context by comparing to a target, but if no target is available, you can also compare to a measure of central tendency, like the average or median. However, instead of comparing to an aggregate, you might also want to compare to other categories.

Consider the following example, which shows the desired end result for this article: a plot which highlights a selected value so that the user can compare it to all others. This example uses some DAX and formatting with a line chart and scatterplot to achieve the result of a joint plot atop a jitter plot. If you want to learn more about what a joint plot or a jitter plot is, we gave an overview of these and similar chart types in a previous article.

This is something I find frustratingly difficult with Power BI. Kurt does a great job of showing how to get there, but it seems like it should be a lot easier to do.

Comments closed

Migrating an Azure Data Studio Connection List

Published 2024-08-13 by Kevin Feasel

Josephine Bush packs up and moves down the road:

I wanted to migrate some connections without recreating them and wondered how to do that in Azure Data Studio. It turns out to be easy. I post about whatever I’m working on, and here’s a short one for you today.

Read on to see how to do this on Windows and MacOS. But that leaves the most critical question: what about on Linux?

Linux follows the same pattern as Windows: File –> Preferences –> Settings, and then Data –> Connection.

Comments closed

Thoughts on Start-DbaMigration in dbatools

Published 2024-08-13 by Kevin Feasel

Chad Callihan shares an experience:

It’s time to follow up on a post from a few months ago where I said I’d give my thoughts on Start-DbaMigration from dbatools after picking up more experience. Along with Start-DbaMigration I picked up some experience with Copy-DbaDatabase to migrate databases from one server to another. Below are some thoughts on the good and bad.

Read for Chad’s scenario and some of the good and the bad of Start-DbaMigration.

Comments closed

Service Broker Blocking on DisableQ

Published 2024-08-13 by Kevin Feasel

Tom Zika is angling to become the Service Broker Baron:

I was paged about a blocking chain where the blocked resource was a Service Broker queue, the lead blocked transaction was called CSbRollbackHandlerTask::DisableQ, the lock mode was SCH-M and even lock partitioning was involved. I won’t repro fully this time, but I’ve covered lock partitioning repro in my post Async-stats-update-causing-blocking. The goal was to capture an instance of transaction CSbRollbackHandlerTask::DisableQ with the same lock mode.

Click through for a demo and how to troubleshoot the issue.

Comments closed

Cosmos DB HTAP into Azure Synapse Analytics and Microsoft Fabric

Published 2024-08-13 by Kevin Feasel

Paul Hernandez doesn’t want to write ETL jobs:

In the ever-evolving landscape of data management and analytics, choosing the right tools and approaches is crucial for optimizing performance and achieving business goals. Two prominent solutions that have gained traction are Azure Synapse Link for Azure Cosmos DB and Mirroring in Microsoft Fabric. Both offer unique benefits and cater to different needs, making it essential to understand their differences and use cases.

Read on to see how each of these works, as well as a quick demonstration of efficacy.

Comments closed

An Overview of Cross-Validation

Published 2024-08-12 by Kevin Feasel

Vinod Chugani explains the benefit of cross-validation in a data science project:

Many beginners will initially rely on the train-test method to evaluate their models. This method is straightforward and seems to give a clear indication of how well a model performs on unseen data. However, this approach can often lead to an incomplete understanding of a model’s capabilities. In this blog, we’ll discuss why it’s important to go beyond the basic train-test split and how cross-validation can offer a more thorough evaluation of model performance. Join us as we guide you through the essential steps to achieve a deeper and more accurate assessment of your machine learning models.

Click through for the full article.

Comments closed

Counting Character Occurrences in R

Published 2024-08-12 by Kevin Feasel

Steven Sanderson counts the ways:

Counting the occurrences of a specific character within a string is a common task in data processing and text manipulation. Whether you’re working with base R or leveraging the power of packages like stringr or stringi, R provides efficient ways to accomplish this. In this post, we’ll explore how to do this using three different methods.

Read on for three separate examples.

Comments closed

Storing Images in Kusto and Visualizing in Power BI or Data Explorer

Published 2024-08-12 by Kevin Feasel

Hauke Mallow shares what is probably a bad idea:

Kusto is a fast and scalable database designed to ingest, store, and analyze large volumes of structured and semi-structured data. For non-structured data like images, Azure Storage is typically the best choice. Databases can reference image data on storage via a URL, meaning images are not directly stored in Kusto. However, there are scenarios where storing image data in Kusto is beneficial. In this blog post, we will explore when it makes sense to store images in Kusto, how to store them, and how to visualize this data using Azure Data Explorer dashboards or Power BI.

I suppose the main benefit would be displaying images in Azure Data Explorer, as that tool might not support loading in external images from a storage account or other sane location. But this feels more like a neat parlor trick than something I’d actively recommend.

Comments closed

Avoid Mixing DATETIME with other Date Types

Published 2024-08-12 by Kevin Feasel

Paul White shares some advice:

Microsoft encourages us not to use the datetime data type: 

Avoid using datetime for new work. Instead, use the time, date, datetime2, and datetimeoffset data types. These types align with the SQL Standard, and are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

Well, ok. Sensible and well-informed people might still choose to use datetime for performance reasons. Common date and time functions have optimised implementations in the SQL Server expression service for the datetime and smalldatetime data types.

Paul has posted the full article on X.

Comments closed