Press "Enter" to skip to content

Month: July 2024

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

Deployment and Release Strategies for Fabric CI/CD

Published 2024-07-10 by Kevin Feasel

Marc Lelijveld digs into CI/CD topics:

Recently, I wrote a blog about the new branch-out feature in Git connected Fabric and Power BI workspaces. In this blog, I will continue the topic of Git integration by discussing various setups you could consider in your Git integration, deployment and release strategies as part of your continuous integration and continuous delivery setup.

Will you connect Git only to your development workspace, or to all stages? And how do you handle your deployment? Keep reading to find out the different patterns you can consider!

Hey, I’m the one who’s supposed to tell people to read on to learn more!

Marc does a great job of laying out three patterns, so I’ll just complain a bit instead. The fact that this has been out for a year and still doesn’t support GitHub is annoying. I know that it’s scheduled to come out in Q3 of 2024, so it’s hopefully just a few months away. But it’s still annoying.

Comments closed

Unicode Characters in SQL Queries

Published 2024-07-10 by Kevin Feasel

Chad Baldwin does some interesting things with Unicode characters:

I don’t want to get too far into the weeds explaining encodings, code points, etc. Mostly because you can just Google it, but also because it’s very confusing. Despite all the hours I’ve spent trying to learn about it, I still don’t get a lot of it. There’s also a lot of nuance regarding encodings when it comes to SQL Server, different collations, and different SQL versions. However, I did come across this blog post that seems to break it down well.

Click through for a few scenarios, including things like tracking progress or marking data in a manner that’s easier to see then “True” and “False” values in columns.

Comments closed

SSMS 20.2 and a Preview of 21

Published 2024-07-10 by Kevin Feasel

Erin Stellato reaches out:

Hey SQL Tools fans!  We’re halfway through 2024 and due to a confluence of events we have a release of SQL Server Management Studio to announce, version SSMS 20.2.

While we’ve been focused on work for SSMS 21, we made time for a few updates to address known issues that affected your workflows and use of SSMS.  You can read about each update and fix in the release notes, but let’s cover some important fixes here.

Read on to see what’s new in 20.2, as well as some of the major plans in mind for SSMS 21.

Comments closed

Automating R Scripts via taskscheduleR

Published 2024-07-09 by Kevin Feasel

Steven Sanderson builds a Windows task:

Today, let’s dive into a nifty R package called taskscheduleR that can automate running your R scripts. Whether you need to execute a task every hour or just once a day, taskscheduleR has you covered. This package leverages the Windows Task Scheduler, making it a breeze to schedule and automate repetitive tasks directly from R. Let’s walk through a couple of examples from my new book, “Extending Excel with Python and R”.

Click through for those examples. Also check out Steven’s new book, that came out at the end of April.

Comments closed

Building a Data API (with POST Operations) using Data API Builder

Published 2024-07-09 by Kevin Feasel

Eduardo Pivaral digs into DAB:

In the previous tip on Data API Builder (DAB) for SQL Server, we discussed how REST APIs provide a secure and platform-agnostic method to share database information using REST or GRAPHQL and how DAB simplifies the process of creating data APIs without the need for extensive coding or third-party tools.

What can we do if we want POST operations? Is it possible to achieve? What other options do we have if we want to implement Data API solutions in our production environments?

Read on to learn more about how this works.

Comments closed

sp_delete_backuphistory Removes Restore History Too

Published 2024-07-09 by Kevin Feasel

Steve Jones susses out a problem:

I had a customer that was looking to document a restore that had occurred on one of their systems and didn’t see it. They had concerns about SQL Server accurately tracking history across time and noted they hadn’t cleaned any history.

We dug through some of their instance jobs and found one that ran sp_delete_backuphistory. The person didn’t realize this removes restore history as well. This post talks a bit about how this works.

Read on for the full story.

Comments closed

Document Templates in SQL

Published 2024-07-09 by Kevin Feasel

Sebastiao Pereira fills in the blanks:

In some industries, there is a series of repeated content stored about a specific topic, i.e., contracts, terms and conditions, legal agreements, lease agreements, bills, etc., with only minor differences. With these documents, there is a need to just replace specific keywords. What is the most efficient way to maintain consistency, data accuracy, and optimize storage?

This is a technique I’ve used a few times, and there’s no real trick to it: just pick something as your tag identifier that will never appear in the text itself.

Comments closed