Press "Enter" to skip to content

Day: February 12, 2020

Building a Cache in ksqlDB

Published 2020-02-12 by Kevin Feasel

Michael Drogalis shows how to build a materialized cache to reduce the load on your Kafka Streams servers:

There are a lot of ways that you can introduce a materialized cache into your architecture. One such way is to leverage ksqlDB, an event streaming database purpose-built for stream processing applications. With native Kafka integration, ksqlDB makes it easy to replicate the pattern of scaling out many sets of distributed caches.

Let’s look at how this works in action with an example application. Imagine that you have a database storing geospatial data of pings from drivers at a ridesharing company. You have a particular piece of logic that you want to move out of the database—a frequently run query to aggregate how active a territory is. You can build a materialized cache for it using ksqlDB.

The tutorial starts you from “grab the Docker container” and takes you through the process.

Comments closed

Changes in the R foreach Package

Published 2020-02-12 by Kevin Feasel

Hong Ooi announces some changes to the foreach package in R:

This post is to announce some new and upcoming changes in the foreach package.

First, foreach can now be found on GitHub! The repository is at https://github.com/RevolutionAnalytics/foreach, replacing its old home on R-Forge. Right now the repo hosts both the foreach and iterators packages, but that may change later.

There are also some changes to the package itself, so read on for those.

Comments closed

Fun with Binding Redirects in .NET

Published 2020-02-12 by Kevin Feasel

Nick Craver has some advice if you see binding redirect problems:

You’re probably here because of an error like this:

Could not load file or assembly ‘System.<…>, Version=4.x.x.x, Culture=neutral, PublicKeyToken=<…>’ or one of its dependencies. The system cannot find the file specified.

And you likely saw a build warning like this:

warning MSB3277: Found conflicts between different versions of “System.<…>” that could not be resolved.

Whelp, you’re not alone. We’re thinking about starting a survivors group. 

Read on for Nick’s advice here. This is particularly tricky with F# and especially when you use type providers, as that can easily lead to a version mismatch in FSharp.Core. I’ve spent way too much time tracking those down.

Comments closed

SQL Server Life Hacks

Published 2020-02-12 by Kevin Feasel

Kevin Hill has a few life hacks for us:

Since I am an independent consultant here under the Dallas DBAs name, I am frequently asked to come in and quickly find out why the server is so slow. Sometimes by existing customers, sometimes out of nowhere. Many of these times I cannot make any “permanent” or “lasting” changes, so my go-to is to run sp_whoisactive as a temporary stored procedure (code in link). Works the same, nothing left behind, no corporate policies violated.

Read on for another useful tip.

Comments closed

Tips for Power BI Paginated Reports

Published 2020-02-12 by Kevin Feasel

Jonathan Jones has five tips to help you work with Power BI Paginated Reports:

Don’t use the Visual Designer, write your queries out first

As tempting as it can be to drop and drag fields and parameters into your dataset, I would not recommend it.  It makes it harder to repeatedly test, share the logic in the query, and see the results. Instead, write the script, whether it be in DAX or SQL.  Writing the queries is a faster process that gives you a lot more control over your queries.  If you don’t understand SQL or DAX to a proficient enough level, don’t panic, you could start the query with the visual designer and then convert it to the script.

When you’re running your DAX queries, don’t use the dataset query designer in the report builder, use DAX studio. DAX studio gives you the ability to efficiently query, format, and test DAX. As an experience it’s much faster and easier to show other people. If you are using SQL queries you could use SQL Server Management studio.

As a practice, when you’re trying to test the data, it’s important to get the data right in your queries before you place that data in your report.

Read on for the rest of the tips.

Comments closed

Removing Duplicates with Power Query

Published 2020-02-12 by Kevin Feasel

Matt Allington wants to remove duplicate records while retaining the last version of the record using Power Query:

Today I was helping a customer with a problem that seemed quite simple on the surface.  She had a data table containing historical customer sales orders (each customer has many orders on different dates).  The objective was to filter this table in Power Query and just load one record for each customer – the one that was the last order date.  To illustrate the problem more clearly, I have adapted the scenario using the Adventure Works database.

Click through for the demo, and also read the comments as there are a lot of interesting attempts to solve the problem there as well.

Comments closed

Solving Groups of Groups Problems with Window Functions

Published 2020-02-12 by Kevin Feasel

James McGillivray shows off some of the power of window functions:

Windowing functions are an underused feature in SQL Server, with myriad uses. The most common problems we solve are running totals, seeing group totals on the same line as individual lines from the group (allowing calculations like subtotal %). I don’t know if there are better solutions, but before Windowing Functions, I used to solve these kind of problems with self joins, or nested queries. The performance of Windowing Functions is significantly better than this approach, and that alone has made my life considerably better.

The more I learn about Windowing Functions, the more often I see use cases where they are useful.

I love talking about, and teaching people to use, Windowing functions to make their lives better, so I’m quite excited to be able to use them as the topic for today’s post.

Specifically, James looks at groups of groups problems and aggregates of aggregates problems.

Comments closed

Uploading Multiple RDL Files to SSRS

Published 2020-02-12 by Kevin Feasel

Stuart Ainsworth shows us how to push several SSRS reports at a time:

My QA folks have a problem; development has been working on migrating a bunch of reports from a legacy 3rd-party application to SQL Server Reporting Services. Development has finished their “sprint” (*cough* waterfall), and handed over 52 reports to QA, and told the to load them into their QA server. The problem? The web interface only loads 1 report at a time.

Me, being the DevOps guy that I am, thought “well, that’s just silly; there has to be a way to upload all of the reports at once, rather than clicking on some silly buttons over and over again”.

Read on to see what Stuart did next.

Comments closed