Press "Enter" to skip to content

Author: Kevin Feasel

GROUP BY ROLLUP

Published 2021-06-24 by Kevin Feasel

Dinesh Asanka hits on one of the under-utilized grouping operators:

You will see that data is aggregated for the columns provided by the GROUP BY clause. Important to note that the data will not be ordered in the GROUP BY columns and you need to explicitly order them by using the ORDER BY clause as shown in the above query.

In the above query, if you wish to find the total for Australia only, you need to run another GROUP BY with EnglishCountryRegionName and perform a UNION ALL. This will be a very ugly method. By using GROUP BY ROLLUP you can achieve the above-said task as shown in the following query.

If I were to rank grouping operators by how frequently I use them, it’s GROUPING SETS by a country mile, then ROLLUP, and almost never do I use CUBE.

Comments closed

Using Ola’s Maintenance Solution on RDS

Published 2021-06-24 by Kevin Feasel

Jack Vamvas takes us through a couple of nuances around using Ola Hallengren’s SQL Server Maintenance Solution on Amazon RDS:

I’ve used the Ola Hallengren Maintenance Solution across various SQL Server environments . I was recently asked by a colleague about how adaptable they are to the AWS RDS SQL Server environment. 

I checked the Ola Hallengren FAQ and there is a comment :

Read on to learn the details.

Comments closed

Consistency and Completeness in Kafka Streams

Published 2021-06-23 by Kevin Feasel

Guozhang Wang announces a whitepaper:

Recently, however, some streaming engines, such as Apache Kafka® and its ecosystem component Kafka Streams, have been able to claim strong correctness guarantees, with the primary dual metrics being consistency, a guarantee that a stream processing application can recover from failures to a consistent state such that final results will not contain duplicates or lose any data, and completeness, a guarantee that a stream processing application does not generate incomplete partial outputs as final results even when input stream records may arrive out of order.

Click through for more details and a link to the paper itself. It’s good to understand as much as you can about the distributed system you use, especially because many times, the claims for consistency should come with large asterisks.

Comments closed

From SQL Server to Excel via R

Published 2021-06-23 by Kevin Feasel

Kevin Wilkie wraps up a series on data movement between Excel and SQL Server:

In today’s post, we’ll go over how to export the data you have in SQL Server to Excel via one of my favorite computer languages – R. (Since we did have a post on how to Import data, it would seem rather rude not to have one on how to Export data.)

As always, you’ll need to open your R tool of choice. I tend to use RStudio but there are several out there that will accomplish this same goal.

Click through to see how.

Comments closed

Cumulative Values in Power BI

Published 2021-06-23 by Kevin Feasel

Matt Allington has a video for us:

The table on the left above shows the change in head count in each department, and is to be populated by the manager. But when it comes to reporting, we really need to know the total change in headcount as a number for each year, not just the first year the change occurred (as shown in the table to the right, above).

There are different ways to solve this problem, but I decided to do it using a combination of Power Query and DAX. 

Click through for the video solution.

Comments closed

Incremental Refreshing in Power BI Premium Per User

Published 2021-06-23 by Kevin Feasel

Gilbert Quevauvilliers continues a series on migrating from Azure Analysis Services to Power BI Premium Per User:

Welcome to the fourth instalment of my series on migrating AAS to PPU.

In this blog post I am going to cover the differences, and pros and cons to incremental refreshing when migrating from AAS to PPU.

The reason for incremental refreshing is that I do not have to reload the entire fact table every day when new data arrives.

This also allows for new data to be updated quicker.

And finally, it also uses less resources (memory and CPU) when refreshing the datasets.

Read on to see how it works in both platforms.

Comments closed

Trimming Strings with Powershell

Published 2021-06-23 by Kevin Feasel

Patrick Gruenauer shows off the Trim series of methods in Powershell:

In PowerShell it’s easy to manipulate strings, especially to cut off some characters from a string. These three methods are your friends when it comes to trimming: Trim, TrimStart and TrimEnd. In this blog post I will show you how to use PowerShell to manipulate strings and cut off something at the beginning and at the end. I will also try to give you some useful practical examples in hand to convince you that it’s worth to learn something about string manipulation. Let’s hop in.

Click through for examples of these three methods and how they do more than removing whitespace.

Comments closed

Fun with Database Corruption

Published 2021-06-23 by Kevin Feasel

Chad Callihan isn’t in production:

When you’re not expecting it, database corruption is bad for your database server (and probably your own personal health). Database corruption can be fun though when you’re the one doing the corrupting. Let’s take a look at corrupting a database and see what we can learn.

Click through to see what kind of madness you can pull off and how to fix it. And if you get really excited about database corruption, check out Steve Stedman’s Database Corruption Challenge.

Comments closed

FGCB_ADD_REMOVE Latch

Published 2021-06-22 by Kevin Feasel

Paul Randal looks at a particular latch:

Most latch class names are tied directly to the data structure that they protect. The FGCB_ADD_REMOVE latch protects a data structure called an FGCB, or File Group Control Block, and there will be one of these latches for each online filegroup of each online database in a SQL Server instance. Whenever a file in a filegroup is added, dropped, grown, or shrunk, the latch must be acquired in EX mode, and when figuring out the next file to allocate from, the latch must be acquired in SH mode to prevent any filegroup changes. (Remember that extent allocations for a filegroup are performed on a round-robin basis through the files in the filegroup, and also take into account proportional fill, which I explain here.)

Read on to understand what can cause this particular latch to become a bottleneck in your system.

Comments closed