Press "Enter" to skip to content

Month: April 2024

Roles and Privileges in PostgreSQL

Published 2024-04-08 by Kevin Feasel

Muhammad Ali explains quite a few of the security terms in PostgreSQL:

PostgreSQL has a fine grained system for for managing user roles and privileges. This helps admins decide who can access certain data and what they’re allowed to do with it. It’s about managing permissions, where you can create different roles and roles can be a member of other roles.

In this blog, we will dive into PostgreSQL user roles and permissions, covering databases, schemas, and other objects level privileges, following a FAQ format.

Most of this is similar to security in SQL Server, though there are some differences to watch out for.

Comments closed

Multidimensional Scaling in R

Published 2024-04-05 by Kevin Feasel

Steven Sanderson is from the 5th dimension:

Visualizing similarities between data points can be tricky, especially when dealing with many features. This is where multidimensional scaling (MDS) comes in handy. It allows us to explore these relationships in a lower-dimensional space, typically 2D or 3D for easier interpretation. In R, the cmdscale() function from base R and is a great tool for performing classical MDS.

Click through to see how this works. In case you’re curious, cmdscale() is an example of principal coordinates analysis. If you’re familiar with principal components analysis, that’s a different form of multidimensional scaling.

Comments closed

Tips for Dealing with Large Spatial Datasets

Published 2024-04-05 by Kevin Feasel

Rhian Davies consults the map:

I love playing with spatial data. Perhaps because I enjoy exploring the outdoors, or because I spend hours playing Geoguessr, or maybe it’s just because maps are pretty but there’s nothing more fun than tinkering with location data.

However, reading in spatial data, especially large data sets can sometimes be a pain. Here are some simple things to consider when working in spatial data in R and breaking large data sets into more manageable chunks.

Click through for three tips when dealing with spatial data. The code is in R but the tips make sense in any language.

Comments closed

The Power of the (Scatter) Plot

Published 2024-04-05 by Kevin Feasel

Kurt Buhler digs into scatter plots:

In many reports, it is common to find large tables or matrixes with a lot of information. Business users come to these reports to get the details they need to answer questions, make decisions, and take actions. However, these detailed tables can be inefficient, requiring users sort, drilldown, and filter them to find actionable data.

Consider the following example of a matrix to analyze gaps in year-over-year growth by product, and dissect those by region. In this scenario, the intended purpose of the report is for a sales team to analyze why we did not hit our revenue growth target.

Read on to learn what you can do with a scatter plot, either natively in Power BI or via custom visual.

Comments closed

Inferring Data Types in SQL Server Tables

Published 2024-04-05 by Kevin Feasel

Emanuele Meazzo tidies up the database:

Do you know the feeling when someone gives you a flat file to import, without any schema information? You have to dump it in your database as-is, then spend precious time figuring out the data type for each column
Let me help you with that, as I’m helping myself too.

Click through for the scenario, as well as a link to the script Emanuele has created. It looks like the script doesn’t try to estimate string lengths, instead focusing on types alone.

Comments closed

A Review of Powerops

Published 2024-04-05 by Kevin Feasel

Nikola Ilic reviews a new tool:

In the last 2-3 months, I had an opportunity to try and test the new external tool, called Powerops. This is an external tool developed by Truviz, the company you might already know as the creators of various Power BI “goodies”.

Now, you are probably expecting “another Power BI external tool that solves a specific challenge”…I’ll tell you straight away – you can’t be more wrong! Powerops is a mix of various existing external tools – think of DAX Studio, Tabular Editor, ALM Toolkit, Measure Killer, etc. – but it’s also more than a simple sum of the features of the aforementioned tools.

Read on for the full review. Pricing is free for the first two months, and then either buy a license or you can upload up to two files per month and remain in the free tier.

Comments closed

Infrastructure as Code in GitHub

Published 2024-04-04 by Kevin Feasel

I have a new video:

In this video, we look at how to perform Infrastructure as Code in GitHub. We take a Bicep script and generate new Azure resources using it and GitHub Actions.

The video includes a very brief primer on Azure Resource Manager (ARM) and Bicep, and then gets into how you can use GitHub Actions to keep your Azure resources configured the way you expect.

Comments closed

Building a Powershell Transcript

Published 2024-04-04 by Kevin Feasel

Rod Edwards saves the transcript:

I have a love for Powershell, it makes my life as a SQL Server tech so much easier when I have to either treat my instances as a herd cattle, rather than separate bovine entities, or have them interact with other technologies as part of a larger task… ie… play with the other farm animals if you will.

Enough of the animal metaphors.

We write Powershell, we run powershell, we swear when our powershell does not do what we would like it to. To this end, we need to log the output somewhere, particularly when our scripts are not going to be run interactively so outputting to the screen becomes pointless.

Click through for a depiction (sans farm animals) of how transcripts can be useful and how you can create one.

Comments closed

Working with Query Store Hints

Published 2024-04-04 by Kevin Feasel

Etienne Lopes shares some thoughts around a new feature in SQL Server 2022:

Last December I got lucky since after upgrading from SQL Server 2012 to 2022, one of my clients had a process running in a particular database that went from around 10 minutes to around 10 hours!

You might be thinking: “It got 60 times worse, where’s the luck in that?”

Well, it presented me with a great opportunity to try a new feature in SQL Server 2022 called “Query Store Hints” (in a real production environment) and confirm the fabulous results that can be obtained by using it, with no effort at all. I thought it could be worth to share this experience and that’s what I’ll present in this post 

Click through for more details on the problem and solution.

Comments closed

Row-Level Security in Postgres

Published 2024-04-04 by Kevin Feasel

Craig Kerstiens implements row-level security:

Row-level security (RLS) in Postgres is a feature that allows you to control which rows a user is allowed to access in a particular table. It enables you to define security policies at the row level based on certain conditions, such as user roles or specific attributes in the data. Most commonly this is used to limit access based on the database user connecting, but it can also be handy to ensure data safety for multi-tenant applications.

Read on to see how you can implement it. It’s pretty similar to the way we do it in SQL Server, though SQL Server has a few parts of policy evaluation that are more explicit versus putting the execution function code itself in a USING clause.

Comments closed