Press "Enter" to skip to content

Curated SQL Posts

New Release: Power BI VS Code Extension

Published 2023-10-31 by Kevin Feasel

Gerhard Brueckl has been busy:

I am working a lot with Power BI in my daily business and there have always been a couple of things that bothered me since the very beginning. Most of this is related to the web UI and its usability, mainly that you need too many clicks to get to where you want (e.g. viewing Datasets refreshes) but also that some features are simply not exposed in the UI that are possible with the Power BI REST APIs (e.g. rebinding a report to another dataset). So I thought there must be some better way to do this and make management and usability of Power BI easier and I came up with the idea for a Visual Studio Code extension for Power BI to close this gap.

Read on to see how it works, how you can get it, and what Gerhard has in mind for it over the short term.

Comments closed

Loading Data from Sharepoint Lists into Microsoft Fabric

Published 2023-10-31 by Kevin Feasel

Stepan Resl loads some data:

In a time of Fabric, it’s worth pointing out our three options for data ingestion.

  • Data Pipelines with Copy Activity
  • Dataflows Gen 2
  • Notebooks

We must compare them to understand ​​what each can offer us from different perspectives. To be able to compare them thoroughly, there are some guardrails that we need to set so that everything goes the same way.

My biggest takeaway from this is, don’t load important business data into Sharepoint Lists to begin with.

Comments closed

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