Press "Enter" to skip to content

Curated SQL Posts

Adding Report Names to the Log Analytics Report

Gilbert Quevauvilliers feels complete:

I was really excited to use the Power BI Log Analytics for Analysis Services Engine report when it was released along with this blog post from the Power BI Team: Announcing long-term usage and performance insights (Public Preview) | Microsoft Power BI Blog | Microsoft Power BI

This is really a great report when using Log Analytics.

I found the one thing that I wanted to view was my report names. The standard report did not have this.

Protip: displaying GUIDs is not the same as displaying useful information. I recommend reading through this just to see how much pain and effort it takes to make the Log Analytics report actually become useful.

Comments closed

Using Radar Charts

Mike Cisneros explains what radar (or spider) charts are and how they work:

A spider chart, also sometimes called a radar chart, is often used when you want to display data across several unique dimensions. Although there are exceptions, these dimensions are usually quantitative, and usually range from zero to a maximum value. Each dimension’s range is normalized to one another, so that when we draw our spider chart, the length of a line from zero to a dimension’s maximum value will be the same for every dimension.

Spider charts can be found in lots of industries, but rarely in large numbers. In our experience, they’re most likely to pop up in food science (comparing products across multiple different facets of taste, texture, etc.) and in sports analytics (comparing athletes across several dimensions of performance). In one of our previous #SWDchallenges, several participants found other use cases for spider charts, such as comparing series on a time-cycle, comparing the volume of searches for different terms, or even visualizing the motifs in a piece of music.

My favorite use of the radar chart was in the Madden NFL series, where I spent far too much of my youth comparing attributes between prospects—for example, the quarterback radar chart might have throwing power, throwing accuracy, speed, strength, and awareness. Then, you compare the relative sizes and spikes of players to gauge who would be better. (Except that in the case of Madden, it was all a lie—turns out the radar charts weren’t actually based on anything, so as usual, a youth wasted).

More recently, Bruce Nolan came up with a radar chart to visualize quarterback play across a set of complementary measures:

Image
Comments closed

Running .NET Apps on Raspberry Pi 4

Joy George Kunjikkur installs the .NET runtime on a Raspberry Pi 4:

Here we are continuing the experiments with Raspberry Pi 4. As a .Net developer, what is the meaning if we cannot install .Net into RasPi and run one program?

Please note this post is aiming at installing the .Net runtime, not the SDK. Development and compilation will be done outside of RasPi. Also, this is not aiming to run ASP.Net, just simple .Net console apps only.

One other option, which Azure IoT Hub uses, is to install moby and deploy your .NET apps as containers. But if you don’t want to do that, click through for a few techniques.

Comments closed

SQLCMD and the Question Mark

Solomon Rutzky stops SQLCMD in its tracks:

This is the story of one such time that I stumbled upon something interesting, and all because I made a mistake. I was doing testing for the previous post (mentioned above) and somehow entered an extra character, or forgot the closing quote on a string literal, or something. Either way, I submitted some T-SQL in the sqlcmd utility that should have produced a parsing error. But instead, it cancelled the entire query batch, and without returning any errors.

Read on to learn more and maybe have a little fun with co-workers.

Comments closed

Large-Scale Database Schema Comaprison

Raul Gonzalez creates a process to compare database schemas across a large number of servers:

There are tools that can compare two databases in great detail, but the caveat I found, it is that they can only compare two databases, so while they’ll be fine to compare DEV<=>PROD or QA<=>PROD, they’re not a good fit to compare and find the differences in, literally, thousands of databases

There it’s where I was, so after some time trying to postpone the inevitable, it was time to roll up my sleeves and get the job done.

Click through for Raul’s explanation and process. At one point, I’d written something which used Red Gate’s Schema Compare DLL to do something similar. The big problem you run into is when there is no single, canonically correct database and you’re trying to generate one from the tangled mess of objects.

Comments closed

Search and Destroy with RegEx: Visual Studio Code Edition

Andy Levy unleashes the power of regular expressions in Visual Studio Code:

Kind of a diversion into a Visual Studio Code/Azure Data Studio tip here (this works in both, as well as SQL Server Management Studio). I’m not a regular expression guru – far from it. I use them occasionally, but usually find myself fumbling around for a bit trying to figure out just the right expression to do what I need.

I’ve known for a while that VSCode/ADS had regular expression matching built into its find/replace feature, but did you know you can also replace with it? It can help remove the tedium of doing a large amount of text processing.

It’s quite a bit different from the regular expression parser in SQL Server Management Studio, but the support of capture groups in VS Code is quite helpful, as Andy demonstrates.

Comments closed

More on SARGability

Erik Darling continues a series on SARGability. First up, max datatype columns aren’t going to cut it:

No matter how much you scream, holler, and curse, when you try to filter data in a column with a max type, that predicate can’t be pushed to when you touch the index.

Leaving aside that max data types can’t be in the key of an index, because that would be insane, even if you stick them in the includes you’re looking at a lot of potential bulk, and not the good kind that makes you regular.

Read on for an example of Erik’s point, and then go to the next post, which covers fixing functions:

The bottom line on scalar UDFs is that they’re poison pills for performance.

They’re bad enough in the select list, but they get even worse if they appear in join or where clause portions of the query.

The example query we’re going to use doesn’t even go out and touch other tables, which can certainly make things worse. It does all its processing “in memory”.

Both of these are worth checking out.

Comments closed

Explanation Anti-Patterns

Julia Evans catalogs things to avoid when providing explanations:

This list isn’t meant to make you feel bad about your writing. I’ve probably done all of these things! I’m certainly going to do them again! I even did at least one of them while writing this post!

But knowing that I’m likely to accidentally do these things makes it easier for me to avoid them, and it makes me more receptive to critique when people point out issues with my writing (“Julia, this is assuming a lot of knowledge that I don’t have!“).

Being aware of these patterns also helps me when reading a confusing explanation: “oh, I’m not confused by this explanation because I’m stupid, I’m confused because it’s introduced 6 new-to-me concepts and it hasn’t explained what any of them is yet!“.

Read on for the list and some excellent explanations of what to do and what to avoid when trying to explain things clearly to people.

Comments closed

A Primer on Apache Cassandra Reads and Writes

Utkarsh Upadhyay explains some of the internals of reading and writing with Apache Cassandra:

Apache Cassandra is a type of No-SQL database. It handles large amounts of data across many commodity servers. Being a highly scalable and high-performance distributed database, it provides high availability with no single point of failure. Here in this blog, mainly I focused on Reads and writes in Cassandra. And For Cassandra architecture, you can refer to this blog Apache Casandra: Back to Basics. So let’s get started with this blog on Apache Cassandra: Reads and Writes.

Click through for a comparison between Cassandra and MySQL, followed by a high-level architectural explanation of read and write operations in Cassandra. Though one thing which raises my eyebrow is the statement that reads in Cassandra are O(1). I don’t know that not to be the case, but I’m inclined to say it doesn’t sound right.

1 Comment

Databricks Autologging

Corey Zumar and Kasey Uhlenhuth announce a new product:

Machine learning teams require the ability to reproduce and explain their results–whether for regulatory, debugging or other purposes. This means every production model must have a record of its lineage and performance characteristics. While some ML practitioners diligently version their source code, hyperparameters and performance metrics, others find it cumbersome or distracting from their rapid prototyping. As a result, data teams encounter three primary challenges when recording this information: (1) standardizing machine learning artifacts tracked across ML teams, (2) ensuring reproducibility and auditability across a diverse set of ML problems and (3) maintaining readable code across many logging calls.

Read on to see how Databricks Autologging can satisfy these issues.

Comments closed