Press "Enter" to skip to content

Month: April 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

Styling Excel Tables in R

Published 2023-04-14 by Kevin Feasel

Steven Sanderson wants to spice things up:

The styledtable package in R, which allows users to create styled tables in R Markdown documents. The package can help to create tables with various formatting options such as bold text, colored cells, and borders. It also has functionality on how to port these to Excel itself.

The package offers a simple syntax that allows users to specify formatting options using HTML and CSS. The resulting table can be customized by changing the CSS file or by using the ‘styler’ function to apply custom styles to individual cells or rows.

Read on for more information on what the package does and a few examples of how it works.

Comments closed

Comparing Data Visualization in Excel and R

Published 2023-04-14 by Kevin Feasel

Amieroh Abrahams builds some graphs:

In Excel it is challenging to eye-ball which changes have been made to a graph, especially if these were minor changes. With R (and some easy to use version control systems), you can see exactly which files were changed. Also, in Excel, a user would usually draw a graph on a single Excel document, and if the same graph is required on a different data set, it is common to copy-and-paste a bunch of manipulations and configurations to another document. Such repeated human interaction is prone to introducing errors, as well as consuming a large amount of time. With R we can avoid this by creating functions, which can be used to run the same code on different data sets simply by changing the input, thereby producing reliable outputs and saving us a lot of time.

Click through for the article. One big thing in Excel’s defense that I did not see here was that it’s a lot easier to perform specific story-telling in Excel visuals. For example, highlight just these two data points, or annotate this segment of the visual. You can do those things in ggplot2 but it’s considerably more difficult than “right-click the data point and format.”

Comments closed

Monitoring Client Timeouts via Extended Event

Published 2023-04-14 by Kevin Feasel

Erik Darling wait until the phone stops ringing and checks caller ID to see who bothered him:

Most applications have a grace period that they’ll let queries run for before they time out. One thing that I notice people really hate is when that happens, because sometimes the effects are pretty rough.

You might have to roll back some long running modification.

Even if you have Accelerated Database Recovery enabled so that the back roll is instant, you may have have 10-30 seconds of blocking.

Or just like, unhappy users because they can’t get access to the information they want.

Monitoring for those timeouts is pretty straight forward with Extended Events.

Click through for a query Erik uses for the task.

Comments closed

Protecting a Website from Cross-Site Request Forgery Attacks

Published 2023-04-14 by Kevin Feasel

Aneesh Lal Gopalakrishnan stops an attack:

ASP.NET MVC and ASP.NET Core are traditionally some of the most used platforms to build financial web applications, such as banks and hedge funds. From a statistical standpoint, these platforms are trusted more than their counterparts, such as Express or NodeJS, for financial web applications. In addition, it is easier to fix CSRF issues in ASP.NET Core than in ASP.NET MVC because of the better tools and support available. We will investigate techniques to fix CSRF issues in ASP.NET MVC.

About 10-15 years ago, CSRF was one of the top threats (in 2007 and 2010, it was #5; in 2013, it was #8), but then it dropped off the list. The reason is that, basically every platform in existence put in anti-CSRF tokens automatically, so you rarely see it work anymore except for really old websites.

Comments closed

Key Components to Power BI

Published 2023-04-14 by Kevin Feasel

Reza Rad provides an overview of what Power BI includes:

Power BI is a cloud-based technology from Microsoft for reporting and data analysis. This reporting technology is built in a way that is not only useful for developers to create reports but also for power users and business analysts. Power BI created a simple, easy-to-use, and user-friendly environment for creating reports. And on the other hand, it is based on several powerful components which help create reports and data analysis for complex scenarios.

Every component of Power BI is responsible for a specific part of the technology. There are components for building reports, connecting to data sources, doing analytics calculations, sharing reports, etc. The following sections explain what each component is. Some of these components are explained in detail in other articles.

Click through for a long list and a series of high-level summaries.

Comments closed