Press "Enter" to skip to content

Author: Kevin Feasel

Displaying SSRS Usage Stats Through Grafana

Published 2020-01-15 by Kevin Feasel

Alessandro Alpi takes queries to view SQL Server Reporting Services data and visualize it in Grafana:

One of the problems that often occur in our organization as well as some of our customers, is to get immediate feedback about usage statistics of reports. Usually, the request of creating reports is out of control and some of them are executed only “that time” and not anymore. In the worst-case scenario, many of them aren’t executed at all and some of them could become even overlapped or duplicated.

Therefore, it is important to know the usage statistics, user by user and report by report, to make the reader aware of them, let him interpreting the values of the same query in multiple ways and graphical layouts. While this is not possible with a tabular format (unless you export the values using any external tools such as Excel) it is simpler when it comes to a dashboard.

And that’s where Grafana excels.

Comments closed

Azure SQL Database Edge in Public Preview

Published 2020-01-15 by Kevin Feasel

Amit Banerjee announces the public preview for Azure SQL Database Edge:

Azure SQL Database Edge is available in public preview. Azure SQL Database Edge runs on ARM and Intel architecture and brings the most secure Microsoft SQL engine to the edge. By running the same Microsoft SQL database engine both on-premises and in the cloud, you now only need to develop your applications once and deploy anywhere across the edge, your datacenter, and Azure.

With the availability of Azure SQL Database Edge in public preview, we’re inviting customers, partners, and ISVs to join the early adopter program to experience the power of SQL and AI on the edge.

I’m interested in checking out more of these time series capabilities.

Comments closed

Using Trellis Charts to Display Small Multiples Over Time

Published 2020-01-14 by Kevin Feasel

Mike Cisneros shows us the evolution of three-point shooting in the NBA using a trellis chart:

 This small multiple chart shows two variables for each team in the league for each of the last 30 seasons: on the x-axis, the number of 3-pointers attempted per game; on the y-axis, the percent of attempted 3-point shots that were successful. Each point is a single team in a single season. The individual panels step you forward in time as the data changes and evolves. They help you see how the pack of all NBA teams is inexorably moving towards more and more 3-point attempts per game (the data points shift rightwards as you progress through the frames). We can also see that there are no longer any teams with sub-30% shooting percentages on those attempts (illustrated by tighter clustering upwards as you move forward in time).

This is a good way of showing movement over time in a static medium, like a printed page. If you’re giving a presentation, this would probably be a bubble chart with a play axis.

Comments closed

Schema Evolution in Kafka

Published 2020-01-14 by Kevin Feasel

The Hadoop in Real World group takes us through schema changes in Apache Kafka:

Meetup.com went live with this new way of distributing RSVPs – that is through Kafka. Both the producer and consumer agrees on the Schema and everything is great. It is silly to think that the schema would stay like that forever. Let’s say meetup.com didn’t feel the value in providing member_id field and removes it. What do you think will happen – will it affect consumers? 

member_id field doesn’t have a default value and it is considered a required column so this change will affect the consumers. When a producer removes a required field, the consumer will see an error something like below –

Caused by: org.apache.kafka.common.errors.SerializationException: Error deserializing Avro message for id 63
Caused by: org.apache.avro.AvroTypeException: found com.hirw.kafkaschemaregistry.producer.Rsvp,
expecting com.hirw.kafkaschemaregistry.producer.Rsvp, missing required field member_id

This is an interesting review of the schema registry in Kafka and what the different modes allow for.

Comments closed

Solving Sudoku with R

Published 2020-01-14 by Kevin Feasel

Tomaz Kastrun builds a validation function for Sudoku:

Function validater will validate for the sudoku board a particular solution at a particular position:

validater(sudoku, 1, c(1,4))

In matrix, at position x=1, y=4, where there is 0, it will test if number 1 is valid or not. If the number is valid, it returns TRUE (number) to outer function for finding complete solution.

This function iterates through all the possible 0-positions and iterates through solutions that are still available based on the rules:

Click through for that validation function.

Comments closed

Loading Event Hubs from Cosmos DB

Published 2020-01-14 by Kevin Feasel

Annie Xu shows us how we can use Azure Functions to take data from Cosmos DB and populate Event Hubs:

One way to load data from Cosmos DB to Event hub is to use Azure Function. But although there is many coding samples out there to create such Azure Function. If you are like me do not have much application development experience, reading those code samples is bit channenging. Luckly, Azure Portal made is so easy.

Annie has a step-by-step walkthrough which makes it easy.

Comments closed

Monitoring Power BI On-Premises Gateways

Published 2020-01-14 by Kevin Feasel

Jeff Pries shows us how we can monitor Power BI Enterprise Gateways:

To tie all of these steps together, I put together a brief C# console application which is capable of using stored credentials or prompting for credentials interactively, then performs the 4 steps listed above. Finally, the application receives the JSON response and de-serializes it and outputs it in a friendly way to the screen (or optionally inserts it into a SQL database table.

For the basics on how I perform the authentication and API query in the application, see my blog post here.

There are a few more steps than I would have anticipated.

Comments closed

Failed to Open Loopback Connection

Published 2020-01-14 by Kevin Feasel

Steve Stedman diagnoses an error:

Recently while working on a SQL Server for a client I came across the following error when I attempted to view the SQL Server logs in SSMS.

From there I decided I needed to track this down, so I attempted to run the sp_readerrorlog stored procedure, which also failed, but it provided more informaiton.

The full error message was:

Msg 22004, Level 16, State 1, Line 0

Failed to open loopback connection. Please see event log for more information.

Msg 22004, Level 16, State 1, Line 0

Error log location not found.

There are several potential solutions, and the one Steve landed on definitely was not my first thought.

Comments closed

Toggling Query Results with Azure Data Studio

Published 2020-01-14 by Kevin Feasel

Taiob Ali shows us how you can hide the results and messages panes in Azure Data Studio:

I know CTRL+R works for the same when using SQL Server Management Studio (SSMS). That hotkey did not work in Azure Data Studio (ADS). Knowing how brilliant technologists Derik is, I realized that a standard google search will not work.

I know ADS is an open-source project. Someone might have requested this feature already.

That functionality is in there, as Taiob shows.

Comments closed

Don’t Miss These Settings in Azure SQL DB

Published 2020-01-14 by Kevin Feasel

Arun Sirpal takes us through a few things administrators tend to miss in Azure SQL Database:

2. Allow Azure Services and resources to access this server setting set to on/off?

I always set this to off. I do not like it ON.

Why? Because I like to control things via vnets (maybe IPs if really needed – it depends on your solution). Nowadays you can use private endpoint connections which allow connections from within a vnet to a private IP.  Sure, you may want to use IP addresses, if you do then I suggest database level firewall rules over server level, especially if you use failover groups.

There are several good ones here.

Comments closed