Press "Enter" to skip to content

Author: Kevin Feasel

Unpivoting Data via CROSS APPLY

Aaron Bertrand’s speaking my language:

I’ve written about UNPIVOT before – see Use SQL Server’s UNPIVOT operator to help normalize output and Use SQL Server’s UNPIVOT operator to dynamically normalize output. It’s a powerful language feature that lets you flip results sideways, sort of like the opposite of PIVOT. This is great if you have columns like Phone1 and Phone2 but want to collapse them into one column. One of the challenges is that if you have other columns you also want to collapse similarly, like Email1 and Email2, you need to add an additional UNPIVOT operator or use a different approach. Is there a way to do this in a less complicated way?

Click through for the answer. And a free Curated SQL protip: if you ever want linked here, write an article about a good use case for the APPLY operator. I’m a sucker for those.

Comments closed

Using the NTILE() Window Function

Joe Celko explains how NTILE() works:

The SQL NTILE() is a window function that allows you to break a table into a specified number of approximately equal groups, or <bucket count>. For each row in a grouping, the NTILE() function assigns a bucket number representing the group to which the row belong starting at one.

Click through for the explanation, including what it is and isn’t, as well as several examples.

Comments closed

Azure Data Studio 1.47 Now Available

David Levy announces a new release of Azure Data Studio:

Another change that is sure to be noticed is that we have enabled parallel message processing by default. This change will improve the performance of the application when connected to SQL Server data sources. We have introduced two new settings to manage this new behavior. The first setting, Mssql: Parallel Message Processing is set to true by default. The other setting, Mssql: Parallel Message Processing Limit, has a default of 100. This setting controls the number of threads used for parallel processing. We are excited to hear your impressions on the impact of this change.

Read on for the full list of changes.

Comments closed

Converting Data to Time Series in R with TidyDensity

Steven Sanderson shows off a new function:

If you’re an R enthusiast like me, you know that data manipulation is at the core of everything we do. The ability to transform your data swiftly and efficiently can make or break your data analysis projects. That’s why I’m thrilled to introduce a game-changing function in TidyDensity, my very own R library. Say hello to convert_to_ts()!

In the world of data analysis, time series data is like a treasure chest of insights waiting to be unlocked. Whether you’re tracking stock prices, monitoring patient data, or analyzing the temperature over the years, having your data in a time series format is a crucial step in the process. With convert_to_ts(), that process just got a whole lot easier.

Click through to see how it works and what you can do with it.

Comments closed

Print Debugging in R and Rust

Jonathan Carroll leaves breadcrumbs:

Print debugging has its place. Sure, it’s not always the best way to debug something, but it can often be the fastest. In this post I describe a useful way to do this in Rust and how we can get similar behaviour in R.

Read on to see how the dbg! macro works in Rust and how to emulate it in R, both by hand and using a library called icecream. H/T R-Bloggers.

Comments closed

Improving Performance of Power BI Project CI in Azure DevOps

Kevin Chant checks in a Power BI project:

I decided to test the guide with the Power BI report that I showed in my post about work with Microsoft Fabric Git integration and multiple workspaces.

So, I went through the guide and was pleasantly surprised that it showed how to do it with a YAML pipeline in Azure Pipelines. Which I must admit I prefer for reasons that I covered why in a previous post about disabling classic pipelines in Azure DevOps.

Read on for a review of the issues Kevin had to sort out, as well as two mechanisms to improve the performance of your Azure DevOps CI process.

Comments closed

The Utility of 6th Normal Form

I have a new video:

In this video, explain what Sixth Normal Form (6NF) is and why it slots in as the third most-important normal form. We look at two separate use cases in which 6NF can make sense and I provide some guidance on when 5NF is good enough versus when 6NF is better.

6th Normal Form doesn’t necessarily make sense all the time, but there are some really good use cases for it.

Comments closed

Microsoft Fabric Cost and Capacity

Soheil Bakhshi lays out the options:

Microsoft Fabric is a SaaS platform that allows users to get, create, share, and visualise data using a wide set of tools. It provides a unified solution for all our data and analytics workloads, from data ingestion and transformation to data engineering, data science, data warehouse, real-time analytics, and data visualisation. In a previous blog post, I explained the basics of the Microsoft Fabric data platform. In a separate blog post, I explained some Microsoft Fabric terminologies and personas where I explained what Tenant and Capacities are.

In this blog post, we will explore the different types of Fabric capacities, how they affect the performance and cost of our Fabric projects, and how you can control the capacity costs by pausing the capacity in Azure when it is not in use.

Click through for more information. The costs are in New Zealand Dollars, so translate as needed.

Comments closed

Getting View Definitions

Chad Callihan finds the DDL for a view:

I recently faced a situation where I had to track down the definition of a particular SQL view for various databases. I didn’t want to click through each database in SSMS to gather the information. I thought I would write a query that I could use to save some clicks and gather what I needed for each database.

The first part of that task, querying for the view definition, may be a bit tricky. I would venture to guess it may not be in the first couple of places you would think to look. Let’s walk through how we can use a query to retrieve the definition of a view.

Just make sure that you have newline retention on or else your view definition is all going on one line.

Comments closed