Press "Enter" to skip to content

Author: Kevin Feasel

100 Estimated Rows

Published 2023-12-19 by Kevin Feasel

Deborah Melkin finds the optimizer in a “Dunno, here’s a guess” scenario:

A little while back, my coworker was having trouble with a query and asked me about a strange thing they were seeing in the execution plan: The estimated rows for the query was showing 100.

100 felt like an awfully specific number. And there were two scenarios I knew of where the SQL Server cardinality estimator immediately used that value – table variables and multi-statement table value functions with SQL 2016 compatibility or earlier or as part of an APPLY where interleaved execution doesn’t apply instead of a JOIN.

The statement in question didn’t use either so what was the issue?

Read on for the solution.

Comments closed

Trying to Load a Table in Microsoft Fabric

Published 2023-12-19 by Kevin Feasel

Eugene Meidinger walks onto a field of rakes:

Last week, I struggled to load the data into Fabric, but finally got it into a Lakehouse. I was starting to run into a lot of frustration, and so it seemed like a good time to back up and get more oriented about the different pieces of Fabric and how they fit together. In my experience, it’s often most effective to try to do something, review some learning, and alternate. Without a particular pain point, it’s hard for the information to stick.

Read on for some thoughts on andragogy, learning paths, and travails loading data.

Comments closed

Join Functions in DAX

Published 2023-12-19 by Kevin Feasel

Marco Russo and Alberto Ferrari join datasets together:

Readers with knowledge of SQL know that the join operation is widespread in SQL queries, as it is the standard way to combine data stored in different tables. It is however uncommon to explicitly join tables in DAX because the relationships in the data model provide enough information to allow many DAX functions to work without an explicit join operation. Most of the time, the join between tables is implicit and automatic.

However, DAX has two explicit join functions: NATURALLEFTOUTERJOIN and NATURALINNERJOIN. Apparently, these functions correspond to the behavior of LEFT OUTER JOIN and INNER JOIN in SQL. However, they differ from SQL in how you specify the join condition. This article shows how these functions can be used in DAX with practical examples. If you need a more introductory article about the syntax of these functions, read From SQL to DAX: Joining Tables, where we compare the SQL syntax with similar DAX functions.

Click through to learn more about how these functions work and what their limitations are.

Comments closed

Fighting Heteroskedasticity in Regression Problems

Published 2023-12-18 by Kevin Feasel

Steven Sanderson deals with my favorite failure of BLUE (mainly because I love the name):

Tired of your least-squares regression model giving wonky results because some data points shout louder than others? Meet Weighted Least Squares (WLS), the superhero of regression, ready to tackle unequal variance (heteroscedasticity) and give your model the justice it deserves! Today, we’ll dive into the world of WLS in R, using base functions for maximum transparency. Buckle up, data warriors!

Read on to see how Weighted Least Squares helps in data analysis when you have heteroskedasticity.

Comments closed

Warehousing and Power BI in Microsoft Fabric

Published 2023-12-18 by Kevin Feasel

Tomaz Kastrun continues a series on Microsoft Fabric. Day 15 covers building a warehouse:

I have named my as “Advent2023_DWH”.

You can create a warehouse using T-SQL scripts, from data flow gen2, from data pipelines and from the sample data. Let’s select the sample data and grab a coffee.

Day 16 looks at data pipelines:

With the Fabric warehouse created and explored, let’s see, how we can use pipelines to get the data into Fabric warehouse.

In the existing data warehouse, we will introduce new data. By clicking “new data”, two options will be available; pipelines and dataflows. Select the pipelines and give it a name.

And Day 17 provides a primer on how Power BI can read Fabric assets:

Within the Power BI in Fabric, you will find many of the components, that can be used to create a final report. And here are the components:

Comments closed

SSMS and the Default Web Browser

Published 2023-12-18 by Kevin Feasel

Erin Stellato explains why things have to be so difficult:

If you have installed SQL Server Management Studio 19.1 or higher and encountered an Internet Explorer dialog with the message “Can’t reach this page” when trying to login using Microsoft Entra authentication (previously known as Azure Active Directory authentication), this post is for you.  An example of what this error looks like is below.  If you haven’t encountered this error and have no issues invoking a web browser from SSMS, then you can stop here and move on to something else!  But if you’re interested, feel free to keep reading.

I think the decision that the tools team made here is the right one: default to using the system browser, but you can see the kind of problems that can cause some environments. It’s hard to write a tool intended to work in a large variety of environments, including highly secured ones.

Comments closed

Resource Governor and Azure SQL Managed Instance

Published 2023-12-18 by Kevin Feasel

Kendra Little has a note for us:

In Azure SQL Managed Instance, you get to use Resource Governor, even in the General Purpose tier. This is awesome.

Just make sure you execute commands in the context of the master database, or you’ll get error 40510: Statement 'ALTER RESOURCE GOVERNOR' is not supported in this version of SQL Server.

Read on for more information about this error and how to circumvent it.

Comments closed

Delta Table Incremental Refresh in Power BI

Published 2023-12-18 by Kevin Feasel

Chris Webb shows off a bit of functionality:

One of the coolest features in Fabric is Direct Lake mode, which allows you to build Power BI reports directly on top of Delta tables in your data lake without having to wait for a semantic model to refresh. However not everyone is ready for Fabric yet so there’s also a lot of interest in the new DeltaLake.Table M function which allows Power Query (in semantic models or dataflows) to read data from Delta tables. If you currently have a serving layer – for example Synapse Serverless or Databricks SQL Warehouse – in between your existing lake house and your import mode Power BI semantic models then this new function could allow you to remove it, to reduce complexity and cut costs. This will only be a good idea, though, if refresh performance isn’t impacted and incremental refresh can be made to work well.

Click through to learn more about the performance of this operation and how it all works.

Comments closed

Password Handling in Powershell Automation Scripts

Published 2023-12-18 by Kevin Feasel

Ajay Dwivedi gives us a tip:

I have been writing automations using PowerShell for many years now. One common issue I notice with people’s code is the improper handling of passwords. In this blog, I share how to set up a Credential Manager on a SQLServer and use the same for handling passwords in automation.

To setup Credential Manager, we need to download and execute the following steps using scripts from my Github repo SQLMonitor.

One point I’d like to clarify in Ajay’s scripts is that the passwords in the database aren’t hashed. Hashing is a one-way operation, so you’d never be able to decrypt it with a passphrase. The password is encrypted and the passphrase isn’t a salt—salts are a way of making a hash unique from the plaintext to prevent multiple users with the same plaintext password from having the same salt. Encryption instead of hashing is the correct answer here because you need the plaintext of the password to perform the automated operation.

As for ENCRYPTBYPASSPHRASE(), it’s okay if you’re running SQL Server 2017 or later. For 2016 and earlier, it uses Triple DES with 128 bit key length and that’s no good.

I’d also look into the Powershell SecretStore module and possibly integrate into an existing key vault if you have one.

Comments closed