Press "Enter" to skip to content

Author: Kevin Feasel

Creating a Trust between On-Prem AD and AWS Directory Service

Published 2022-03-16 by Kevin Feasel

Tom Collins makes a connection:

Most SQL Servers use a large portion of the authentication as Windows Authentication – utilising Kerberos and NTLM protocols via Active Directory. So when it comes to considering moving on-prem SQL Server resources to Cloud Providers – Active Directory is a foundational question.    There are other methods than Microsoft Directory – which I’ll discuss in future posts.

Utilising AWS RDS SQL Server with Windows Authentication methods is only possible using the AWS Directory Service.  i.e The AWS RDS SQL Server is created and added as a resource to the AWS Directory Service . If on-prem users require access to the AWS RDS SQL Server via Kerberos , a forest trust is required between the AWS Directory Service and the on-prem AD. 

For this post – the focus is on an existing on-premises SQL Server inventory using Microsoft Active Directory Services.

Read on to see what you’d need to do to implement this.

Comments closed

The Cost is a Lie

Published 2022-03-16 by Kevin Feasel

Erik Darling doesn’t need your costs:

Costs are okay for figuring out why SQL Server’s cost-based optimizer:

– Chose a particular query plan

– Chose a particular operator

Costs are not okay for figuring out:

– Which queries are the slowest

– Which queries you should tune first

– Which missing index requests are the most important

– Which part of a query plan was the slowest

Yep. And read on for more information.

Comments closed

Materializing Views on Materialized Views

Published 2022-03-16 by Kevin Feasel

Drew Furgiuele is asking for it:

Consider this: you’ve developed a data ingestion strategy that is taking in remote thermostat readings. Usually, the devices report in on a set frequency and you’re able to calculate aggregate readings an hourly interval. A materialized view could be created that does this calculation and stores the results out for querying. But what if something causes some of this data to become duplicated? You’d first have to eliminate these duplicates, re-ingest the data, and then do your calculations again.

This is where we can leverage creating a materialized view over a materialized view. Our first materialized view will handle the deduplication, and our second can handle the aggregation of the deduplicated data.

Yo dawg, I heard you like materialized views, so I put some materialized views in your materialized views so you can materialize views while you materialize views.

Comments closed

TIMMEAN() in Power Query

Published 2022-03-16 by Kevin Feasel

Imke Feldmann has another Excel function to convert:

TRIMMEAN is a statistical function in Excel that calculates the “mean taken by excluding a percentage of data points from the top and bottom tails of a data set”. So you can use it if you want to exclude potential outliers from your data. Daniil Maslyuk has a nice approach for it in DAX, but in here I want to share my M version for Power Query for it.

Read on for the function and how you can use it.

Comments closed

Dropping Offline Databases in SQL Server

Published 2022-03-16 by Kevin Feasel

Chad Callihan has a couple notes about dropping offline databases:

“Oops” is not a word you want to utter often as a database administrator. Sure, accidents happen (who hasn’t missed that WHERE clause when deleting data) but there are steps you can take to minimize problems that will ruin your day, week, etc. Let’s look at a few ways to delete databases that supposedly are no longer needed.

“I’m feeling lucky” isn’t something most DBAs choose.

Comments closed

Checking R Function Inputs

Published 2022-03-15 by Kevin Feasel

Hugo Gruson, et al, share some techniques for ensuring function input matches your expectations:

Are you, like we were, tired of filling your functions with argument checking code that sometimes ends up being longer that the core of the function itself? Are you trying to find what is the most efficient approach to check inputs easily and without forgetting any edge cases? Read about our exploration into the various ways to check your function inputs in R in this blog post. And please share your own tips and discoveries in the comment section!

Read on for several techniques.

Comments closed

Understanding Data Mesh

Published 2022-03-15 by Kevin Feasel

Rick Spurgeon has a video series:

Decentralized architectures continue to flourish as engineering teams look to unlock the potential of their people and systems. From Git, to microservices, to cryptocurrencies, these designs look to decentralization as a method of breaking apart centralized bottlenecks. Data mesh is an approach to data and organizational management centered around decentralizing control of data itself. In this post, we’ll look at a Confluent Developer video-led course that tackles the big concepts and walks you through creating your own data mesh using event streams and Confluent Cloud.

Click through for a 9-part video series.

Comments closed

Measurement Units in R

Published 2022-03-15 by Kevin Feasel

K.H. Kim has an article on unit conversion in R:

Data mtcars has a column named mpg. mpg means miles per gallon. ‘Mile’ and ‘gallon’ are units for length and volume. A mile is approximately 1.6 kilometers and a gallon is approximately 3.7 liters. Mile and gallon sound unfamiliar to people who live outside England or U.S.A. because international standard units for length and volume are meter and liter.

In this post, we will learn how to convert a unit to another unit, for instance, we will convert mpg to km/L, which is more comprehensible to people who use SI units

This is something that I’m surprised languages don’t do more of. F# has the concept of units of measure but these are compile-time—at runtime, you can still break the rules. And in most languages, type systems are so simplistic that it’s all just plain decimal math. H/T R-bloggers.

Comments closed

Working with SIDs in Powershell

Published 2022-03-15 by Kevin Feasel

Jeffery Hicks translates security identifiers:

As usually happens during my day, I get sidetracked to another issue, and before you know it, I have a new PowerShell tool. In this instance, I was looking at event logs using Get-WinEvent. One of the event record properties is a UserID.

That’s very nice, but who is this? In this particular instance, the UserID property is SecurityIdentifier object.

Read on to see how to translate a SecurityIdentifier into something human-understandable.

Comments closed

Returning Blank instead of Zero in Power BI

Published 2022-03-15 by Kevin Feasel

Marco Russo does a bit of transformation:

In matrix visuals, Power BI usually hides rows where all the measures return a blank value. To leverage this behavior or simply to change the visualization of a measure depending on its result, you might want to achieve one of the following:

– Transforming a blank result to zero: this is covered in the article, How to return 0 instead of BLANK in DAX.

– Transforming a zero result to blank: this is the scenario described in this article.

Click through to see what the process looks like.

Comments closed