Press "Enter" to skip to content

Month: October 2020

Dynamic Format Strings when using Calculation Groups

Published 2020-10-21 by Kevin Feasel

Alberto Ferrari shows off how you can dynamically generate format strings when using calculation groups in Power BI:

Each product in Contoso weighs a certain weight. The weight is stored in two columns: the unit of measure and the actual weight, expressed in that unit of measure. Specifically, Contoso uses three units of measure: ounces, pounds, and grams.

Because the units of measure are different, you cannot aggregate the weight over different products. If you author a simple measure that computes the ordered weight of products by using a simple SUMX, the result is wrong:

Click through to see how you can work through this problem.

Comments closed

SQL Server Management Studio 18.7

Published 2020-10-21 by Kevin Feasel

Drew Skwiers-Koballa announces SQL Server Management Studio version 18.7 is now generally available:

Policy-based management is accessed in SQL Server Management Studio under “Management” in the object explorer as “Policy Management”. Getting started with policy-based management can be accelerated by importing the sample policies available for SQL Server. In September, these policies were added to the open source collection of SQL Server samples to facilitate their use and improvement. You can access these sample policies on the GitHub repository and your contributions to these best practices are welcome. For more information on Policy-Based Management, please check out the documentation.

I think Policy-Based Management is one of the biggest missed opportunities in SQL Server. They came out with a good start in 2008 but the product stagnated after that and it remains under-utilized as a result. Perhaps open-sourcing the policies will help, as the key problem with PBM was how limited it was.

Comments closed

Alternative Ways of Displaying Heatmap Data

Published 2020-10-20 by Kevin Feasel

Cole Nussbaumer Knaflic gives us a couple alternatives to displaying data in a heatmap:

I often describe heatmaps as a good means for getting an initial view of your data. They can help you start to explore and understand where there might be something interesting to highlight or dig into. But once you’ve identified the noteworthy aspects of your data, should you use heatmaps to communicate them?

As often is the case, it depends.

If you are communicating to an audience who likes to see data in tables—applying heatmap formatting can provide a visual sense of the numbers without fully changing the approach (or having it feel like you’ve taken detail away). If you know your stakeholders will want to look up specific numbers (particularly in the case where different stakeholders will care about different numbers) and then understand them in the context of the broader landscape, a heatmap may also work in this scenario.

Click through for some ideas.

Comments closed

The Spark Starter Guide

Published 2020-10-20 by Kevin Feasel

Landon Robinson has some good news for us:

If you visit hadoopsters.com/spark or thesparkguide.com, you’ll see something new and exciting from us. It’s official: we’ve written and are publishing a comprehensive guide to Apache Spark.

This guide will be completely online and completely free. A book’s worth of content, containing exercises in Python and Scala to teach you Spark, at your fingertips. Again, free.

Landon has posted chapter 1, section 1 already:

This section introduces the concept of data pipelines – how data is processed from one form into another. It’s also the generic term used to describe how data moves from one location or form, and is consumed, altered, transformed, and delivered to another location or form.

You’ll be introduced to Spark functions like join, filter, and aggregate to process data in a variety of forms. You’ll learn it all through interactive Spark exercises in Scala and Python.

This is very early in the process but I’m excited.

Comments closed

Azure Site-to-Site VPN Blocking Certain Traffic

Published 2020-10-20 by Kevin Feasel

Denny Cherry diagnoses a network configuration issue:

I ran across an interesting a couple of weeks ago when working with a client. The client has several subsidiaries each with their own vNet. The client had a site to site VPN been the Azure vNets. All traffic was successfully crossing the Azure Site to Site VPN as expected. The sticking point was that a software licensing server running in one of the subsidiaries Azure infrastructure configurations. The software licensing software simply wasn’t working.

Click through to learn why.

Comments closed

Altering User-Defined Table Types

Published 2020-10-20 by Kevin Feasel

Michael J. Swart has a clever solution to the inability to alter user-defined table types:

Last year, Aaron Bertrand tackled the question, How To Alter User Defined Table Types. Aaron points out that “There is no ALTER TYPE, and you can’t drop and re-create a type that is in use”. Aaron’s suggestion was to create a new type and then update all procedure to use the new type.

I think I’ve got a bit of improvement based on sp_rename and sp_refreshmodule. 

This is a clever solution. Prior to it, my workflow was:

  1. Create a new user-defined table type
  2. Create new stored procedures which reference the new user-defined table type
  3. Alter and deploy code to call these new stored procedures
  4. Drop the old procedure and user-defined table type

If the changes are such that they don’t require immediate app changes to use (for example, adding a nullable column to the UDTT), this can save a lot of effort.

Comments closed

Power BI Custom Format Strings

Published 2020-10-20 by Kevin Feasel

Matt Allington is back from the future:

The ability to apply custom format strings directly inside the Power BI Desktop report view was first announced back in February 2020. At the time I was quite excited and tweeted about it only to find out shortly thereafter that it hadn’t been released at all! It seems it was a release candidate that was pulled at the last minute, but no one updated the announcement! Then late last week I was doing some work in Power BI desktop and noticed that this feature has now been released and is working (I don’t recall seeing a new announcement at all).

Below I show you how to use custom formatting strings in Power BI.

Click through to see how it works, as well as a couple gotchas.

Comments closed

Resolving Call Stack Symbols on SQL Server 2019

Published 2020-10-20 by Kevin Feasel

Paul Randal takes us through a change to SQL Server 2019:

After beating my head against the proverbial wall for an hour, I wondered if I had the wrong symbols somehow. I checked with the excellent SQLCallStackResolver tool from GitHub (authored by Arvind Shyamsundar from the Product Group) and that worked fine with the symbols I had, so it had to be something within SQL Server.

Read on to see the answer.

Comments closed

Creating Power BI External Tools in VS Code

Published 2020-10-20 by Kevin Feasel

Phil Seamark takes us through creating external tools in Power BI:

For this article, I want to share a way for you to create your own Power BI “Helper Tool” and register it as an external tool in Power BI. This article carries on from some of my recent articles on how you can use Visual Studio Code to help automate specific tasks by taking advantage of the existing Analysis Services client libraries.

In my role, I often connect to AS models (Power BI or Azure AS) and often want to perform specific tasks quickly. The helper tool I share here allows you to connect easily to an AS model and then perform helpful tasks. I’ve deliberately kept the look and feel of the tool to be ‘old school’ like me. 

Click through for the step-by-step instructions.

Comments closed