Press "Enter" to skip to content

Day: April 23, 2024

Comparing Cumulative Values for Events across Different Periods

Kurt Buhler performs comparative analytics:

In a previous article about format strings, we showed an example of how format strings can improve visualizations. The visualizations in that article compared the performance of a company’s marketing videos on a streaming platform to the average of all their videos released that year. In this article, we explain how to conduct this analysis yourself in DAX, where you compare series that occur in different periods.

Click through for an explanation, both in video and in blog form.

Comments closed

Cannot Open Backup Device with SQL Managed Instance and SAS Token

Sam Garth troubleshoots an issue:

On a recent case, a customer was trying to restore a database from a storage account using a SAS token when they received the below error.

An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)

Additional information:
Cannot open backup device
https://storage.blob.core.windows.net/container/dbbackup_2024_03_21_121901.bak
Operating system error 86(The specified network password is not correct.).
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)

Read on for the troubleshooting steps Sam followed to solve the problem.

Comments closed

PostgreSQL Row Visibility Indicators

Cary Huang explains how row visibility works in Postgres:

Simply put, the visibility refers to whether a row of data (Heap Tuple by default) should be displayed to the user in certain states, backend processes, or transactions.

For example,

  • A user changes a data record from ‘A’ to ‘B’ via UPDATE
  • PostgreSQL handles this by INSERTing a new record call ‘B’ while marking record ‘A’ as ‘invisible’
  • Both records exist in the system, but ‘B’ is visible, while ‘A’ is not.
  • A deleted or invisible record is also referred as a ‘dead’ tuple
  • One of the responsibilities of VACUUM process is to clear these ‘dead’ tuples to free up space.

Read on to learn more about various visibility indicators and how they ultimately tell us whether a row should be visible or not.

Comments closed

Recursive Common Table Expressions in Postgres

Ryan Booz explains how recursive common table expressions work:

The first two articles in this series demonstrated how PostgreSQL is a capable tool for ELT – taking raw input and transforming it into usable data for querying and analyzing. We used sample data from the Advent of Code 2023 to demonstrate some of the ELT techniques in PostgreSQL.

In the first article, we discussed functions and features of SQL in PostgreSQL that can be used together to transform and analyze data. In the second article, we introduced Common Table Expressions (CTE) as a method to build a transformation query one step at a time to improve readability and debugging.

In this final article, I’ll tackle one last feature of SQL that allows us to process data in a loop, referencing previous iterations to perform complex calculations: Recursive CTE’s.

Given that Postgres allows for materialized common table expressions, I’m a bit curious about how recursive common table expressions perform compared to SQL Server.

Comments closed

Searching for Tenant Settings in Microsoft Fabric

Nicky van Vroenhoven performs a search:

You probably also use the same method as I did to search through the Admin portal and tenant settings: CTRL + F from your browser. It does the trick, but not very well. 

For example, it only searches the titles of the settings, not the descriptions.

Next to that, you also can get a lof matches that you have to scroll or loop through, which makes it not very clear because more often than not, you don’t know in what section of the tenant settings you ended up.

Read on for an alternative method of searching. Or, I guess, two of them because without Nicky’s post, it can be easy to confuse the two search boxes.

Comments closed