Press "Enter" to skip to content

Author: Kevin Feasel

Creating Executables from Powershell Scripts

Published 2020-08-03 by Kevin Feasel

Patrick Gruenauer introduces us to an interesting tool:

Have you ever dreamed of creating exe files out of your PowerShell scripts? If yes, read on. In this post I will show you how you can create exe files with a small and simple program called ps2exe.

Read on to see how it works. Then give it a try and wallow in the fact that some Powershell-wielding production DBA who absolutely is not a developer and doesn’t write code like developers slowly realizes that the developer call was coming from inside the house.

Comments closed

Issues when Using the Power BI REST API

Published 2020-08-03 by Kevin Feasel

Nicky van Vroenhoven walks us through a few issues discovered when trying to use the Power BI REST API:

Last month when I was trying to follow the excellent video on Building a Power BI Admin View by Parker Stevens ( b | @PowerBIElite | YouTube).

However, I ran into a few things that I’d like to dedicate this quick post on.

Read on to see where Nicky ran into issues and how you might be able to avoid them.

Comments closed

Wanted: Limiting Calculation Group Members in Power BI

Published 2020-08-03 by Kevin Feasel

Marco Russo shares an annoyance in how calculation groups are currently set up:

The consumption experience following the introduction of calculation groups needs to be improved in Power BI. For example, the immediate advantage of having a time intelligence calculation group is that it is no longer necessary to create multiple variations of the same measure to implement all the possible time intelligence calculations. However, what happens when you have a matrix with Sales Amount, Total Cost, Margin, but you want to show the current value for all the measures and the year-over-year difference only for the Margin measure? Today, if you add a year-over-year calculation item to the matrix, you get the year-over-year of all the measures.

Marco has a Power BI Idea to resolve this which sounds a lot like GROUPING SETS in T-SQL.

Comments closed

Recovering SSMS Scripts After a Crash

Published 2020-08-03 by Kevin Feasel

Jonathan Kehayias shows where those recovery scripts for SSMS are located:

It happens to the best of us and this post is more of a reminder for myself the next time it happens to me than anything else.  You are working in SQL Server Management Studio, you have a few tabs open (OK, it was 123 this time, but lets stay focused on the purpose of this blog post – You should see my Desktop and all the icons on top of other icons…), and then suddenly you get the dreaded SSMS has stopped responding/crashed window.  Sure you have been saving the important things along the way, but there are plenty of tabs that were just working queries for analysis that don’t really need to be saved but you still need them.  What do you do?

Even if you do use a tool like SSMS Tools Pack or DevArt’s SQLcomplete (which is what I use), it is still good to know where these scripts are just in case. I’ve also noticed that Azure Data Studio has been quite a bit better about maintaining scripts on close.

Comments closed

Enabling SQL Server Optimizer Hotfixes

Published 2020-08-03 by Kevin Feasel

John Morehouse takes us through the step-by-step for enabling optimizer hotfixes in SQL Server:

There are a number of knobs and switches that are available to database administrators that can be used to enable better performance.  There are three options in particular that this blog will be discussing, trace flag 4199, the database scoped configuration QUERY_OPIMIZER_HOTFIXES and the qeury hint ENABLE_QUERY_OPTIMIZER_HOTFIXES. Understanding how these options function will give you a hand up on ensuring the query optimizer is running as optimally as possible.

Let’s take a look at the three options.

Read on to learn more. There is some potential risk of regression with new optimizer updates, so standard rules around testing apply.

Comments closed

Custom Windows in Apache Flink

Published 2020-07-31 by Kevin Feasel

Alexander Fedulov walks us through window options with Apache Flink:

In the previous articles of the series, we described how you can achieve flexible stream partitioning based on dynamically-updated configurations (a set of fraud-detection rules) and how you can utilize Flink’s Broadcast mechanism to distribute processing configuration at runtime among the relevant operators. 

