Press "Enter" to skip to content

Author: Kevin Feasel

Extracting Numbers from a Stacked Density Plot

Published 2022-07-18 by Kevin Feasel

Derek Jones digs into an image:

A month or so ago, I found a graph showing a percentage of PCs having a given range of memory installed, between March 2000 and April 2020, on a TechTalk page of PC Matic; it had the form of a stacked density plot. This kind of installed memory data is rare, how could I get the underlying values (a previous post covers extracting data from a heatmap)?

Read on for an interesting attempt at reverse-engineering the original numbers used to create an image. H/T R-Bloggers.

Comments closed

Argument {0} is Null or Empty in Azure Data Factory

Published 2022-07-18 by Kevin Feasel

Richard Swinbank diagnoses a problem:

I encountered this error recently while using Azure Data Factory’s (ADF) new(ish) Script activity to run a SQL query. It took me a while to find a fix, and when it happened again two weeks later I’d completely forgotten it. With a bit of luck, writing it down will help me remember next time – and if it helps you too, great

Read on to see what the problem was and how Richard solved it.

Comments closed

Tips for the Tidyverse

Published 2022-07-18 by Kevin Feasel

Tomaz Kastrun shares some advice:

Tidyverse provides a handful of great functions for operating across multiple columns simultaneously. Across is a function, that makes it easy to apply the same transformation over numerous columns in summarise() and mutate() functions.

Across accepts two arguments; a) array of columns and b) function or list of functions to be applied to selected columns.

Check out eight tips for working with packages in R’s tidyverse.

Comments closed

Working with Synapse Link for SQL

Published 2022-07-18 by Kevin Feasel

Steve Howard gives us an overview of a preview:

Azure Synapse Link for SQL greatly simplifies analytics pipelines as Microsoft manages the orchestration process for you. Since being announced at Microsoft Build, many of you have had the opportunity to try it out in a POC so now seems like a good time to take a deep dive on some implementation aspects that may save you time later.

This blog post will assume you have experience with Synapse dedicated SQL pools and that you have some basic working knowledge of Synapse Link for SQL from following the quick start or from doing an initial POC.

Looking at this, I am a bit concerned about what it means to sync actively changing tables, especially ones large enough to benefit from being in a dedicated SQL pool. “Just reload all the data” may be the right answer but it doesn’t sound like a convenient one.

Comments closed

The Siren Song of Platform-Portable SQL

Published 2022-07-18 by Kevin Feasel

Shane O’Neill gets on the soapbox:

Part of why I’m ranting about database-ubiquitousness SQL instead of ORMs is that I’ve also witnessed SQL code that has to work everywhere

I’m going to call this SQL “DUh SQL” from now on since I only have so many vowels on my keyboard and shouldn’t waste them re-writing the word “ubiquitousness”

Them: “Nope, we can’t use DATEFROMPARTS; it’s not available in other databases! Yeah, we use GETDATE() everywhere; what’s your point? WHAT!?!”

I strongly agree with this. Lowest common denominator SQL just means your code is likely to perform poorly in all environments. Even if you know you need to support multiple platforms, it’s better to get the most out of each and have whichever data access layer implementation you inject figure out the details of how to do it.

Comments closed

Quality Checks for Power BI Visuals

Published 2022-07-18 by Kevin Feasel

Meagan Longoria has a checklist:

For more formal enterprise Power BI development, many people have a checklist to ensure data acquisition and data modeling quality and performance. Fewer people have a checklist for their data visualization. I’d like to offer some ideas for quality checks on the visual design of your Power BI report. I’ll update this list as I get feedback or new ideas.

Read on for the list, as it’s a good one. For the most part, these also apply to visuals created in other tools.

Comments closed

SQL Server 2012 Migration Plan

Published 2022-07-18 by Kevin Feasel

Lee Markum says farewell to SQL Server 2012:

Today is end of support for SQL Server 2012. May it rest in peace.

Migrating a SQL Server can be a lot of work. There are so many things to think about. It’s a pain.

It is a pain but Lee does have a few tips on how to get started with a migration plan. And as you get closer to present-day SQL Server (remember: there were 4 versions of SQL Server released after 2012 and we’re getting another one this year), being able to set up distributed Availability Groups for version migration can make life a lot easier for you.

Comments closed

Proofs of Concept and Pilots

Published 2022-07-15 by Kevin Feasel

Kenneth Fisher strikes a chord:

If your POC does not follow your companies best practices and standards then it is not a valid POC.

There are way to many settings that will change it’s performance, cause security issues, etc. On top of that, almost every POC I’ve ever seen ends up becoming the test environment if not the actual production environment. So all of those little compromises end up in your actual, non POC environment because it’s way too much work to fix them now. You should have said something when we set this up.

To use one of my favorite lines, “Short answer: yes with an if; long answer: no with a but.”

Before I get to the answer, I do want to differentiate between a proof of concept and a pilot. The idea of a proof of concept is to see if I can make this thing work. Can I get these two processes to talk to each other? Can I build a website which accepts user input and displays something? Can I get this idea from my head into code? Can I process 500,000 records per second using our existing hardware? One important thing about a proof of concept is that it always has the possibility of failure. “No” is a valid answer here based on the conditions. By contrast, a pilot is a starter for the full project. You might work with one business unit instead of all of them, migrate a small amount of traffic to the new system, or only handle data from a single branch office. Also, you want that answer as fast as is reasonable so that your business decision-makers can make business decisions on that information. By contrast, when we do a pilot, we already know the answer is yes; we just need to build it out and answer the technical details along the way.

Returning to the line above: Yes, I agree with Kenneth if your company lacks the discipline to differentiate between proofs of concept and pilots (and that’s not as denigrating a remark as it sounds…though it’s somewhat denigrating). No, do not follow the same practices for a proof of concept that you would for a full product, but you need to ensure that code gets destroyed and you start over with new code which does follow those practices.

2 Comments

Filtered XML Deadlock Reports with Extended Events

Published 2022-07-15 by Kevin Feasel

Grant Fritchey digs into a useful Extended Event:

One of my favorite little bits of information about Extended Events is the fact that everyone running a full instance of SQL Server has deadlock information available to them, even if they never enabled Trace Flag 1222 through the system_health session. That captures the xml_deadlock_report which has the full deadlock graph. However, what if you want to capture deadlock info, but, you’re dealing the GDPR, and transmitting query values could be problematic? Enter xml_deadlock_report_filtered.

Read on to see how it works, though note Grant’s warning that this is a non-documented event.

Comments closed

CREATE VIEW with Variables and the XY Problem

Published 2022-07-15 by Kevin Feasel

Ronen Ariely tackles a challenge:

how to pass parameters to view in SQL Server. I need create views with variable as below.

Create view view_name asSelect * from table where tas_id = V_Taskid

V_taskid is variable which are passing during run time from ADF pipeline

Is it possible to pass variable like above in view[?]

Ronen provides an answer but also notes that this probably isn’t the right question. In many cases, we ask for a specific detail because we think we know what to do but are stuck. In practice, we’re stuck because we’re asking the wrong questions. Most of the time, we don’t even know the right question to ask, making things even more challenging.

Comments closed