Press "Enter" to skip to content

Curated SQL Posts

VISUAL SHAPE and Visual Calculations

Published 2024-03-12 by Kevin Feasel

Marco Russo and Alberto Ferrari fit the square pegs into the square holes:

Visual calculations, introduced as a preview feature with the February 2024 release of Power BI, aim to simplify the creation of calculations tied to a specific visual. Using visual calculations for simple calculations is straightforward.

However, as soon as developers create more complex calculations, they should understand the technical details of visual calculation implementation. This requires understanding the hierarchical structure of the virtual table, the new visual context, the semantics of ROWS and COLUMNS, the behavior of CALCULATE, and the new visual context modifiers EXPAND and COLLAPSE.

In this first article about visual calculations, we introduce VISUAL SHAPE and the basics of visual calculation implementation, leaving the remaining topics to future articles. A complete whitepaper with a detailed explanation of all these topics will be available soon to SQLBI+ subscribers.

Even without a complete whitepaper, this serves as a useful primer on the topic.

Comments closed

Dropping Extra tempdb Files

Published 2024-03-12 by Kevin Feasel

Vlad Drumea doesn’t want to restart SQL Server:

This is a brief post containing a script you can use to delete extra tempdb data files and avoid the “file not empty” (Msg 5042) error.

I’ve occasionally ran into situations where an instance is configured with more than the recommended number of tempdb data files.

If you’re dealing with more than a couple of extra files it can get annoying, especially on a busy instance.

Read on for the script and how you can use it on busy and not-so-busy instances.

Comments closed

Saving Money on Azure Storage

Published 2024-03-11 by Kevin Feasel

Rahul Miglani claws back some cash:

In today’s digital landscape, businesses are increasingly turning to cloud storage solutions to manage their data effectively. Microsoft Azure offers a wide range of storage options tailored to meet diverse business needs while optimizing costs. In this blog post, we’ll explore how organizations can leverage Azure storage options to achieve significant cost savings without compromising performance or reliability.

Read on for ten tips. A lot of it boils down to keeping just enough data and putting it in the right tier, but there’s a bit more to the story.

Comments closed

Relationship Columns and Power BI DirectQuery Mode

Published 2024-03-11 by Kevin Feasel

Chris Webb builds a relationship:

Many Power BI connectors for relational databases, such as the SQL Server connector, have an advanced option to control whether relationship columns are returned or not. By default this option is on. Returning these relationship columns adds a small overhead to the time taken to open a connection to a data source and so, for Power BI DirectQuery semantic models, turning this option off can improve report performance slightly.

Read on to learn what these relationship columns are and how you can remove them. Chris also provides a first-order approach to how you can estimate the performance pain involved with including these.

Comments closed

Analyzing TDS 8.0 Handshake Packets with Wireshark

Published 2024-03-11 by Kevin Feasel

Matt Changchien fires up the packet capture tool:

The main difference between TLS1.2 and TLS1.3 is that TLS 1.3 reduces the number of round trips from two to one during the handshake phase, making it faster and more secure than TLS 1.2. The server hello packet containing server certificate is encrypted and one Round Trip Time (1-RTT) resumption is discontinued, and replaced with 0-RTT resumption based on client key share. Added security of TLS 1.3 comes from discontinuing certain cyphers and algorithms. (TLS 1.3 support – SQL Server | Microsoft Learn)

Click through to see what the differences look like between TLS 1.2 and TLS 1.3, as well as the specific example of Azure SQL Database.

Comments closed

Input, Output, & Input/Output Parameters in Oracle & Postgres Procedures & Functions

Published 2024-03-11 by Kevin Feasel

Akhil Reddy Banappagari makes me use too many ampersands:

When migrating Oracle routines to PostgreSQL, handling OUT and INOUT parameters can be tricky. Understanding the distinctions between Oracle and PostgreSQL in how they manage these parameters is essential for a successful migration. This knowledge helps you smoothly adjust your routines, ensuring your code works well without any issues. In this article, we shall explore IN, OUT and INOUT parameters in Oracle and PostgreSQL and understand some of the important differences.

Read on to see how these work in Postgres and Oracle.

Comments closed

Running SemPy from Microsoft Fabric Notebooks

Published 2024-03-11 by Kevin Feasel

Gilbert Quevauvilliers sets up an environment:

Below is where I had an error when trying to run a notebook via a data pipeline and it failed.

Below are the steps to get this working.

This was the error message I got as shown below.

Notebook execution failed at Notebook service with http status code – ‘200’, please check the Run logs on Notebook, additional details – ‘Error name – MagicUsageError, Error value – %pip magic command is disabled.’ :

Read on to see how you can fix this error and get SemPy running.

Comments closed

Subsetting Data Frames in R using Multiple Conditions

Published 2024-03-08 by Kevin Feasel

Steven Sanderson can’t stop at one filter:

In data analysis with R, subsetting data frames based on multiple conditions is a common task. It allows us to extract specific subsets of data that meet certain criteria. In this blog post, we will explore how to subset a data frame using three different methods: base R’s subset() function, dplyr’s filter() function, and the data.table package.

Click through for examples.

Comments closed

A Primer on Vector Similarity Search

Published 2024-03-08 by Kevin Feasel

Pavan Belagatti talks vectors:

In the realm of generative AI, vectors play a crucial role as a means of representing and manipulating complex data. Within this context, vectors are often high-dimensional arrays of numbers that encode significant amounts of information. For instance, in the case of image generation, each image can be converted into a vector representing its pixel values or more abstract features extracted through deep learning models.

These vectors become the language through which AI algorithms understand and generate new content. By navigating and modifying these vectors in a multidimensional space, generative AI produces new, synthetic instances of data — whether images, sounds or text — that mimic the characteristics of the original dataset. This vector manipulation is at the heart of AI’s ability to learn from data and generate realistic outputs based on that learning.

Read on for a high-level overview of the topic.

Comments closed