Press "Enter" to skip to content

Day: April 17, 2023

Purging WSUS Synchronization Events

Published 2023-04-17 by Kevin Feasel

Hannah Vernon has a script:

Since Windows Server Update Services synchronizes the list of Windows Updates from the source Microsoft Servers on a regular basis, the history of sync events can become quite tedious to load in the User Interface. Frustratingly, there is no way through the user interface to remove old history for synchronization events. The SQL Server T-SQL code below creates a stored procedure that can be used to cleanup old events prior to a particular cut-off date. I run the code via a SQL Server Agent job daily, with a cut-off date of 30 days ago.

Click through for the stored procedure.

Comments closed

Learning Important Postgres Settings for SQL Server DBAs

Published 2023-04-17 by Kevin Feasel

Ryan Booz helps a SQL Server DBA out:

Five years ago, I began my transition back to using PostgreSQL full-time, and it wasn’t the smoothest journey. One of the biggest reasons I struggled to make progress learning PostgreSQL early on was simply not knowing what I should be looking for. In fact, I often have conversations multiple times a month about the differences between SQL Server and PostgreSQL with folks setting out on a similar journey.

My guess is that you’re trying to figure out the same things, which is how you ended up on this series of posts.

Read on to take advantage of Ryan’s pain and suffering.

Comments closed

Postgres GIS Calculations by SRID

Published 2023-04-17 by Kevin Feasel

Ryan Lambert talks accuracy:

A common use case with PostGIS data is to calculate things, such as distances between points, lengths of lines, and the area of polygons. The topic of accuracy, or inaccuracy, with GEOMETRY data comes up often. The most frequent offenders are generic SRIDs such as 3857 and 4326. In some projects accuracy is paramount. Non-negotiable. On the other hand, plenty of projects do not need accurate calculations. Those projects often rely on relationships between calculations, not the actual values of the calculations themselves. If Coffee shop Y is 4 times further away than Coffee shop Z. I’ll often go to Coffee shop Z just based on that.

In most cases, users should still understand how significant the errors are. This post explores one approach to determine the how accurate (or not!) the calculations of a given SRID are in a particular region, based on latitude (North/South). The queries used in this post can be adjusted for your specific area.

Click through to see how much the choice of SRID can impact your results.

Comments closed

Performing a Pareto Calculation in DAX

Published 2023-04-17 by Kevin Feasel

Phil Seamark does some manufacturing analysis:

I always enjoy it when we get new DAX functions, especially so for the new set of WINDOW Functions recently added. As part of the April 2023 release of Power BI Desktop, we now have a RANK function and the ability to use a measure to control the order within the existing WINDOW function.

The first thing that sprung to my mind was to see how a Pareto calculation might leverage the new capability.

The basic idea of a Pareto calculation is to create a curve like representation of data ordered from largest to smallest.

Read on to see how.

2 Comments

Disabling Filter Pane Aggregates in Power BI

Published 2023-04-17 by Kevin Feasel

Chris Webb disables a visual element:

These numbers are counts of the number of rows for each value in the table that the field is from. The query to get these counts is usually quite fast and inexpensive, but if you’re filtering on a field from a very large table (for example a fact table) and/or using DirectQuery mode that might not be true. For example, the screenshot above is taken from a DirectQuery dataset and here’s the SQL query that generates the counts shown:

Read on to see how to do this.

Comments closed