Press "Enter" to skip to content

Curated SQL Posts

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

SSMS 19.0 Released

Published 2023-01-27 by Kevin Feasel

Erin Stellato has a new version of Management Studio for us:

In addition, we received a lot of feedback about the dialog box, “Querying transaction count”, we introduced back in SSMS 18.11.   In 18.12, we added an option to cancel the check if it took more than two seconds, but user comments indicated that was not enough.  Therefore, in SSMS 19.0 you have the ability to disable to dialog box completely.  Within Tools > Options > Query Execution, simply uncheck the option “Check for open transactions before closing T-SQL query windows”.  

That alone might be worth it, especially if you work via VPN and have a bunch of SSMS tabs open.

Comments closed

TempDB Improvements in SQL Server Versions

Published 2023-01-26 by Kevin Feasel

Jared Poche looks at the recent past:

Tempdb contention has long been an issue in SQL Server, and there are many blogs on the issue already. But I wanted to add one more mainly to highlight the improvements in recent versions of SQL Server

Tempdb contention is most often discussed in as relating to the creation of temp tables (and other objects) in tempdb. If you are experiencing this you will see PAGELATCH_EX or PAGELATCH_SH waits, frequently with wait resources like 2:1:1 or 2:1:3. This indicates contention in database 2 (tempdb), page 1 (the first data file in tempdb), and one of the PFS, GAM, or SGAM pages (which are pages 1, 2, and 3 respectively). Tempdb files of sufficient size will have additional PFS, GAM, and SGAM pages at higher page numbers, but 1 and 3 are the pages most often referenced.

Read on to see what the SQL Server team has done over three of the past four versions of SQL Server to limit tempdb contention.

Comments closed

Power BI Publish to Web

Published 2023-01-26 by Kevin Feasel

Reza Rad takes us through the Publish to Web option in Power BI:

Publish to the web is an easy way of sharing public data. However, it has some disadvantages as well. In this article and video, you will learn about this feature in more detail, and you will learn this feature is, and how it is different from Power BI Embedded. It will be a very long article if you want to learn about both Power BI Embedded and Publish to the web and compare them here. So in this article, I’ll explain Publish to Web, and in another article, you’ll learn about Power BI embedded and their differences. In this article, you will learn how easy it is to share your report with the public through a web page which can be your blog post, an HTML page, or any other web page. Some frequently asked questions about this feature are answered through the content of this article. If you like to learn more about Power BI, read Power BI online book, from Rookie to Rock Star.

Read on for the video as well as a full-length article.

Comments closed