Press "Enter" to skip to content

Curated SQL Posts

Random Walks in R with TidyDensity

Published 2024-07-11 by Kevin Feasel

Steven Sanderson goes for a walk:

A random walk is a mathematical object that describes a path consisting of a succession of random steps. It’s a cornerstone concept in fields like physics, economics, and biology. In finance, for example, the random walk hypothesis suggests that stock market prices evolve according to a random walk and thus cannot be predicted.

Read on to see how you can generate a dataset matching a random walk, as well as a comparison of techniques for generating them.

Comments closed

Measure-Object in Powershell

Published 2024-07-11 by Kevin Feasel

Patrick Gruenauer counts the ways:

The Measure-Object cmdlet counts objects. But it can do even more. We can calculate the sum, the average and much more. In this blog post I show a few examples with Measure-Object. Let’s dive in.

It’s a fairly straightforward cmdlet but it has a lot of use, being a combination of something like wc in Linux as well as collecting basic statistics on objects.

Comments closed

JSON and JSONB Data Types in Postgres

Published 2024-07-11 by Kevin Feasel

Andrea Gnemmi covers a pair of data types to manage one thing:

We have all encountered the need to store non-structured or semi-structured data in an RDBMS; XML or JSON data in particular. This can be complicated, especially in the past with limited technical options, and even more complicated if we want to query this data efficiently.

Read on to learn more about the differences between JSON and JSONB, as well as mechanisms you can use to query subsets of the data.

Comments closed

Visual Calculations and Multi-Bar Graphs

Published 2024-07-11 by Kevin Feasel

Erik Svensen builds a thing:

In this post I will guide you through creating this chart in Power BI – it is a stacked bar chart that show the size/impact of three different measures – Sales Value, Sales Units and Avg Price in one visual.

It’s not a visualization that I would recommend but there might be use cases for it somewhere and it has been a good exercise in what we can do with visual calculations.

It’s very clever, I’ll give it that.

Comments closed

An Introduction to Streamlit

Published 2024-07-10 by Kevin Feasel

I have started a new video series:

In this video, I talk about Streamlit, a great Python library for building data applications quickly. We discuss what data applications are, get an idea of how Streamlit compares to other code-first data visualization techniques, and start building a demo application. I also toss in a lengthy sidebar on Python virtual environments because of how important they are.

Streamlit certainly has its foibles—many of which I’ll cover in the series—but I like it a lot as a simple way of building data applications.

Comments closed

Extracting Strings before a Space using R

Published 2024-07-10 by Kevin Feasel

Steven Sanderson grabs a name:

Hello, R users! Today, we’ll dive into a common text manipulation task: extracting strings before a space. This is a handy trick for dealing with names, addresses, or any text data where you need to isolate the first part of a string.

We’ll explore three approaches: using base R, stringr, and stringi. Each method offers its unique advantages, so you can choose the one that fits your style best.

Click through for the three examples. I will note that if you’re actually using this code to split names, well, names tend to be a lot trickier than we give them credit for. Keep in mind that people can have multi-part names (“Debbie Mae” or “van den Berg”), so unless you know the data all follows a specific pattern, don’t assume the data follows a specific pattern.

Comments closed

Access Controls in PostgreSQL

Published 2024-07-10 by Kevin Feasel

Umair Shahid talks about access rights:

Access control is a fundamental aspect of database security, ensuring that only authorized users can perform specific actions on the data. Effective access control helps protect sensitive information from unauthorized access and prevents data breaches, which can have severe legal and financial repercussions for organizations.

PostgreSQL has a strong reputation for reliability, feature robustness, and performance. One of its notable strengths is its comprehensive support for various access control mechanisms, which allow database administrators to finely tune who can access what data and how.

It turns out that there’s a lot of overlap in how these work between SQL Server and Postgres, though the exact syntax may be a bit different for certain items.

Comments closed

Microsoft Healthcare Accelerator for Fabric

Published 2024-07-10 by Kevin Feasel

Tino Zishiri takes us through an accelerator solution:

Microsoft released the Healthcare Data Solutions in Microsoft Fabric in Q1 2024. It was introduced as a “A game-changer for healthcare data analysis” by Umesh Rustogi, General Manager of Microsoft Health and Life Sciences Data Platform.

Microsoft Fabric is a unified platform that bundles services, apps, and connectors under a single umbrella, providing users with the tooling to meet all data and analytics needs.

The Healthcare Data Solutions are built on top of this robust service offering. The solution is aimed at users who are looking for a powerful tool to integrate and transform Healthcare data. In addition, users can run real-time analytics, data science workloads and meet business intelligence needs without compromising the privacy and security of their data.

Click through to learn more about how this works for defining an industry-standard architectural pattern.

Comments closed

Automate the Power BI Incremental Refresh Policy via Semantic Link Labs

Published 2024-07-10 by Kevin Feasel

Gilbert Quevauvilliers needs to get rid of some data fast:

The scenario here is that quite often there is a requirement to only keep data from a specific start date, or where it should be keeping data for the last N number of years (which is the first day in January).

Currently in Power BI using the default Incremental refresh settings this is not possible. Typically, you must keep more data than is required.

It is best illustrated by using a working example.

Check out that scenario and how you can use the Semantic Link Labs Python library to resolve it.

Comments closed