Press "Enter" to skip to content

Curated SQL Posts

Plotting Logistic Regression in R

Published 2023-10-30 by Kevin Feasel

Steven Sanderson performs a logistic regression:

Logistic regression is a statistical method used for predicting the probability of a binary outcome. It’s a fundamental tool in machine learning and statistics, often employed in various fields such as healthcare, finance, and marketing. We use logistic regression when we want to understand the relationship between one or more independent variables and a binary outcome, which can be “yes/no,” “1/0,” or any two-class distinction.

Click through to learn how to do this.

Comments closed

Ensure Azure Backup Retention Periods via Azure Policy

Published 2023-10-30 by Kevin Feasel

Sabrin Alsahsah keeps backups around:

In this blog article, we will cover how to prevent and deny the automated backup retention days for Azure SQL database to be lower than X number of days.

We have received some service requests from customers that would like to ensure that they have for example 14 days as a retention period for their automated backup and would like to do this using a custom policy since they have multiple databases.

You can follow the steps below to create a custom policy and ensure this:

Click through for that process.

Comments closed

ApplicationContext Updates for Power BI

Published 2023-10-30 by Kevin Feasel

Chris Webb shares an update with us:

If you’re a fan of using Log Analytics for monitoring Power BI activity, then you may have noticed there’s some new data in the ApplicationContext column of the PowerBIDatasetsWorkspace table. Up until recently the ApplicationContext column only contained IDs that identify the report and the visual that generated a DAX query (something I blogged about here); it now contains additional information on the type of Power BI report that generated the query and an ID for the user session.

This is quite useful for seeing not just that people are using your Power BI services, but also who and what they are using to consume the information. Though do beware Chris’s warning about consumption method before you get too excited.

Comments closed

Enabling Python and R Support for VS Code Polyglot Notebooks

Published 2023-10-30 by Kevin Feasel

Joy George Kunjikkur enables a preview option:

Obviously, we should have Polyglot notebooks up and running. The first step to enable Python preview is that we need to install Jupyter on the machine and make sure the Python kernel spec is available. Run the below command to make sure it is there.

It looks like what the preview is doing is shelling out to Jupyter notebooks, so I’d imagine variables won’t cross over between languages.

Comments closed

Row- and Column-Level Security in Microsoft Fabric Warehouses

Published 2023-10-30 by Kevin Feasel

Maheswaran Arunachalam gives us an update on data security in Microsoft Fabric:

Row-Level Security (RLS) and Column-Level Security (CLS) are methods that simplify the design and coding of security in applications by imposing restrictions on data access. CLS allows for specific users to access only certain columns of a table relevant to their department, protecting sensitive data. RLS, on the other hand, restricts users to accessing only data rows pertinent to their role or department. Both methods locate the access restriction logic in the database tier, applying the restrictions every time data access is attempted from any tier, making the security system more reliable and robust.

Click through for the process. This is pretty much the same as any SQL Server-based system.

Comments closed

Building a Bland-Altman Plot in R

Published 2023-10-27 by Kevin Feasel

Steven Sanderson performs a comparison:

Before we dive into the code, let’s briefly understand what a Bland-Altman plot is. It’s a graphical method to visualize the agreement between two measurement techniques, often used in fields like medicine or any domain with comparative measurements. The plot displays the differences between two measurements (Y-axis) against their means (X-axis).

Click through to see how this works and how you can interpret the results.

Comments closed

Trying out Batch Mode on Rowstore

Published 2023-10-27 by Kevin Feasel

Etienne Lopes has some fun with a feature:

Before 2012, creating analytical queries (that usually scan many rows and have lots of aggregations) from big OLTP databases to feed real-time based reports used in decision making processes, could be quite challenging. Then ColumnStore Indexes arrived and they’ve been enhanced overtime, offering amazing gains both in performance and storage.

Unfortunately, regarding pure OLTP databases, there are many situations in which ColumnStore Indexes can’t (or won’t) be used. There are some great performance enhancements present in columnstore that’s for sure and today I’m going to speak about one that became automatically available since SQL Server 2019 for “traditional” RowStore tables. It’s called “Batch Mode on Rowstore” and it can really boost some of our analytical queries over the “traditional tables” without any effort from our side!

There are a series of specific rules you need to hit but if you hit them, I’ve noticed about a 3x performance gain with you doing nothing at all.

Comments closed

Analyzing Dependencies in Microsoft Fabric Measures

Published 2023-10-27 by Kevin Feasel

Sandeep Pawar has an interesting library for us:

In my previous blog post, I introduced Semantic-Link, discussing its use cases and explained how it enables us to create solutions that were either not possible or not easily achievable before. In this blog post, I would like to present another powerful use case that, although possible in the past, could not be created and used seamlessly in Power BI. Allow me to introduce the MeasureMaze Python library, which helps uncover insights from a complex maze of dependencies in a Power BI semantic model using Semantic-Link and the power of network analysis.

Sandeep dives into the approach and what you get out of this library. It’s very interesting.

Comments closed

Automating SQL Dump Analysis

Published 2023-10-27 by Kevin Feasel

Sean Gallardy has been busy:

One of the things that is lacking in the community is for some simple dump analysis for SQL Server, well no longer!

Introducing the Beta version of SQL Dump Analysis… sure it’s not super pretty, sure it’s not 100% yet… but it’ll give you the basic goods and get you on your way.

This is a website you upload SQL dumps to, rather than an executable you’d download. Still, check it out.

Comments closed