Press "Enter" to skip to content

Month: July 2019

Power BI Conditional Formatting and Icons

Published 2019-07-23 by Kevin Feasel

Matt Allington shows how you can now use icons as the output of conditional formatting in Power BI:

Note how the icons above have both shape and colour so you can differentiate between them even if you are colour blind.  This is best practice.

You can also change the default formatting to work on the hard coded number settings that you specify. In the example below I have changed the settings to work on absolute numbers instead of percentages (note the changes in the highlighted boxes).  Also note that I have set the minimum and maximum numbers shown as 1 and 2.  To do this, simply delete the value in these boxes.  Thanks to Chris Webb for finally helping me understand how this works.

It’s easy to go overboard with this, but I’m happy to see conditional formatted icons in place; done right, you can pack a lot of information into a small space with them.

Comments closed

The SQL Notebook Experience, Featuring Powershell

Published 2019-07-23 by Kevin Feasel

Rob Sewell takes a break from book-writing and talks about using Powershell in SQL Notebooks:

Yes, it’s funny but also it carries a serious warning. Without understanding what it is doing, please don’t enable PowerShell to be run in a SQL Notebook that someone sent you in an email or you find on a GitHub. In the same way as you don’t open the word document attachment which will get a thousand million trillion pounddollars into your bank account or run code you copy from the internet on production without understanding what it does, this could be a very dangerous thing to do.

With that warning out of the way, there are loads of really useful and fantastic use cases for this. SQL Notebooks make great run-books or incident response recorders and PowerShell is an obvious tool for this. (If only we could save the PowerShell output in a SQL Notebook, this would be even better)

“It’s a bit hacky” is a generous statement, but it’s really cool that Rob figured out a way to do this. There is a Powershell kernel for Jupyter, but I’ve not had the best experience adding new kernels to Azure Data Studio (at least not F#’s kernel, which I tried).

Comments closed

Simple Query Zen

Published 2019-07-23 by Kevin Feasel

Erik Darling wants you to simplify your life queries:

See, when a query is big and complicated to you, there’s a pretty good chance you’re gonna get a big and complicated query plan, because it’s big and complicated to the optimizer, too.

This isn’t to say the optimizer is dumb or bad or ugly; it’s just that there’s only so long it’s willing to spend coming up with a plan.

Remember, cheap plan fast. Not perfect, not great, maybe good enough.

It’s a good operating philosophy: if you have a query which has gone off the rails, one of the best things you can do is try to turn the query into several small steps. It’s possible to reduce complexity that way…though you may also gain complexity in the process if you do it wrong.

Comments closed

Transactional Replication Tips

Published 2019-07-23 by Kevin Feasel

Nate Johnson has a few things which might make SQL Server transactional replication easier for you:

For what seems like years, I’ve bemoaned the fact that SQL Transactional Replication doesn’t come with a “Just Trust Me” option. I’ll explain more about what I mean in a moment. The other thing I’ve complained about is that there’s no “Pause” button — which not entirely accurate, since obviously you could just stop the distribution and subscription agents. But specifically what I mean is, it’s not easy to ‘put it on hold so you can make some schema changes to one of the tables that’s being replicated’, and then easily “Resume” it after you’re done with said changes.

Well, I’m happy to say that now I have both of these tools/methodologies in my arsenal!

Read on for those tips and a couple more.

Comments closed

Keeping S3 and Blob Storage in Sync

Published 2019-07-23 by Kevin Feasel

Sheldon Hull shares with us a technique to keep an S3 bucket in sync with an Azure Blob Storage blob:

Moving data between two cloud providers can be painful, and require more provider scripting if doing api calls. For this, you can benefit from a tool that abstracts the calls into a seamless synchronization tool.

I’ve used RClone before when needing to deduplicate several terabytes of data in my own Google Drive, so I figured I’d see if it could help me sync up 25GB of json files from Azure to S3.

You’ll have to do a few of the steps on your own, but this looks like a good way of parking data in two clouds.

Comments closed

Comparing Classification Model Quality

Published 2019-07-22 by Kevin Feasel

Stephanie Glen looks at ways to compare model evaluation for classification models:

In part 1, I compared a few model evaluation techniques that fall under the umbrella of ‘general statistical tools and tests’. Here in Part 2 I compare three of the more popular model evaluation techniques for classification and clustering: confusion matrix, gain and lift chart, and ROC curve. The main difference between the three techniques is that each focuses on a different type of result:

– Confusion matrix: false positives, false negatives, true positives and true negatives.
– Gain and lift: focus is on true positives.
– ROC curve: focus on true positives vs. false positives.

These are good tools for evaluation and Stephanie does a good job explaining each.

Comments closed

Databricks Runtime 5.5

Published 2019-07-22 by Kevin Feasel

Bilal Aslam and Yifan Cao announce Databricks Runtime 5.5:

Secrets API in R notebooks
The Databricks Secrets API [Azure|AWS] lets you inject secrets into notebooks without hardcoding them. As of Databricks Runtime 5.5, this API is available in R notebooks in addition to existing support for Python and Scala notebooks. You can use the dbutils.secrets.get function to obtain secrets. Secrets are redacted before printing to a notebook cell.

There are some good updates in this release. Read on for the full list.

Comments closed

Multi-Server Patching with dbatools

Published 2019-07-22 by Kevin Feasel

Eric Cobb shows how you can use dbatools to upgrade multiple SQL Server instances:

Patching SQL Server can sometimes be a time consuming process, especially when you have multiple servers that need to be patched. Remoting in to each box to run through the update wizard is tedious, and if you have multiple patches to apply you’re going to be spending a considerable chunk of time on it.

Thankfully, the dbatool Powershell Module makes this process much easier! In fact, once you get it figured out, using Update-DbaInstance to patch your SQL Servers becomes very simple. But, getting it figured out can be a little confusing, so I’ve outlined the steps below that I use. The biggest hangups I had were setting up a central patching location and using a credential to access it.

Click through to see an example of this in action.

Comments closed

Diagnosing Analysis Services with SSASDiag

Published 2019-07-22 by Kevin Feasel

Chris Webb discusses SSASDiag:

There are a lot of great community-developed tools out there for Analysis Services developers to use (BI Developer Extensions, DAX Studio, Tabular Editor, Analysis Services Query Analyzer to name a few) and they have saved me vast amounts of time and effort over the years. When I joined Microsoft last month I came across one which I had never seen before but which is nevertheless quite mature and feature-rich: the SSAS Diagnostics Tool or SSASDiag for short. It’s available on GitHub here:
https://github.com/ssasdiag/SSASDiag

Read on for Chris’s initial thoughts and check out the tool.

Comments closed

SQL Server 2017 and Visual Studio 2017/2019 Installation Error

Published 2019-07-22 by Kevin Feasel

Hamish Watson walks us through an error which happens when you install SQL Server 2017 and Visual Studio 2019 in the “wrong” order:

This blog post is about a situation that initially perplexed me – I was installing SQL Server 2017 onto a new DEMO machine – running Windows Server 2019. This install is one I have done over 50 times, if not more.

Halfway through I got an interesting error that (1) I’ve never seen before and (2) did not expect post SQL Server 2014.

MSI Error: 1706 An installation package for the product Microsoft SQL Server 2012 Native Client cannot be found. Try the installation again using a valid copy of the installation package ‘sqlncli.msi’.

Read on for the solution. And note that it also happens with Visual Studio 2017.

Comments closed