Press "Enter" to skip to content

Curated SQL Posts

Showing Images in Power BI

Teo Lachev hits on an annoyance I have with Power BI:

The Power BI image-rendering visualizations, such as Table or Card, expect image links to public servers hosting the images with anonymous permissions. This has obvious shortcomings. Can we load images from a database or Power BI data table? You bet, as Jason Thomas has demonstrated a long time ago. Here are the steps I followed to show the images from the Production.ProductPhoto table in the AdventureWorks2012 (or later) database. If you want to embed a few images into a Power BI data table (instead of an external database), you can convert them manually to Base64 using any of the online image converters, such as https://codebeautify.org/image-to-base64-converter and embed the resulting string into a Power BI data table (the Enter Data feature). Gerhard Brueckl takes this one step further by showing you how to automate the base64 conversion with many images.

The experience for displaying images isn’t the worst—I don’t mind needing base64 encoded strings for images—but there’s a strict limit on how large your images can be, and that size is puny. I have one dashboard where I show a CPU chart from Grafana and I have to leave it at a very small resolution or else I don’t get to see the whole chart—it cuts off in the middle.

Comments closed

Bookmarks or Page Navigation for Power BI

Prathy Kamasani embraces the power of “and”:

Power BI Bookmarks, the secret behind many sleek reports, It revolutionised the entire PBI Report design approach. Initially, Bookmarks were portrayed more as some saved views, which can be used for storytelling than for navigation. However, we certainly saw more report designers using Bookmarks for navigation than for storytelling. In March 2020, we have a new functionality called Page navigation,  which brings us to the dilemma of which one to use and when?

Read on for the comparison.

Comments closed

Monitoring Azure Synapse Analytics SQL Pools with Power BI

Brett Powell has a pair of Power BI templates for monitoring Azure Synapse Analytics:

Upon clicking ‘Load’ you’ll either need to provide your credentials for this source (if you don’t have this data source saved from previous use) or the queries will execute and the following report pages will be available:

– Executions
– Waits
– Sessions
– Waits Detail
– Execution Detail
– Memory
– ExecutionDrillThrough (hidden)

Click through to see what the templates look like and how to obtain them.

Comments closed

foldLeft and foldRight in Scala

Sarfaraz Hussain explains the difference between foldLeft and foldRight in Scala:

The fold method is a Higher Order Function in Scala and it has two variant namely,
i. foldLeft
ii. foldRight
In this blog, we will look into them in detail and try to understand how they work.

Before moving ahead, I want to clarify that the fold method is just a wrapper to foldLeft, i.e. the fold method internally invokes the foldLeft method. So, now let’s get started.

Folding is an extremely powerful technique for getting rid of loops in code. Being comfortable with folding is (in my eyes) one of the signs which indicate that you’ve reached a mid-level understanding of functional programming.

Comments closed

Don’t Install Hadoop on Windows

Hadi speaks truth:

A few days ago, I published the installation guides for Hadoop, Hive, and Pig on Windows 10. And yesterday, I finished installing and configuring the ecosystem. The only consequence I have is that “Think 1000 times before installing Hadoop and related technologies on Windows!”.

The biggest problem is that Microsoft got flaky about this. Back in 2012-2013, they backed running Hadoop on Windows as part of getting HDInsight up and running. I even remember the HDInsight emulator which could run on a local desktop. By 2014 or so, they shifted directions and decided it wasn’t worth the effort. Because Apache Spark (which does have pretty decent Windows support, at least for development) really wants Hive, you can fake it with winutils.

Comments closed

The Pains of Database Restoration

Stuart Moore covers some of the pains of database restoration in two posts. First, why dbatools’ Restore-DbaDatabase is a complicated as it is:

At first glance Restore-DbaDatabase looks like a slow lumberig complex beast. In reality it’s not that bad.

It’s the result of design decisions I took in wanting a solid versatile command that could cope with everything that people would want from it.

In this post, we’ll go through the main decisions/points of contention one by one

Stuart then covers the limitations of Restore-DbaDatabase:

Like all tools, Restore-DbaDatabase isn’t able to do everything that everyone wants it to. Certainly, at the moment I’d like it to write it’s own blog posts and fetch me a cold beer, but that doesn’t happen

A lot of the below isn’t complaining about people asking for features. If we can do it, we will, and we’re keen to make this work for as many people in as many situations as possible

But quite a few requests over the years have been non starters for a number of reasons.

Read them both; they’re part of Stuart’s 31 Days of Backup and Restore with dbatools series.

Comments closed

Change Tracking and Internal Tables

Tim Weigel continues a series on change tracking:

In my last post, I showed you how to configure change tracking at the table level and how to get configuration information about change tracking from the database engine. We looked at sys.change_tracking_databases and sys.change_tracking_tables, and looked at some sample scripts that present the information in a more readable format.

Before moving on to working with change tracking, I’d like to show you a little bit about how SQL Server handles change tracking data under the hood. Let’s take a few minutes to talk about sys.internal_tablessys.dm_tran_commit_table, and sys.syscommittab. These aren’t objects that most DBAs interact with on a routine basis, but they’re useful for understanding how change tracking does what it does.

Click through to learn more about these internal tables.

Comments closed

Don’t Use sys.dm_hadr_cluster_members for Quorum Info

Sean Gallardy explains a limitation of sys.dm_hadr_cluster_members:

I’ve now run across a few different instances where the monitoring for quorum was done via this DMV. On the surface, it seems like nothing would be wrong with using the “number_of_quorum_votes” column to check on the members of the cluster and see their voting status. However, this isn’t quite the case… you see there are various mechanisms that influence whether or not a member (or witness) has a vote and these continue to be expanded in each version of WSFC.

Click through for a short history lesson as well as some good advice on how accurately to get this information.

Comments closed

Patching SQL Server in Docker Containers

Rob Farley takes us through updating SQL Server when it lives in a container:

Now, the thing with running SQL in containers is that the concept of downloading a patch file doesn’t work in the same way. If it were regular Linux, the commands would be very simple, such as ‘sudo yum update mssql-server’ in RHEL. But Docker doesn’t quite work the same way, as reflected by the Microsoft documentation which mentions Docker for installing but not in the Update section.

Rob then explains the process. Containers are cattle, not pets. Just make sure your data files live outside the container before you blow it away…

Comments closed

Determining Statistics Utilization

Deborah Melkin shows us how to see if a particular statistic is in use:

You know those tweets that you see once but can never find again? I remember seeing one a while ago where someone tweeted to #sqlhelp asking if the internal inserted and deleted tables had statistics or if they were like table variables, which didn’t.

This is a great question in general. But then it got me thinking – how do you prove this? I wanted to know the answer as well so I decided to look into this. And I went down the wrong sort of rabbit hole trying to figure this out. Eventually I talked to a friend about this and got pointed in the right direction…

And the answer to how you find which statistics are used is…?

Read on for the answer and several examples.

Comments closed