Press "Enter" to skip to content

Month: September 2022

Careful Batching

Published 2022-09-22 by Kevin Feasel

Michael J. Swart follows up on an older post:

When I wrote Take Care When Scripting Batches, I wanted to guard against a common pitfall when implementing a batching solution (n-squared performance). I suggested a way to be careful. But I knew that my solution was not going to be universally applicable to everyone else’s situation. So I wrote that post with a focus on how to evaluate candidate solutions.

But we developers love recipes for problem solving. I wish it was the case that for whatever kind of problem you got, you just stick the right formula in and problem solved. But unfortunately everyone’s situation is different and the majority of questions I get are of the form “What about my situation?” I’m afraid that without extra details, the best advice remains to do the work to set up the tests and find out for yourself.

Definitely read the original article first. My normal approach is the naive + index method, so I’ll have to try out Michael’s method as well next time I need to delete a chunk of records.

Comments closed

dbops Powershell Module

Published 2022-09-22 by Kevin Feasel

Kevin Chant looks at a useful Powershell module:

Before covering the dbops PowerShell module I want to quickly cover DbUp.

DbUp is a .NET library that you can use to do migration-based deployments. It is open-source and is licensed under the MIT license, which you can read about in the DbUp license file.

According to the official list of supported databases, it allows you to do migration-based deployments to various databases. Such as SQL Server and MySQL. As you will discover later in this post it also works with a newer Azure service as well.

DbUp has been on my to-learn list for a little while, though I haven’t had a chance to dig into it yet.

Comments closed

DAX OFFSET

Published 2022-09-22 by Kevin Feasel

Marc Lelijveld looks back on things:

Over the past few days, I attended the Power BI Next Step conference in Lego land – Denmark. During the keynote, Will Thompson – PM on the Power BI team, showed a new DAX function that is available to all of us already, but was very well hidden in the latest builds of Power BI Desktop. This new function, called OFFSET, allows us to do in context comparisons between two values, without writing extremely lengthy and complex DAX.

I gave it a go and in this post I share my first experiences with this new function and how I think this will make our life easier!

This looks a bit like the combination of LAG() and LEAD() in SQL Marc shows off what’s available now and notes what appears to be forthcoming.

Comments closed

Simplifying a Shrinkage Report

Published 2022-09-21 by Kevin Feasel

Mike Cisneros cleans up a complex visual made up of less complex visuals:

There are opportunities to improve this visual, but I appreciate that the graph is appropriately titled, that the legend is clear and easy to find, and that the most recent data point is the only one that is labeled. On its own, it’s an acceptable view of the data, albeit one that could be strengthened.

When this visual is considered in its greater context, however, the need to improve the legibility of this graph becomes obvious.

These charts are generated at multiple levels of geographic and thematic detail. Each one is then shared as part of one large report, with almost no visual variation from region to region, level to level, or week to week:

There’s a lot going for that redesign.

Comments closed

Conversion Precedence in SQL Server

Published 2022-09-21 by Kevin Feasel

Kenneth Fisher sorts out data types:

With the expectation that you would get back a decimal. Did you wonder why it converted the integers to decimals rather than the other way around? If not you probably should have. I’ll give you a hint. It’s not because it’s the first value in the equation, although that does make a difference.

Thought about it? In case you didn’t figure it out T-SQL (and again, I’m going to say all programing languages where implicit conversions are allowed) has a conversion precedence order. In other words in any calculation where an implicit conversion is required the datatype with a value higher on the list is converted to the datatype with the lower value on the list.

The parenthetical statement above is correct. This makes things really convenient until you have an implicit conversion you weren’t expecting. There’s an advantage to explicit-only languages, though those do involve more typing.

Comments closed

E-Mailing Power BI Query Outputs as CSV Files

Published 2022-09-21 by Kevin Feasel

Gilbert Quevauvilliers finds a way:

I recently had a requirement from a customer where they wanted a list of all Customers and the Primary Contact to be emailed to them every day.

The reason for this to be emailed daily is to ensure that when calling the customer, they know whom to speak to.

This got me thinking and I could use Power Automate to achieve this task which I detail in this blog post below.

I appreciate the ingenuity involved in getting this to work, though this also presents a good case for having this data in a warehouse, where data export to CSV would be easier.

Comments closed

Tracking Implicit Transactions with sp_whoisactive

Published 2022-09-21 by Kevin Feasel

Erik Darling is on the hunt:

But anyway, you should avoid implicit transactions as much as possible. They most often show up in the Microsoft JDBC driver queries that only people who hate you use.

When I first started to realize how bad they are, I wrote a bunch of checks into the Blitz scripts (I’ll cover those next week) that check for them.

I also opened an issue to add the check to sp_WhoIsActive, because it’s rather popular, I hear.

Click through to see how you can track them now. Also, Erik has been showcasing community procedures like sp_WhoIsActive, sp_PressureDetector, sp_QuickieStore, and sp_HumanEvents all month and he’s doing great work there.

Comments closed