Press "Enter" to skip to content

Author: Kevin Feasel

Parsing JSON and todynamic in KQL

Published 2022-09-07 by Kevin Feasel

Robert Cain reviews a rose by any other name:

In order to compact data, JSON is often used to store information within columns in databases. JSON is also useful when you want to store data, but the exact data is different on a row by row basis. Using JSON prevents the need to have a lot of extra columns that will be blank for many of the rows.

It becomes important then that a query language provides a simple method for unpacking that JSON data into useful columns. The Kusto Query Language provides that ability through the use of the parse_json scalar function. In this post we’ll look at examples of how to use it to expand data stored in JSON format.

Read on to see how this relates to the todynamic function in KQL, as well as examples of how to use parse_json.

Comments closed

Blocking during Creation of Indexed Views

Published 2022-09-07 by Kevin Feasel

Tom Zika runs into a problem:

There was a need to make changes to a table with an Indexed View. Since Indexed Views must be created with SCHEMABINDING, the View must be dropped and recreated.

From past experience, I knew that this operation blocked all queries (Read/Write) that referenced any table from the View’s definition for the duration of the Clustered index creation, even under the RCSI level.

Because the index might be large and the maintenance window small, I want to do that as fast as possible.

Tom does a good job of taking us through the problem as well as the solution. Definitely worth a read if you’re creating indexed views with Enterprise Edition.

Comments closed

Creating Server Roles Can Create Logins

Published 2022-09-07 by Kevin Feasel

Kenneth Fisher ran into an interesting issue:

One of my co-workers came to me the other day and told me that they found their network id as a login on one of our SQL Server instances. Why is that note worthy? We make a point of using AD groups if at all possible. He did a little bit more work and tracked it down to a series of commands that he had run recently. I’ll be honest with you, this was starting to feel like a couple of other cases where I’ve seen database objects and principals show up unexpectedly. One was when I created a database object without setting a default schema, and the other was when I created a database audit specification to audit a user that didn’t have an associated database principal (access was through an AD group).

This time around, it was creating a server role. Read on for Kenneth’s thoughts and how you can avoid accidentally creating logins for individual Active Directory users.

Comments closed

Finding Users in Power BI Workspaces

Published 2022-09-07 by Kevin Feasel

Tomaz Kastrun has a script for us:

Determining and checking who has access to a particular workspace in your organisation can be time consuming task. You can always retrieve the list of workspaces and access the list of all users with PowerShell cmdlet Get-PowerBIWorkspace.

The results of this cmdlet gives you a great way to get to valuable information easy and fast. And the following script can do just that.

Click through for the script, which also excludes personal workspaces. Tomaz then provides a bonus script creating a graph of how users and workspaces connect.

Comments closed

EvaluateAndLog Not Executed Due to Optimization

Published 2022-09-07 by Kevin Feasel

Jeffrey Wang wraps up a series on the EvaluateAndLog function:

This is the fourth and the last article on the EvaluateAndLog function. While DAX developers were excited about the prospect of printing the values of a portion of a DAX expression, many of the early adopters of the function were puzzled by a mysterious message they ran into from time to time: “EvaluateAndLog function is not executed due to optimization.” Another question on people’s mind is “While it’s great to be able to see the values of a sub-expression used in my calculation, is there a way for me to see the values of other expressions which are not needed to calculate the final result?” I will provide answers to both questions in this post.

Click through to understand why you might get this message and what it means.

Comments closed

Principal Type not Supported Errors in Azure SQL DB/MI

Published 2022-09-07 by Kevin Feasel

Sabrin Alsahsah takes us through a few common causes of Azure Active Directory errors in Azure SQL Database and SQL Managed Instances:

We received some support cases when customers encounter the error below while trying to add an AAD security group to their Azure SQL Database or Azure SQL managed instance. In this blog article, we will be listing a few points to be checked to troubleshoot this error and can help you to identify the cause.

Msg 33130, Level 16, State 1, Line 1

Principal ‘XXXXXX’ could not be found or this principal type is not supported.

Read on for several reasons why things might be going wrong.

Comments closed

Refreshing Power BI Datasets from SSIS

Published 2022-09-07 by Kevin Feasel

Teo Lachev solves a problem:

Scenario: You use SSIS to load data for on-prem BI solution. As a last step of the ETL pipeline, you want to refresh a Power BI dataset. There’s quite a bit of misinformation on the Internet about how to do this, hence this blog.

Read on to see how you can do this, supposing that you’re hosting in a Premium-Per-User or Premium workspace.

Comments closed

Connecting to Azure Blob Storage with SQL Server 2022

Published 2022-09-06 by Kevin Feasel

I take a look back at the past and forward to the future:

PolyBase used to connect to Azure Blob Storage. Specifically, you could use the wasbs:// protocol and connect to Azure Blob Storage over WebHDFS. Here’s an example of an external data source which would work for SQL Server 2016 through 2019:

SQL Server 2022 changes its mechanisms around Azure Blob Storage a little bit, though I think the changes are sensible.

Comments closed

Cell-Level (aka Column-Level) Encryption in SQL Server

Published 2022-09-06 by Kevin Feasel

Etienne Lopes takes us through cell-level encryption:

Securing data has always been important but as time goes by, more and more data is available all around us, some of it is considered sensitive data and it becomes a major concern to protect it somehow, in fact in certain cases it is legally mandatory to comply with certain regulations (like GDPR). SQL Server offers a few options regarding data protection (either by means of encryption or obfuscation), cell level encryption being one of them.

In this post I’ll explain what is cell level encryption along with its use cases and I’ll use a thorough demo to show how to implement it in a database and how it works

Despite Always Encrypted being available, I’m more apt to use column-level/cell-level encryption because it’s easier to implement. Well, that and the lack of Always Encrypted in Azure Synapse Analytics.

Comments closed

Getting a Power BI Dataset’s Refresh History

Published 2022-09-06 by Kevin Feasel

Chris Webb wants to know the lore:

In the last post in this series I showed how to get the status of an individual Power BI dataset refresh. In this post I’ll show how to get the history of a dataset’s refreshes and save it to a file in OneDrive for Business – useful if you want to do some analysis on refresh performance or the causes of failures.

This is one of the most straightforward things to do with the Power BI Enhanced Refresh API , especially once you’ve already got a custom connector created in the way I’ve shown in the previous posts in this series. You just need to add a new Action to your custom connector, fill in the General section:

Click through to see what to do.

Comments closed