Press "Enter" to skip to content

Curated SQL Posts

Creating a Power BI Dataset and Report via DirectLake

Published 2023-09-07 by Kevin Feasel

Gilbert Quevauvilliers finishes up a series:

In the final part of my series, I am going to be creating the Power BI DirectLake dataset and report from my tables that I had previously loaded into the lakehouse.

In this series I am going to show you all the steps I did to have the successful outcome I had with my client where I created the dataset (measures and fields) and the Power BI report.

Click through for links to the prior posts, as well as a walkthrough on creating a DirectLake asset in Power BI.

Comments closed

Transaction Log Files and Instant File Initialization

Published 2023-09-07 by Kevin Feasel

Erik Darling preps us for SQL Server 2022:

Look, I don’t blame you if you haven’t dug deep into what SQL Server 2022 has to offer just yet. It’s hard enough to keep up with all the problems fixed and caused by cumulative updates.

One thing you may want to pay attention to is how transaction log files are grown and VLFs are created, especially for new databases.

Read on to see what has changed there.

Comments closed

Value and Hash Encoding in VertiPaq

Published 2023-09-07 by Kevin Feasel

Kristyna Hughes looks at column encodings:

Power BI encoding is a powerful optimizing option that is often overlooked because it’s not visible in neither the Power BI Desktop tool nor in Power BI Service. Natively, the VertiPaq engine in Power BI investigates all columns in the data model and determines how it can store that data most efficiently. To achieve maximum compression, the VertiPaq engine starts by encoding each column which determines the method of compression applied to that column. There are a couple types of encoding – value and hash.

Read on to learn the difference, as well as how to push your columns to use a specific type of encoding.

Comments closed

First Impressions of DAX Optimizer

Published 2023-09-07 by Kevin Feasel

Nikola Ilic takes a look:

A few months ago, while scrolling through posts on social media, one of them immediately grabbed my attention! It was about a new tool, called DAX Optimizer, which promised to identify and remove performance bottlenecks in your DAX formulas. For all of us dealing with optimizing Power BI reports on a day-to-day basis, that was a huge promise (and one I was impatiently waiting to see in action).

One important note is that this is not a free tool, as Nikola mentions. Read on for more thoughts about how it works, what it picks up, and whether it’s a good fit for your environment given the price.

Comments closed

An Overview of SQL Server Indexes

Published 2023-09-07 by Kevin Feasel

Adron Hall gives us a tour of indexes in several relational database management systems:

1. Structure of the Craft: Dive deep, and you’d find tree-like structures, be it the B-tree or its illustrious cousins – the B+ tree and B* tree. These aren’t ordinary trees; they’re a labyrinth that efficiently guides the system to the row it seeks in a jiffy.
2. Guiding Stars – Pointers: Each entry in this labyrinth isn’t just a dead end. It carries a key value and – wait for it – a pointer. Think of it as a magical compass pointing directly to the treasure, or in this case, the row in the table.
3. Supercharged Searches: Now, imagine sifting through a library without a catalog – agonizingly slow, right? That’s how a database without an index feels. But bring in an index, and suddenly even the vastest of tables become a playground of swift searches.

Read on for an overview of what purposes indexes fulfill in these data platforms. I use “purposes they fulfill” rather than “types of indexes” because there are a couple entries on the list which are not, strictly speaking, actual types of indexes.

Comments closed

Random Number Generation in R

Published 2023-09-06 by Kevin Feasel

Adrian Tam rolls the dice:

Whether working on a machine learning project, a simulation, or other models, you need to generate random numbers in your code. R as a programming language, has several functions for random number generation. In this post, you will learn about them and see how they can be used in a larger program. Specifically, you will learn

  • How to generate Gaussian random numbers into a vector
  • How to generate uniform random numbers
  • How to manipulate random vectors and random matrices

And, of course, these are pseudo-random numbers because we’re still dealing with computers and random seeds, after all.

Comments closed

Kernel Density Plots in R

Published 2023-09-06 by Kevin Feasel

Steven Sanderson explains one common type of plot in R:

Kernel Density Plots are a type of plot that displays the distribution of values in a dataset using one continuous curve. They are similar to histograms, but they are even better at displaying the shape of a distribution since they aren’t affected by the number of bins used in the histogram. In this blog post, we will discuss what Kernel Density Plots are in simple terms, what they are useful for, and show several examples using both base R and ggplot2.

Read on to learn more, including how to generate these in base R, ggplot2, and with the tidy_density package.

Comments closed

Restoring Backups from S3 to Azure SQL MI

Published 2023-09-06 by Kevin Feasel

Strahinja Rodic announces a new feature going to GA:

In September last year SQL server 2022 introduced new feature – backup and restore to simple storage service (S3) – compatible object storage that grants the user the capability to back up or restore their databases using S3-compatible object storage, whether that be on-premises, or in the cloud.

To provide this integration Azure SQL MI is enriched with a new S3 connector, which uses the S3 REST API to connect to Amazon S3 storage. It extends the existing RESTORE FROM URL syntax by adding support for the new S3 connector using the REST API.

Click through to see what you need to have set up for it to work, as well as the restoration process.

Comments closed

Automatic SSL Certificate Updates for SQL Server

Published 2023-09-06 by Kevin Feasel

Jamie Wick doesn’t want to waste time clicking mouse buttons:

In a previous post (How Secure Are Your Client Connections?) I covered using SSL certificates to encrypt client connections to SQL Server. One part of the process that has always been annoyingly repetitive is the need to regularly renew/update the SSL certificate(s) and reconfigure SQL Server to use them. In the past our SSL certs were good for 3 years. Then in 2020 Google, Microsoft, Apple & others announced that they were reducing their certificate validity period to a maximum of 398 days. Recently, there have been proposals to further reduce the validity period to 90 days. When this happens (and I’m certain it is a when, and not an if situation), manually replacing SSL certs on multiple SQL servers will not be a viable option.

Read on to see what Jamie’s solution to the problem is.

Comments closed

Scale-Out Read-Only Databases in Azure SQL DB

Published 2023-09-06 by Kevin Feasel

Etienne Lopes begins a new series:

“As part of High Availability architecture, each single database, elastic pool database, and managed instance in the Premium and Business Critical service tier is automatically provisioned with a primary read-write replica and one or more secondary read-only replicas.”

Read on to see how you can add support for read-only, scale-out replicas to an existing Azure SQL Database. Just know how much that bill is going to be.

Comments closed