Press "Enter" to skip to content

Author: Kevin Feasel

Expression-Based Formatting in DAX

Published 2020-05-18 by Kevin Feasel

Matt Allington points out a new look to a slightly less new feature:

When I say “new”, they are actually not new – this feature has actually been around since the start of 2019. What is “new” is the discoverability of the feature. Prior to the April 2020 release, you had to first hover your mouse button over the section (Title text in the example above). After you hovered your mouse, you would see a vertical … menu (kind of like a vertical ellipsis), then when you hovered over the vertical ellipsis, you would then see the Fx button. Click the mysterious hidden button and only then could you discover the world of expression based formatting.

Click through to learn about expression-based formatting and where it might be useful.

Comments closed

Removing and Refilling All Tables in a Database

Published 2020-05-18 by Kevin Feasel

Phil Factor has a couple T-SQL scripts for us to remove and reload a test database:

Copying a database using a BCP dataset from a DOS script or from PowerShell is fairly quick and trivial, but what if the data is already in another copy of the database on the same instance? Imagine you have a database build that is a copy of an existing database on the same instance and you want to run a ‘destructive’ test on the data, and do it over and over again.

Deleting all the data in a database should, you’d have thought, be trivial. Truncation doesn’t work because it doesn’t like to truncate tables with foreign keys, even if they are disabled. DELETE is safer but you will need to then reset any identity columns. Deleting large tables in one chunk isn’t scalable, so you have to do it in smaller chunks. I like to disable triggers while I do this as well. The only disadvantage comes if you have triggers that are supposed to be disabled because the simple code just re-enables them all.

This is, I think, one of the biggest selling points for containers where the database is built into the container image. You spin up a container based off of an image, perform your destructive testing, and destroy the container afterward. The next time you need to run these tests, spin up a new container. And if you need to change the data, modify the container. This introduces new challenges like how SQL Server on Linux has limitations which don’t exist on Windows, but for supported functionality, it’s a nice solution.

Comments closed

Speeding Up Pivot Operations in Power Query

Published 2020-05-18 by Kevin Feasel

Imke Feldmann has a few tricks for making pivot operations in Power Query faster:

Pivot operations in are a very handy feature in  Power Query but they can slow down refresh performance. So with some bittersweet pleasure I can tell that I found a trick to speed them up. The sweetness comes from the fact that the performance improvement is very significant. But the bitterness comes from the fact that I could have used this for almost 4 years now, but was too blind to realize at the time when I first worked with the code.

Read the whole thing and learn why Imke is disappointed in not knowing this four years ago.

Comments closed

Separating Reports from Datasets in Power BI

Published 2020-05-18 by Kevin Feasel

Melissa Coates has a video and an article for us:

Including documentation alongside the dataset is helpful for teammates who might need to work on this dataset at some point, or even for yourself in the future. It also means you won’t have an empty report, and you won’t need to delete the empty report once the file is published to the service.

Click through for plenty of useful information on the why and how, as well as what to watch out for.

Comments closed

Choosing a Recommender

Published 2020-05-15 by Kevin Feasel

Tori Tompkins walks us through four methods for generating models for recommendation systems:

One of the most popular approaches to recommendation problems is Collaborative Filtering (CF).

This approach is based on the assumption that users who have agreed in the past, also tend to agree in the future. For example, if Alice likes Star Wars, Lord of the Rings and Harry Potter and Bob likes Stars Wars and Lord of the Rings too. Then it is likely that Bob would also enjoy Harry Potter. The collaborative in Collaborative Filtering is in relation to looking at the way that you multiple users interact with the same data and share the same commonalities.

Read on for a comparison of these four systems as well as a handy chart to help you figure out which system might work best for you.

Comments closed

Planning for a Big Data Cluster

Published 2020-05-15 by Kevin Feasel

Chris Adkin has started a series on SQL Server Big Data Clusters:

Proposing the idea of using virtual machines as Kubernetes cluster nodes to a Kubernetes purist is likely to be met with consternation. However, the different nodes in your cluster have different resource requirements. A master node can get away with as little as 2 GB of memory and 2 logical processors, worker nodes require much more resources. A best practice is never to run applications on master nodes in production. The view of the world from a Kubernetes purist, is that Kubernetes is designed to obviate the need for virtualization. Consider that you do go down the bare metal route, its unlikely that you are going to purchase blades or servers with 2 GB of memory and 2 CPU cores. At the very least consider the use of virtual machines to host master nodes on. For organizations that have standardized on a software defined virtualized infrastructure, Kubernetes will run perfectly happy on this. Also for the rapid provisioning of environments – virtualization provides the fastest means of doing this – simply create yourself a virtual machine template and base your cluster node hosts on this.

Click through for more guidance around what you need to know before you deploy a cluster.

Comments closed

Paging in Power Query

Published 2020-05-15 by Kevin Feasel

Imke Feldmann explains how you can perform paging with Power Query:

When you use an API with a paging mechanism (like the example from this blogpost), you’ll might work with a field that contains the address for the next page. You can use this to walk through the available chunks until you reach the last element. That last element in the pagination will not contain a next-field or that field will be null.

There is a bit of nuance to this, so click through and learn how.

Comments closed

Accessing Azure from an Android Device

Published 2020-05-15 by Kevin Feasel

Kenneth Fisher had a dream:

The other day I had a random thought.

I wonder if there is a version of Azure Portal for Android.

Turns out there is!

This looks most useful for quick observations of machines and services, such as after receiving an alert. But as Kenneth points out, hooking a phone up to a monitor, keyboard, and mouse (which, at least on newer Samsung models, is definitely an option) means that you can hit that cloud shell and do most of what you need.

Comments closed