Following up directly where we left the discussion of the end-to-end solution last time, in this article we will describe how you can use the “Swiss knife” of Flink – the Process Function to create an implementation that is tailor-made to match your streaming business logic requirements. Our discussion will continue in the context of the Fraud Detection engine. We will also demonstrate how you can implement your own custom replacement for time windows for cases where the out-of-the-box windowing available from the DataStream API does not satisfy your requirements. In particular, we will look at the trade-offs that you can make when designing a solution which requires low-latency reactions to individual events.

This article will describe some high-level concepts that can be applied independently, but it is recommended that you review the material in part one and part two of the series as well as checkout the code base in order to make it easier to follow along.

It’s worth giving this a careful read.

Comments closed

Sending Messages from SQL Agent to Microsoft Teams

Published 2020-07-31 by Kevin Feasel

Rob Sewell is waiting for a message and it comes in two parts. First up, sending SQL Agent results to a Teams channel:

Using dbatools we can create a simple script to gather the results of Agent Jobs form a list of instances. Maybe it would be good to be able to get the job runs results every 12 hours so that at 6am in the morning the early-bird DBA can quickly identify if there are any failures that need immediate action and at 6pm , the team can check that everything was ok before they clock off.

But that’s not enough for Rob:

Following on from yesterdays post about creating an overview of SQL Agent Job Results and sending it to a Teams channel, I was given another challenge

Can you write a job step that I can add to SQL Agent jobs that can send the result of that job to a Teams Channel

The use case was for some migration projects that had steps that were scheduled via SQL Agent Jobs and instead of the DBA having to estimate when they would finish and keep checking so that they could let the next team know that it was time for their part to start, they wanted it to notify a Teams channel. This turned out especially useful as the job finished earlier than expected at 3am and the off-shore team could begin their work immediately.

Read the whole thing, as Rob has some detailed code examples.

Comments closed

Finding Distance Between Cities using SQL Server

Published 2020-07-31 by Kevin Feasel

Hasan Savran wants to find geographic distances with SQL Server:

I wrote about finding distance between two location in my older post. I have been getting question about how to make the search by using data in SQL Server. In this post, I will try to answer all these questions. You do not need to have latest version of SQL Server to do any of these examples. SQL Server has been supporting Geospatial data since 2008.
     First, we need some data. Not just some data, some free spatial data. I want to show you how to find distance between cities in this post, so I need at least names of the cities and their latitude and longitude. I downloaded this data from SimpleMaps website in CSV Format.

Read on for the solution.

Comments closed

X Functions in DAX

Published 2020-07-31 by Kevin Feasel

Matt Allington explains what that “X” means in functions like MEDIANX and also builds out a problem to solve:

An X function is a class of functions in DAX that are also known as “iterators” (note, there are other iterating functions too, like FILTER, but I am only referring to the X functions here). It is a big topic on its own, and this article is not going to be the definitive guide to X functions. But I will give you a couple of insights.

I have learnt a lot about how to teach people DAX over the last 6 years, and my teaching methods have evolved over that time. I remember fondly speaking at the Microsoft Data Insights Summit with Will Thompson on the topic “DAX 50 – DAX for the rest of us“. Will said to me “don’t mention the word ‘iterator’ as it is too confusing.”. I didn’t agree with Will at the time, but his comment stuck with me. Over time I have changed the day way I teach DAX. These days I show people how to add a calculated column in a table (everyone can do that – its dead easy). Then I explain that an X function does exactly the same thing, it’s just that you can’t see the interim results materialised in front of their eyes.

Click through to see what Matt means and stay tuned for the next episode of the X Functions, where Matt reveals that there are aliens by use of bi-directional relationships.

Comments closed

Reusing a Recordset Stored as an SSIS Object Variable

Published 2020-07-31 by Kevin Feasel

Tim Mitchell appeases the masses:

A few years back, I wrote a blog post about using an SSIS object variable as a data flow source. In that post, I described how you could load a set of query results into an object-typed variable in SQL Server Integration Services and then use that in-memory data as a source within a data flow. In the comments and the feedback I got on that post, the same question kept coming up: what is the process for reusing a recordset in an SSIS object variable in the same package?

In this post, I’ll show how you can modify the scripts within your SSIS package to allow reprocessing of the same set of results in an object variable.

Tim has a nice workaround for the problem, so check it out.

Comments closed