Press "Enter" to skip to content

Day: December 29, 2023

Plotting Time Series in R

Published 2023-12-29 by Kevin Feasel

Steven Sanderson builds some charts:

Our Flight Plan:

  1. Loading Up with Data: Grabbing our trusty dataset, AirPassengers.
  2. Taking Off with Base R: Creating a basic time series plot using base R functions.
  3. Soaring with ggplot2: Crafting a visually stunning time series plot using the ggplot2 library.
  4. Navigating Date Formatting: Customizing axis labels with scale_x_date() for clarity.
  5. Landing with Your Own Exploration: Encouraging you to take the controls and create your own time series plots!

Click through to see each of these steps in action.

Comments closed

Advent of Code Day 8 T-SQL Solution

Published 2023-12-29 by Kevin Feasel

Kevin Wilkie continues a series on this year’s advent of code. Day 8:

To make life a little simpler for all of us, I used a variable that I just pasted my instructions into. In my case, I called it @FollowThis allowing it to be up to 300 characters in length. Hopefully, no one’s instructions list is over that length – if so you’ll need to make that adjustment.

I also created a variable called @BackUpOfFollowThis that is strictly a copy of the original @FollowThis variable. (No muss, no fuss)

Once I place all of my inputs into the dbo.AOCDay8 table, we can easily start slicing and dicing.

And the more complex form for day 8:

First, we find out that we’re now starting with multiple positions – anything that ends with an A – and finishing anywhere that ends in a Z.

To make this happen, we’re going to want to create another table that will have all of our positions that end with the letter A.

Click through for Kevin’s solutions.

Comments closed

The Value of Data Lineage

Published 2023-12-29 by Kevin Feasel

Chisom Kanu explains why data lineage matters:

Data lineage is a component of modern data management that helps organizations understand the origins, transformations, and movement of their data. It is like a road map that shows us where our data has been, how it has changed, and where it is going, just like tracking the journey of a package: from the person who sent it (the source) to the places it passes through, and finally to the person who receives it.

The concept of data lineage has been around for many years, but it has become increasingly important in recent years due to the growth of big data and the increasing complexity of data processing systems.

Read on to learn more about data lineage.

Comments closed

DAX in Review: 2023

Published 2023-12-29 by Kevin Feasel

Marco Russo gives us a review:

Despite the large number driven by the new INFO functions, we did not see big changes in DAX, but rather a consolidation of the new window functions.

After three years, in 2023 the composite models reached the general availability! More important, the official name is now “composite models”, dropping the previous “DirectQuery for Power BI datasets and Analysis Services” name used in preview. Add to this that “datasets” have been renamed to “semantic models” (a change we fully support at SQLBI), and it seems this year we made peace with the Microsoft naming departments (there should be more than one).

Click through for more on DAX, as well as what Marco and Alberto have been up to this year.

Comments closed

Monitoring Checkpoints and the Background Writer in Postgres

Published 2023-12-29 by Kevin Feasel

Muhammad Ali keeps an eye on things:

In PostgreSQL, a checkpoint is an operation during which the database flushes/syncs all pending modifications(dirty buffers) inside memory to the actual data files on the disk.

This is important for two primary reasons. Firstly, it guarantees that all committed transactions are permanently stored, thereby safeguarding against data loss in the event of a system failure. Secondly, it works closely with the database recovery mechanism. If a crash occurs, PostgreSQL begins processing WAL logs starting from the last successful checkpoint(It gets this information from the pg_control file located in the PG data directory) during recovery. Additionally, this process allows for the fine-tuning of performance through a variety of parameters, adaptable to specific workload requirements which are discussed below.

Read on to learn more about how checkpoints work in Postgres, how the background writer works, and things to keep in mind.

Comments closed

Dynamic SQL in Stored Procedures

Published 2023-12-29 by Kevin Feasel

Erik Darling gets serious:

I’ve spent a lot of time on this blog telling you different reasons why you should, when you should, and how you should use dynamic SQL.

In this post, I’m going to go into some of the finer points of how I approach dynamic SQL to avoid issues — not performance issues — more procedural issues.

Things you should use to protect yourself from wonky object names, string truncation, object identification, and more.

Read on for Erik’s tips for handling dynamic SQL.

Comments closed