Press "Enter" to skip to content

Month: September 2024

Data Analysis with Window Functions in Postgres

Published 2024-09-18 by Kevin Feasel

Elizabeth Christensen dives into window functions:

SQL makes sense when it’s working on a single row, or even when it’s aggregating across multiple rows. But what happens when you want to compare between rows of something you’ve already calculated? Or make groups of data and query those? Enter window functions.

Window functions tend to confuse people – but they’re a pretty awesome tool in SQL for data analytics. The best part is that you don’t need charts, fancy BI tools or AI to get some actionable and useful data for your stakeholders.

Read on for several demonstrations. Most of this you can also do with SQL Server 2012 or later, though the DATE_TRUNC() example will only work in SQL Server 2022 or Azure SQL DB / Managed Instance. Prior to that, you’d need to use a different mechanism, such as CAST(o.order_date AS DATE), to get it working.

Leave a Comment

Automated Patching in SQL Server on Azure

Published 2024-09-18 by Kevin Feasel

Abdullah Mamun closes a door and opens a window:

The automated patching feature for SQL Server on Azure VM will be retired on September 17th, 2027.

While Automated Patching is being retired, we’re excited to replace it with Azure Update Manager. Azure Update Manager is an enterprise class powerful tool that provides:

  1. Centralize Update Management: Azure Update Manager provides a unified dashboard where you can view and manage updates across your entire environment, including virtual machines, on-premises servers, and even hybrid scenarios.
  2. Customize Schedules: With Azure Update Manager, you can create custom update schedules based on your organization’s needs. Whether you prefer weekly, monthly, or specific date-based updates, Azure Update Manager has you covered.
  3. Patch Compliance Reports: Azure Update Manager generates detailed reports on patch compliance, helping you stay informed about the status of updates across your infrastructure.

Click through to learn more about the transition and next steps.

Leave a Comment

Syncing Slicers in Power BI without Bi-Directional Relationships

Published 2024-09-18 by Kevin Feasel

Marc Lelijveld avoids the dreaded bi-directional relationship:

Have you ever wanted to sync two slicers on a report page? Even when both slicers are coming from different dimensions? A lot of users end up setting the relationships to bi-directional (both) which has huge side effects! You may up with a ambiguous data model, over filtering fact tables and wrong results. Also, there is a very likely performance impact to this solution.

But actually, to make the slicers sync, you don’t have to change the relationships! In this blog I will show you how you can sync two (or more) slicers on a report page without changing the relationships or the semantic model!

Read on to learn how.

Leave a Comment

Simple Data Cleanup with Pandas

Published 2024-09-17 by Kevin Feasel

Ivan Palomares Carrascosa builds a process:

Few data science projects are exempt from the necessity of cleaning data. Data cleaning encompasses the initial steps of preparing data. Its specific purpose is that only the relevant and useful information underlying the data is retained, be it for its posterior analysis, to use as inputs to an AI or machine learning model, and so on. Unifying or converting data types, dealing with missing values, eliminating noisy values stemming from erroneous measurements, and removing duplicates are some examples of typical processes within the data cleaning stage.

As you might think, the more complex the data, the more intricate, tedious, and time-consuming the data cleaning can become, especially when implementing it manually.

Ivan handles some of the most common types of data clean work and shows a simple way of implementing these.

Leave a Comment

Random Walks in R with RandomWalker

Published 2024-09-17 by Kevin Feasel

Steven Sanderson is going for a walk (not the after-dinner kind):

Welcome to the world of ‘RandomWalker’, an innovative R package designed to simplify the creation of various types of random walks. Developed by myself and my co-author, Antti Rask, this package is in its experimental phase but promises to be a powerful tool for statisticians, data scientists, and financial analysts alike. With a focus on Tidyverse compatibility, ‘RandomWalker’ aims to integrate seamlessly into your data analysis workflows, offering both automatic and customizable random walk generation.

Read on to learn more about the package, including why you might want to use it and the functionality you can get out of it.

Leave a Comment

Cloning Tables in Databricks

Published 2024-09-17 by Kevin Feasel

Chen Hirsh hogs the photocopier:

The simplest use case to explain why table cloning is helpful is this: Let’s say you have a large table, and you want to test some new process on it, but you don’t want to ruin the data for other processes, so you need a clean copy of your table (or multiple tables) to play with. Coping a large table might take time (Databricks does it very fast, but if it’s a big table it still takes time to copy the data) ,and what happens if you then need to change your code? you have to drop the target table, copy the source table again, and so on.

here is where cloning can be your friend.

Read on to learn about three cloning techniques. H/T Madeira Data Solutions blog.

Leave a Comment

Change Management for the DBA

Published 2024-09-17 by Kevin Feasel

Terri Hurley moved our cheese:

If you have never been involved in Change Management processes but now find yourself part of one, it may seem a bit overwhelming or confusing. However, the reasons for and benefits of these processes are simple and straightforward.

This article will explain Change Management and how DBAs are involved and can benefit from it.

Read on to learn more about how change management can work for a DBA. I’ve worked for several organizations as they’ve moved from a philosophy of “just do it” toward proper change management, typically for regulatory reasons like Sarbanes-Oxley compliance.

Leave a Comment

Working with Always Encrypted Data in SSIS

Published 2024-09-17 by Kevin Feasel

Rod Edwards continues a series on Always Encrypted:

So now, lets see how it plays with another one of those common toolsets that you may use alongside your Encrypted data. In this post, i’ll be talking about accessing and importing data using SSIS, nothing fancy, just reading data from an Excel sheet, and piping into our Always Encrypted table, encrypting as we go.

I’m not saying to use Excel for housing confidential data either!… as no one does that…oh no, not anywhere, ever….</sarcasm>.

As previously, this focuses on using Azure Key Vault for securing Encryption keys required.

Considering that all corporate data is in Excel someplace (some variant of which may eventually become Feasel’s Second Law), of course that sensitive and confidential data will be in a plain Excel file that people e-mail around.

Leave a Comment