Press "Enter" to skip to content

Author: Kevin Feasel

Including Zero on Charts

Published 2022-04-14 by Kevin Feasel

Steve Jones thinks about zero:

I’m not great at building charts and graphs. I can build a basic chart, but I often depend on the tooling I use to size, scale, etc. appropriately for whatever I’m graphing. That, or I just use a basic graph that starts from zero and has some sort of linear scale. Or I just present a table of numbers.

There are plenty of misleading charts, especially used by the media that want to show some particular aspect of data that suits the story they are reporting. Many of these misleading charts often don’t start at zero, and they end up scaling in a way that can confuse people.

Steve references a lengthy article on the topic, one which is definitely worth the read, especially because as far as I’m aware, most of the academic literature on visualization and starting at 0 ignores line charts. The only work I’m familiar with is Cleveland, McGill, and McGill, who recommended banking to 45 degrees (and here’s an example of it in SAS).

Comments closed

Code Formatting Holy Wars

Published 2022-04-14 by Kevin Feasel

Tom Zika and I are on opposite sides:

So I’ll take it one step further.
I’ll never use semicolons unless I have to.

Tools like Redgate’s SQL Prompt can add semicolons automatically, but I still won’t do it.

My quick thoughts:

  • Semi-colons? Love them. The chaotic neutral part of me wants to see Microsoft make good on their deprecation notice of code lacking semi-colons just to watch the world burn.
  • Commas go at the end because we are not barbarians.
  • Aliases should be short and sufficiently meaningful within the context of the statement. Tom and I agree here.
  • PascalCase is the best case.
  • INNER JOIN instead of JOIN because, again, we are not barbarians. LEFT OUTER JOIN instead of LEFT JOIN because, well, you guessed it.

And at the end of the day, consistency and readability are the most important things…though I’ll fight for my aesthetics like I’m the third monkey in line for Noah’s Ark and brother, it’s starting to rain.

Comments closed

The Islands T-SQL Challenge: Sequencing Islands

Published 2022-04-13 by Kevin Feasel

Itzik Ben-Gan has an interesting challenge for us:

The challenge is as follows:

Assuming partitioning based on the column grp and ordering based on the column ord, compute sequential row numbers starting with 1 within each consecutive group of rows with the same value in the val column. Following is the desired result for the given small set of sample data:

Click through for an example of this, a pair of “classic” solutions, and Itzik’s solution.

Comments closed

Costs for Managed Virtual Networks in Azure Data Factory

Published 2022-04-13 by Kevin Feasel

Martin Schoombee brings up an interesting point:

We were running SSIS in an Azure VM, spinning the VM up and down as required to run the ETL processes. A third-party SSIS component was used to extract data out of Dynamics 365 CRM, and accounted for a significant part of the yearly costs. I blogged about the reasons why I think it’s worth moving from Azure AS to Power BI PPU before, and combined with the move to Azure Data Factory I estimated a cost reduction of almost 35%.

After deploying the solution I noticed that our daily ETL costs were significantly higher than I thought it would be, and that started a little rabbit-hole exercise to figure out why.

I’m used to thinking about managed virtual networks in the case of Azure Synapse Analytics, where I think it makes a lot of sense as a default (especially because you can’t switch after you’ve made a decision).

Comments closed

Using Transactions in SQL Server

Published 2022-04-13 by Kevin Feasel

Kevin Chant has good advice:

When doing inserts, updates or delete statements using T-SQL always, and I mean always, run them inside a transaction.

You can do this in different ways in SQL Server. One of the most popular ways is to start an explicit transaction by using the begin transaction command. From there you can either commit the transaction if it is good or issue a rollback command to undo the transaction.

Read on for the why. This is excellent advice.

Comments closed

Measure Explosion and Optimization with Tabular Editor 3

Published 2022-04-13 by Kevin Feasel

Matt Allington pulls out the dynamite:

OK, so what’s the problem? The problem is that sometimes you get such a deep, nested path of measures that the underlying formula is more complex than it needs to be, and it is virtually impossible to debug. If you simply explode/expand a final measure into the full, underlying DAX code, it makes it much easier to see what is going on. Returning to the DAX example above, this is what the third measure actually looks like under the hood, removing all references to the dependent measures

Click through for an article and a video.

Comments closed

Modifying Multiple Lines of Text at Once in SSMS

Published 2022-04-13 by Kevin Feasel

Chad Callihan has some advice:

A quick T-SQL/SSMS trick that I know and love is the ability to add text to multiple lines at a time. I tend use macros in Notepad++ to accomplish these types of repetitive actions but this type of work can be completed in SSMS.

Annoyingly, this doesn’t work quite the same way in VS Code or Azure Data Studio. Alt + Down moves a line down one, Alt + Shift + Down copies the current line. To replicate this behavior in VS Code / Azure Data Studio, hold down Alt + Shift and use the mouse to select the lines you want.

Comments closed