Press "Enter" to skip to content

Curated SQL Posts

Using Managed Identities with Azure Functions

Published 2023-01-06 by Kevin Feasel

Dennes Torres takes us through the proper use of managed identities:

Let’s talk about authentication between Azure Functions and resources used by Azure Functions and conclude with many poorly documented secrets about how to use User Assigned Managed Identity. When we build Azure functions, they usually need to authenticate against other Azure resources: Azure SQL Database, Storage Accounts, Service Bus and many more.

Each of these services have an authentication that we can call “Meh!”: Azure SQL has SQL Standard Logins, storage accounts have SAS tokens, service bus, shared access keys and so on. These are not the safest methods possible. If the key leaks, you will have a security problem because anyone with the key will be able to access the resource.

There are multiple solutions for this problem, some of them would pass through Key Vault, used to store secrets, keys and passwords. But let’s go directly to the best one: Remove the usage of keys at all. 

Read on to learn how.

Comments closed

Translation in Power BI via Cognitive Services

Published 2023-01-06 by Kevin Feasel

Leila Etaati gets lost in translation:

There is a possibility to call cognitive service for translation inside Power Query.

I will use this to translate 3000 rows of data about people arrested in Iran for protesting; This information contains city Name, Fullname and Other statements.

In this article, I will show how to call cognitive services for translation, create a proper JSON call and finally, use it inside Power Query.

Read on for the translation in Power Query, specifically from Farsi to English.

Comments closed

Common Table Expressions in MySQL

Published 2023-01-06 by Kevin Feasel

Robert Sheldon looks at the syntax for common table expressions in MySQL:

As with many relational database management systems, MySQL provides a variety of methods for combining data in a data manipulation language (DML) statement. You can join multiple tables in a single query or add subqueries that pull data in from other tables. You can also access views and temporary tables from within a statement, often along with permanent tables.

MySQL also offers another valuable tool for working with data—the common table expression (CTE). A CTE is a named result set that you define in a WITH clause. The WITH clause is associated with a single DML statement but is created outside the statement. However, only that statement can access the result set.

The syntax is very similar to that of SQL Server save for an explicit RECURSIVE clause rather implicit recursion as in T-SQL.

Comments closed

K-Fold Cross-Validation in Python

Published 2023-01-05 by Kevin Feasel

Shanthababu Pandian gives us a primer on k-fold cross-validation:

In each set (fold) training and the test would be performed precisely once during this entire process. It helps us to avoid overfitting. As we know when a model is trained using all of the data in a single shot and gives the best performance accuracy. Resisting this k-fold cross-validation helps us to build the model as a generalized one.

To achieve this K-Fold Cross Validation, we have to split the data set into three sets, Training, Testing, and Validation, with the challenge of the volume of the data.

Read on for the explanation and an example.

Comments closed

Converting SSRS Reports to Power BI Paginated Reports

Published 2023-01-05 by Kevin Feasel

Olivier Van Steenlandt makes a move:

In this blog post, we will be going through the process to convert a Reporting Services report to a Power BI Paginated report and deploy it in the end.

For this blog post, I have created a very basic SSRS report named Product Sales Overview. This report gets his data from an Azure SQL Database which contains the default dummy database (AdventureWorksLT).

Read the whole thing. It makes me wonder, though, if there’s an automated process for doing these conversions, especially if you have hundreds or thousands of reports.

Comments closed

Power BI Licensing Guide

Published 2023-01-05 by Kevin Feasel

Reza Rad busts out the calculator:

Licensing in Power BI comes with many options. Understanding which features are included in which licensing plan is always a question for users. In this article and video, you will learn about all the different licensing plans in Power BI, the scenarios for which to use the licensing, and scenarios for which you may need to change your licensing. This article and video are intended to help you to decide the most cost-effective licensing plan for your requirement.

Reza goes into great detail in the post and then answers a lot of questions in the comments as well.

Comments closed

Cosmos DB to Data Explorer Synapse Link

Published 2023-01-05 by Kevin Feasel

Vicent-Philippe Lauzon makes an announcement:

We recently made our new Kusto data connection available in public preview:  Cosmos DB to Azure Data Explorer Synapse Link.

This does look like a marketing-heavy announcement but the short version is that you can ingest data from Cosmos DB into Data Explorer pools via Synapse Link rather than creating your own ETL process. The previous Cosmos DB connector for Synapse Link tied to a dedicated SQL pool.

Comments closed

Finding Unused Indexes on an Instance

Published 2023-01-05 by Kevin Feasel

Matthew McGiffen goes searching:

The script uses the undocumented sp_MSforeachdb internal stored procedure to iterate through all the databases (excluding the system ones) and store the results of a standard unused indexes script into a temp table. The table can then be queried to analyse the results.

This script comes with a caveat, that the sp_MSforeachdb procedure can be unreliable and on occasion skip databases so you should check that the results include all the databases you are interested in. A simple count of distinct database names, and comparing that against the number of databases on your instance should be sufficient.

That caveat aside (and if you are concerned about it, check out Aaron Bertrand’s sp_foreachdb), click through for the script. I do like that this limits you to just zero-read indexes, as even write-heavy indexes may still be useful.

Comments closed