Press "Enter" to skip to content

Author: Kevin Feasel

Replacing Error Values on All Columns in Power BI

Published 2022-07-22 by Kevin Feasel

Kristyna Hughes needs to clear out some errors:

End users that are not trained in data governance but are actively involved in maintaining a data set can easily make data entry mistakes or create inconsistent data types within columns. For example, you may have a column in the dataset called “Sales” and instead of 0, someone may type “None” or “NA”. When this gets loaded into Power BI, Power BI will not know how to convert the text value “None” to a number, and it will throw an error on the refresh of the report.

One way to mitigate the impact of user-entered data is to replace errors with null values. This is not ideal since it doesn’t fix the data entry issues, but it does enable reports to still be refreshed and used while the data issues are addressed. In Power Query, you can manually replace the errors with null by going to the “Transform” tab then selecting the drop down for “Replace Values” and choosing “Replace Errors”.

Click through for a script which does this for all columns in Power Query.

Comments closed

Pinned SSMS Tabs in a Separate Row

Published 2022-07-22 by Kevin Feasel

Kenneth Fisher keeps ’em separated:

I use this all of the time. I have a solution in SSMS to store all of the queries I use on a regular basis. I open one of the queries and then pin it to the top to keep it sperate from the rest of my sometimes literally dozens of queries. Things like my query to figure out where the backups for this instance are, and my diagnostic queries are almost always up there at the top left. So I open the file, pin the file. A few days later when I end up re-opening SSMS I open the file, and I pin the file. Not hard, but rather repetitive.

The other day though I found a fantastic option.

Click through to learn what Kenneth found.

Comments closed

Deleting from Multiple Tables by Key

Published 2022-07-22 by Kevin Feasel

Guy Glantser needs to delete some data:

Sometimes you need to delete data from multiple tables in a database. For example, you might have a multi-tenant database, and you need to delete all the data that belong to some tenant.

The problem is that there are many tables in the database that contain data, which you need to delete. If you have a column like “TenantId” in all tables, then your life is easier, because you have a simple predicate to apply to your DELETE statements against all tables. But even then, if there are foreign keys between tables to enforce referential integrity, then things get more complicated.

Read through for Guy’s answer, which definitely works and can be the quickest solution. If you can’t drop foreign key constraints (even temporarily), I have a post from a while back on tracing foreign keys to “levels.” The post only covers finding the ordering but could be extended to delete data one level at a time.

Comments closed

Sorting in KQL

Published 2022-07-21 by Kevin Feasel

Robert Cain continues a series on KQL:

Like most query languages, the Kusto Query Language as the ability to sort the output. It works almost, but not quite, like you expect. So let’s take a look at the KQL sort operator.

Read on to get the general idea but also some of the nuance behind this operator.

Comments closed

The Benefit of Tick Marks on a Visual

Published 2022-07-21 by Kevin Feasel

Alex Velez lays out the case for tick marks:

Lately, I’ve noticed that more and more graphs don’t include gridlines. If it’s unclear, I believe this to be a positive trend. I, myself, rarely use gridlines, and often remove them when I find them in a graph I’m reviewing. But I don’t stop there. 

More often than not, if a chart has gridlines, it will be lacking tick marks along the axis, and possibly an axis line as well. 

Read on to see why.

Comments closed

Storing sp_BlitzIndex Details between Reboots

Published 2022-07-21 by Kevin Feasel

Tracy Boggiano continues a series on starting a new job as a DBA:

I mentioned in my New Database Job – The 90 Day Plan blog how I have a trick for storing index usage stats up until close to the next reboot of the SQL Server.  You really can do this for any DMV related query that you gets reset at the reboot of a SQL Server instance.  With this I am able to have data of the most the current index usage stats up to the midnight before a reboot of my SQL instances to analyze.

This is a good one to store, as is the output of sp_WhoIsActive.

Comments closed

Azure SQL Database and the Well-Architected Framework

Published 2022-07-21 by Kevin Feasel

Jason Bouska has a big announcement:

Microsoft Azure SQL Database is a fully managed cloud database (PaaS) that handles many database management tasks without user intervention. Tasks such as patching, upgrading, taking backups, and monitoring can be configured to the specific needs of the workload and are performed in the background. Azure SQL Database runs the latest stable version of SQL Server and patched OS with 99.99% availability. The intelligent automated functions built into the database free up the user to focus on other important tasks.

Today I am introducing the Azure Well-Architected Service Guide for Azure SQL Database. Like other service guides, this guide for Azure SQL Database contains design considerations, checklists, and detailed configuration recommendations that can assist cloud architects in deploying optimal Azure SQL workloads in line with the guiding tenets of the Well-Architected Framework: security, reliability, cost management, performance efficiency, and operational excellence.

I’ve found that the Well-Architected Framework (whose overloaded acronym is still annoying) works best once you’re far enough along that you have a good idea of workload characteristics, meaning it’s not for the pre-planning state. Also, a full review might take hours or days and require several people to complete, not just a DBA.

Comments closed

Errors Sending Subscription E-mails in SSRS

Published 2022-07-21 by Kevin Feasel

Garry Bargsley sorts out an e-mail problem:

Recently, I was tasked with creating an email subscription to a new SSRS report in an environment that I was not familiar with.  I have created my fair share of subscriptions in my day, and this one was very straightforward.

I found the report, clicked on Manage, and went to the Subscription page.  Clicked on New Subscription and filled in all the information, easy peasy.

The subscription is ready to go when the schedule kicks in the next day, or so I thought.

Turns out that wasn’t quite the case. Read on to see what happened and how Garry fixed the problem.

Comments closed

Verifying a Database Backup Has Occurred

Published 2022-07-21 by Kevin Feasel

Lee Markum reviews backup logs:

As data professionals responsible for SQL Server, it is drilled into our heads that we need to take backups. But, how do we know we actually have backups available to us when we need them? How can we verify that a backup has been taken? Some types of auditing that an employer has to undergo might require proof of backups. How will you provide it?

Read on for three options. Note that this post is about finding the existence of backups, not checking to see if the backups are any good.

Comments closed