Press "Enter" to skip to content

Curated SQL Posts

Installing Prometheus Exporter for Windows Clients

Published 2022-04-20 by Kevin Feasel

Jamie Wick exports some data:

Prometheus is an open-source monitoring solution that our Linux team has been using for several years. More recently, we began using it for our Windows-based servers too. (I’ll post a writeup about Prometheus in the future)

One of the obstacles to implementing Prometheus monitoring on our Windows servers was finding and installing an agent. We ultimately decided to use the windows_exporter agent available in the Prometheus Community on GitHub. The exporter is free to use under an MIT license and supports an extensive list of WMI metrics that are grouped into Collectors.

Read on for more info, including ways to avoid common errors.

Comments closed

Imagining a SaaS Plane for Data Mesh in Azure

Published 2022-04-20 by Kevin Feasel

Paul Andrew shares some deep thoughts:

For part 7 of this series, I want to explore what else could be delivered in our Azure Data Mesh if we continue our established thinking around the planes of interaction for our data products. As with part 6, we are still missing good Azure Resources that can deployed for certain situations. However, I want to frontload some concepts now, so we are ready if/when a suitable technical answer arrives in the cloud.

Note that this is all speculative. It’s interesting speculation, though.

Comments closed

Seeing Top N in Power BI

Published 2022-04-20 by Kevin Feasel

Reza Rad does some filtering:

I have previously written articles about how you can write a measure in DAX that helps with TOP N filtering. However, you may not need that calculation for many situations. If all you want is just simply to get the top 10 customers based on the sales amount, or bottom 5 products, etc, then you can simply use the visual-level filter GUI to perform this filtering. This is not a new functionality in Power BI, However, many users might not have yet seen it, so I’ll explain it in this short article and blog.

Read on to understand when you can use this and when you should go to TOPN() in DAX.

Comments closed

Cross-Subscription Restore for Dedicated SQL Pools

Published 2022-04-20 by Kevin Feasel

Steve Howard announces some good news:

We are excited to announce the release of cross-subscription restore. This has been one of our top requested features from customers as it unlocks multiple scenarios from dev/test to simplified billing at the subscription level for restored data warehouses.

Click through to see how you can do this. There was a workaround in the past but this should be quite a bit faster.

Comments closed

Currying and Partial Application

Published 2022-04-19 by Kevin Feasel

Prakhar explains the difference between currying and partial application:

Currying simply means converting a function taking more than one parameter can be into a series of functions with each taking one parameter. Example:

Click through for an example, as well as the difference between currying and partial application. As for why currying is important, this is how we tie together the concept of mathematical functions, which require exactly one parameter (a function being defined as, for every value of the domain, there is one and only one value of the range), with computer science functions, which may have multiple parameters. Currying allows us to bridge that gap without needing to write loads of intermediary functions.

Comments closed

Azure Redis Tips

Published 2022-04-19 by Kevin Feasel

Arun Sirpal enumerates some advice:

My learnings on Redis thus far which you may find useful:

1. Location of Redis should be close to your app.

2. Data structures within Redis, larger key value sizes lead to fragmentation of memory space and these larger memory requirements means more network data transfer, Redis states to use 100KB maximum, this will affect the transfer time allocated from the app. It could time out if the data request is big.

Click through for the rest of Arun’s advice. My advice on the 100KB maximum is that it really should be closer to 100 bytes or 1KB max in practice, especially for storing data which differs by entity (user, customer, organization, whatever your domain uses).

Comments closed

Animated SQL: Visualizing Query Operations

Published 2022-04-19 by Kevin Feasel

Steve Jones looks at an interesting site:

While I think SQL is interesting, I know some people struggle with the way the language work. Someone at work posted a link to this site: https://animatesql.com/

I think the idea is this site helps you visualize how a SQL query works. It’s not free form, and I can’t just write any SQL, but you choose a keyword and then a sample query is shown. If you press Visualize, it walks through how this query is processed.

Click through to see how it works and Steve’s thoughts. It looks like they’re using either MySQL or Postgres in the background; it’s hard to tell because both support all of the site functionality including LIMIT/OFFSET (versus TOP and OFFSET/FETCH). Sadly, it’s pretty limited in terms of the queries supported—for example, I tried adding in a quick ROW_NUMBER() window function and that did not go over well. Still, I like this a lot as a teaching tool, especially for people brand new to SQL and haven’t sorted out how to think in sets.

Comments closed

SQL Audit for STIG Compliance

Published 2022-04-19 by Kevin Feasel

Tracy Boggiano has proof of existence:

Recently I spent months of my lift working on STIG and CIS compliance at my job and one of those tasks was setting up SQL Audit for STIG.  Now, that might seem like a trivial task after all don’t you just have to create an audit and audit specification and let it run.  If only it were that easy, some of the specifications can have a significant performance impact on your system depending on the type of activity happening and if you happened to lucky enough to have a monitoring software setup your will be logging even more data that doesn’t make sense to log.  In addition, on my system we are using SQL replication and that activity due to volume doesn’t make sense to log.  So, let’s walk through my setup and how I got there, the how I got there being the most important part so you can figure out how to use filters to setup a SQL audit that does that kill your performance.

Read on for the audit specification and server audit scripts, as well as some details on how to read from server audits.

Comments closed

Automating Table-Level Refresh in Power BI

Published 2022-04-19 by Kevin Feasel

Dennes Torres digs into a challenge:

The refresh schedule on the Power BI portal is made at the Dataset level. This means all the tables refresh on the same schedule. Some models may not have a problem with this, but many models will. This article explains how to automate table level refresh in Power BI.

This refresh schedule means you will be creating a bigger workload than you really need compared to a refresh at the table level if it were possible.

There are some options to ignore and work-around this, and there is one option which will require more effort but can solve the problem. This article will analyse these options and go deeper into how to build custom refresh automation solutions.

Read on for a detailed solution.

Comments closed

Well-Architected Framework for IoT

Published 2022-04-19 by Kevin Feasel

Ben Brauer announces the Well-Architected Framework for IoT devices on Azure:

The IoT workload guidance outlines the core principles that facilitate a well-architected IoT solution and provides recommendations for each of the 5 pillars of the Well-Architected Framework. This guidance highlights the key considerations and high-level principles for an IoT workload, design considerations to help you enable those principles, and tradeoffs to consider in order to meet your business goals.

Despite its overloaded acronym, I like the Well-Architected Framework as a way of making sure that you are implementing a solution in Azure the right way.

Comments closed