Press "Enter" to skip to content

Month: March 2023

Tips for Power BI Modeling with ADX

Published 2023-03-16 by Kevin Feasel

Dany Hoter shares some tips on creating star schema models with Azure Data Explorer:

Relationships between DQ tables are created as M:M by default. This is not a problem and even recommended with single direction.

Read on for several tips. What’s interesting as I read this is just how radically different the advice is for ADX utilization versus Power BI utilization, such as using strings to join dimensions to facts. That would be heresy in a Kimball-style model and is a common cause for slow-down in Power BI. Yet that’s the recommendation here for working with ADX, unless I’m misunderstanding Dany’s post.

Comments closed

Using the Log Replay Service to Migrate to Azure SQL MI

Published 2023-03-16 by Kevin Feasel

Rob Carrol makes a move:

The Log Replay Service (LRS) is a new Azure service that allows you to migrate your databases from SQL Server on-premises, SQL Server on Azure Virtual Machines, Amazon EC2, Amazon RDS for SQL Server, or Google Compute Engine to Azure SQL Managed Instance. LRS is a free cloud service that uses log shipping technology to enable custom migrations of databases from SQL Server 2008 through 2022.

Read on for some configuration options and tips on how to use the service.

Comments closed

Power BI Group By Columns

Published 2023-03-15 by Kevin Feasel

Marco Russo and Alberto Ferrari bundle things together:

In Power BI you can specify the unique identifier of a column value by using another column or another set of columns. This feature is currently used by the Fields Parameter feature in Power BI, but it may also be used for other purposes in a model. However, there are several limitations – such as the incompatibility with MDX queries – that reduce one’s ability to use Group By Columns property in many scenarios, so it cannot be used with Excel as a client.

Read on to learn more about how grouping works in Power BI and some of the limitations.

Comments closed

QA Refreshes via CI/CD

Published 2023-03-15 by Kevin Feasel

Hiram Fleitas rebuilds the QA environment:

In this post I am going to cover how to automatically refresh a lower environment commonly used for testing as part of your release (CD) pipeline.

Well, why? – you may be asking.

  1. In some cases, developers and testers need to test their application code-changes against a fresh copy of production-like data. This helps them do validations prior to publishing their changes to production where their apps are bombarded by end-user live workloads.
  2. Also, the lower environment may be used for testing, and we can’t overwrite the test data constantly. It needs to be a hot-standby refresh, made available when necessary.

Click through for notes on the process.

Comments closed

Investigating Powershell Object Members

Published 2023-03-15 by Kevin Feasel

Jeffrey Hicks wants to know what he can do:

A few weeks ago, I was working on content for a new PowerShell course for Pluralsight. The subject was objects. We all know the importance of working with objects in PowerShell. Hopefully, you also know that the output you get on your screen from running a PowerShell command is not the whole story. Formatted presentation is separate from the underlying objects in the pipeline. That’s why it is important to know how to use Get-Member to discover how an object is defined.

But, as Jeffrey points out, this doesn’t work for static members. Read on to learn what does.

Comments closed

RBAC with Kubernetes

Published 2023-03-15 by Kevin Feasel

Mercy Bassey locks down some containers:

Have you been searching for a way to manage your resources effectively in Kubernetes? Why not consider Kubernetes Role-Based Access Control (RBAC)? With Kubernetes RBAC, you can securely manage containers.

Kubernetes RBAC allows administrators like yourself to define roles with specific permissions to access resources in a Kubernetes cluster. And in this tutorial, you will learn how to create a user and define roles with specific permissions.

There are enough steps involved that I’d definitely want to manage this at the group level.

Comments closed

DBCC CHECKALLOC

Published 2023-03-15 by Kevin Feasel

Steve Stedman helps us understand a DBCC command:

DBCC CHECKALLOC is a database console command (DBCC) in Microsoft SQL Server that can be used to check the allocation and structural integrity of the data and index pages in a database. Checking the allocation and structural integrity of the pages can be useful for identifying and correcting issues with the database that could cause errors or performance issues.

Read on to learn more and see a couple examples of it in action.

Comments closed

End of Month in Snowflake and SQL Server

Published 2023-03-15 by Kevin Feasel

Kevin Wilkie is ready for that end-of-month paycheck:

When you work with data, you’ll probably need to work with dates at least once a month. That is the nature of the beast. Today, let’s compare working with them in SQL Server and Snowflake. I want to focus only on adding and subtracting months when provided with a specific day.

Along the way, I would also push for a calendar table, so that you can remove some of the more difficult (or even most common) date calculations.

Comments closed

The Importance of Naming Constraints in SQL Server

Published 2023-03-15 by Kevin Feasel

Eitan Blumin gives everything a name:

This article was published by Aaron Bertrand a few years ago, talking about system-named constraints in SQL Server.

The article mostly focuses on the issue of naming conventions as the main issue with system-named constraints and provides a useful stored procedure script to generate sp_rename commands for all system-named constraints.

However, the script in the article provides the solution for only one database and doesn’t support the new “Edge Constraints” that were introduced in SQL Server 2019.

Check out Aaron’s article and Eitan’s follow-up piece.

Comments closed

Thoughts on Linear Regression

Published 2023-03-14 by Kevin Feasel

John Mount shares some thoughts:

I want to spend some time thinking out loud about linear regression.

As a data science consultant and teacher I spend a lot of time using linear regression and teaching linear regression. I have found each of these pursuits can degenerate into mere doctrine or instructions. “do this,” “expect this,” “don’t do that,” “you should know,” and so on. What I want to do here is take a step back and think out loud about linear regression from first principles. To do attempt this I am going to start with the problem linear regression solves, and try to delay getting to the things so important that “everybody should known them without question.” So let’s think about a few things in a particular order.

For thinking out loud, this is laid out rather well, so give it a read.

Comments closed