Press "Enter" to skip to content

Month: November 2024

Open Order Computation with Visual Calculations in DAX

Published 2024-11-19 by Kevin Feasel

Marco Russo and Alberto Ferrari want to track open orders:

Open orders (or events in progress) is an extremely common pattern in business intelligence. It answers a simple question: given two dates – order received and order delivered – how many orders have yet to be delivered at any given point? We do have a pattern here: Events in progress – DAX Patterns that solves the scenario with DAX measures.

While demonstrating the pattern during a classroom course, one student (thanks Justin Duff!) asked whether the pattern could be solved by using visual calculations. It turns out that visual calculations can be of great help in optimizing the performance of this specific scenario because they greatly reduce the number of calculations required to solve it. Well… sort of. Visual calculations might perform well, but we will do better with DAX alone!

Read on for the examples and why visual calculations might not be the best fit for this scenario.

Comments closed

Creating Totals and Subtotals in Postgres with ROLLUP and CUBE

Published 2024-11-19 by Kevin Feasel

Elizabeth Christensen uses a pair of analytical operators:

Postgres is being used more and more for analytical workloads. There’s a few hidden gems I recently ran across that are really handy for doing SQL for data analysis, ROLLUP and CUBE. Rollup and cube don’t get a lot of attention, but follow along with me in this post to see how they can save you a few steps and enhance your date binning and summary reporting.

I’ve used ROLLUP on occasion, but never found a great case when CUBE made sense in a report. I am, however, quite partial to GROUPING SETS, the third of these analytical operators and the one that gives you the most control.

Comments closed

Deadlock Resolution and Prevention in SQL Server

Published 2024-11-19 by Kevin Feasel

Eitan Blumin works through some deadlock issues:

Deadlocks in SQL Server can be frustrating and can cause significant performance and reliability issues. A deadlock occurs when two or more transactions are waiting for each other to release a lock on a resource, resulting in a situation where no transaction can proceed, and eventually, one of them is automatically killed and rolled back. This can happen when two transactions try to access the same data in a different order or when one transaction holds a lock on a resource while waiting for a lock held by another transaction. In this blog post, we’ll discuss how to troubleshoot and prevent deadlocks in SQL Server.

Click through for a way to get information on deadlocks, as well as three techniques for reducing the risk of deadlocks occurring.

Comments closed

Using Kubernetes with Distributed Availability Groups

Published 2024-11-19 by Kevin Feasel

Andrew Pruski has a guide for us:

A while back I wrote about how to use a Cross Platform (or Clusterless) Availability Group to seed a database from a Windows SQL instance into a pod in Kubernetes.

I was talking with a colleague last week and they asked, “What if the existing Windows instance is already in an Availability Group?”

This is a fair question, as it’s fairly rare (in my experience) to run a standalone SQL instance in production…most instances are in some form of HA setup, be it a Failover Cluster Instance or an Availability Group.

Read on for the tutorial. There are quite a few steps involved.

Comments closed

Updates on the Spark Connect Dotnet Library

Published 2024-11-18 by Kevin Feasel

Ed Elliott has an update for us:

There have been quite a few changes in the last couple of months and I just wanted to give a quick update on the current state of the project. In terms of usage I am starting to hear from people using the library and submitting pr’s and requests so although usage is pretty low (which is expected from the fact that the Microsoft supported version usage wasn’t very high) it is growing which is interesting.

Read on for thoughts on production readiness, support for Spark 4.0, a couple of other updates, and some future plans.

Comments closed

Creating a Numbers Table in Power Query

Published 2024-11-18 by Kevin Feasel

Koen Verbeeck builds a tally table:

Sometimes you need a numbers table (also called tally table) when you’re working with data. It’s a table with one single integer column that contains incremental numbers (0,1,2,3,4,…). In a database, it’s fairly easy to generate using either cross-joins or a generator function (GENERATE_SERIES in T-SQL). Recently, I needed such a table in Power BI Desktop. Suppose you don’t have a database as a source (or maybe you cannot change the database), so we need to generate this in Power Query.

Read on to see how.

Comments closed

Comparing Numeric Data Types between MySQL and PostgreSQL

Published 2024-11-18 by Kevin Feasel

Aisha Bukar compares data types:

Numeric data types serve as a foundation in DBMS. It helps represent numbers in different forms which helps in storing and manipulating arithmetic operations on numerical data.

When dealing with numeric data types in databases like MySQL and PostgreSQL, it’s important to understand key concepts like range, precision(fixed-point numbers, floating-point numbers), and storage. This helps you understand how data is stored, processed, and retrieved.

Read on for quick descriptions of the three primary properties of numeric data types (range, precision, and storage), as well as how the two relational database platforms handle numeric data.

Comments closed

Tracking Column Sizes on DAX Queries

Published 2024-11-18 by Kevin Feasel

Chris Webb busts out the calculator:

I had meant to follow up my recent post on how to find the columns touched by a DAX query by writing one on how to use this technique to find the size of these columns in memory, so you can find the total size of the columns that need to be paged into memory when a DAX query runs on a Direct Lake semantic model. Before I could do that, though, my colleague Michael Kovalsky messaged me to say that not only had he taken the query from that first post and incorporated it in Semantic Link Labs, he’d done the work to get column sizes too. All that’s left for me to do, then, is give you some simple examples of how to use it.

Click through for those simple examples, though note that this requires Microsoft Fabric.

Comments closed

Concurrency Control in Oracle vs PostgreSQL

Published 2024-11-18 by Kevin Feasel

Umair Shahid continues a series on migrating from Oracle to PostgreSQL:

Transitioning from Oracle to PostgreSQL can be a transformative experience for database administrators because of the subtle differences between the two technologies. Understanding how the two handle concurrency differently is critical to managing highly concurrent workloads. 

Concurrency control is essential for maintaining data consistency when multiple users access the database simultaneously. Oracle and PostgreSQL take different approaches to concurrency control: Oracle primarily relies on locking and consistent snapshots, while PostgreSQL utilizes a Multi-Version Concurrency Control (MVCC) system.

This article provides an in-depth look at concurrency control in PostgreSQL from an Oracle perspective.

Read on for that comparison.

Comments closed