Press "Enter" to skip to content

Day: December 18, 2023

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

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

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

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

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

SQL Server on Linux and the Built-In Administrators Group

Published 2023-12-18 by Kevin Feasel

Andrew Pruski goes messing around:

When I first started working with SQL on Linux one of the first things I did was to remove the default the [BUILTIN\Administrators] login. This is pretty much standard practice with SQL on Windows as we don’t want administrators on the server to have automatic sysadmin rights into the SQL instance.

But this login makes no sense on Linux as there is no administrators group, so it should be dropped…right?

Andrew then goes on to show us why that’s not right. Read the whole thing.

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