Press "Enter" to skip to content

Month: January 2023

Monitoring TDE in Action

Published 2023-01-30 by Kevin Feasel

Matthew McGiffen pulls out the magnifying glass with a built-in light:

The encryption of your existing data occurs as a background process referred to as the encryption scan, but it will consume resources while it runs, so if you are implementing TDE against a system with large databases where performance is critical then you will want to either run it in a period of quiet (or down time), or you will want to monitor to check that encryption isn’t impacting your system too much. Experience suggests that it shouldn’t be a problem unless your server is already under strain.

There are a few things to look out for if you are monitoring during the encryption scan:

Click through for a list of items you might want to keep in mind, as well as some important tips about suspending or stopping the encryption process.

Comments closed

Passing Power BI Report Values to Dynamic M Parameters

Published 2023-01-30 by Kevin Feasel

Chris Webb explains how to let end users work with Dynamic M parameters:

Dynamic M parameters are incredibly useful in Power BI when using DirectQuery mode because they allow you to pass values from your report direct to your data source query, bypassing Power BI’s own SQL (or whatever query language your source uses) generation layer. However it isn’t obvious how to pass any value you want: dynamic M parameters must be bound to slicers or filters, and they must be bound to tables in your dataset, which means that at first glance it seems like you can only pass values that are already present somewhere in your dataset (for example in a dimension table) to a dynamic M parameter. This isn’t true though: there is a way to allow passing of any value your users enter to a dynamic M parameter and in this post I’ll show you how.

Click through for an explanation of why you might want to do this, as well as a demonstration of how you can.

Comments closed

Troubleshooting Always Encrypted Certificate Errors

Published 2023-01-30 by Kevin Feasel

Chad Callihan swears this is the right key:

The benefit of SQL Server Always Encrypted is to encrypt sensitive data in specified columns to prevent it from being seen by unauthorized users. It is a great feature, but there are some steps to consider besides the SQL setup side. What if you are one of the lucky (or unlucky) users who should have access to query that data unencrypted but get blocked by an error? Or, what if you’re setting up Always Encrypted and your application encounters issues decrypting data?

Let’s step through an example of one error you may come across that prevents authorized access.

Read on for the error and its root cause.

Comments closed

Comparative Data Type Efficiency in Power BI

Published 2023-01-30 by Kevin Feasel

Soheil Bakhshi doesn’t want everything in Power BI to be decimals and strings:

In my consulting experience working with customers using Power BI, many challenges that Power BI developers face are due to negligence to data types. Here are some common challenges that are the direct or indirect results of inappropriate data types and data type conversion:

  • Getting incorrect results while all calculations in your data model are correct.
  • Poor performing data model.
  • Bloated model size.
  • Difficulties in configuring user-defined aggregations (agg awareness).
  • Difficulties in setting up incremental data refresh.
  • Getting blank visuals after the first data refresh in Power BI service.

Read on for a dive into these problems.

Comments closed

Improving the Shiny UI Experience

Published 2023-01-27 by Kevin Feasel

Tim Brock talks responsiveness:

Confusingly (and rather unhelpfully) when it comes to web applications there are two different topics that may be referred to by the terms “responsive” or “responsiveness”. If you stick “responsive UI” into your favourite search engine the top results will concern “responsive design” – the practice of making websites and applications work across devices, regardless of device and browser dimensions. That’s an interesting and important topic when it comes to designing data-science applications but it’s not what we’re covering here.

What we’re covering here is responsiveness that you might stick “un” in front of if things got really bad. It’s about making your user interface feel like it responds instantaneously to a user’s interaction. We’ll go from covering clicking a button and making sure the user sees some kind of simple acknowledgement the button has been clicked to clicking a button (or dragging a slider or…) and immediately seeing the results of complex computations.

Read on to learn a few things you can do to make those apps a little more user-friendly.

Comments closed

Tagging on Pure Storage Volumes

Published 2023-01-27 by Kevin Feasel

Anthony Nocentino tags all the things:

A tag is a key/value pair that can be attached to an object in FlashArray, like a volume or a snapshot. Using tags enables you to attach additional metadata to objects for classification, sorting, and searching. For example, you can assign a tag to a collection of volumes and then come along later and retrieve a listing of volumes that match a particular key or value. You can use tags to add application context to resources inside FlashArray. Specifically, in the examples in this blog post, I want to tag volumes with the names SQL Server Instances.

Click through to see what you’d need to do, assuming you have an appropriate device running Purity 6.0 or later.

Comments closed

Limiting Data Factory Users to Trigger Pipelines

Published 2023-01-27 by Kevin Feasel

Koen Verbeeck doesn’t want people running amok:

Typically you have a bunch of pipelines that are started by one or more triggers. Sometimes, a pipeline needs to be manually triggered. For example, when the finance department is closing the fiscal year, they probably want to run the ETL pipeline a couple of times on-demand, to make sure their latest changes are reflected in the reports. Since you don’t want them to contact you every time to start a pipeline, it might be an idea to give them permission to start the pipeline themselves.

This can obviously be done by tools such as Azure Logic Apps or a Power App, but in my case the users also wanted to view the progress of the pipeline (did something crash? Why is it taking so long? etc.) and developing a Power App with all those features seemed a bit cumbersome to me. Instead, we gave them permission on ADF itself so they can start the pipelines. There’s one problem though, there’s only one role for ADF in Azure, and it’s the contributor role. A bit too much permission, as anyone with that role can change anything in ADF. You don’t want that.

So what can you do? Click through to find out.

Comments closed

Building a Generic Percent of Grand Total in DAX

Published 2023-01-27 by Kevin Feasel

Leo Tachev isn’t down with the copy-pasta:

Suppose you need to calculate a percentage of grand total measure. Easy, you can use the Power BI “Show value as” without any DAX, right? Now suppose that you have 50 Table visuals and each of them require the same measure to be shown as a percentage of total. Although it requires far more clicks, “Show value as” is still not so bad for avoiding the DAX rabbit hole. But what about if you need this calculation in another measure, such as to implement a weighted average? Now, you can’t reference the Microsoft-generated field because it’s not implemented as a measure.

Click through for one solution.

Comments closed

External Tables and the Serverless SQL Pool

Published 2023-01-27 by Kevin Feasel

Ryan Adams continues a series on querying the serverless SQL pool in Azure Synapse Analytics:

There are two ways to read data inside Data Lake using the Synapse Serverless engine.  In this article, we’ll look at the second method which uses an external table to query a path within the lake.

Synapse is a collection of tools with four different analytical engines (Dedicated Pool, Spark Pool, Serverless Pool, Data Explorer Pool).  This gives you a lot of options for ingesting, transforming, storing, and querying your data.  Here you will use the Synapse Serverless Pool to query the data in your ADLS account.   

Read on for a demonstration.

Comments closed

Minimizing Calculated Column Usage in Power BI

Published 2023-01-27 by Kevin Feasel

Ed Hansberry wants us to slow down and think:

First off I want to make sure it is clear that I am not saying “never use calculated columns.” I am not. What I am saying is they should be avoided, and there are several reasons. I am going to focus on the model size, and thus performance. Note that the data I am going to show is a contrived set of data for demonstration purposes only to explain.

Read the whole thing, including a set of questions you might want to ask yourself before deploying that PBIX file with calculated columns in it.

Comments closed