Press "Enter" to skip to content

Curated SQL Posts

Using SHAP to Gauge Geographic Effects in R or Python

Published 2023-06-08 by Kevin Feasel

Michael Mayer runs an analysis:

This is the next article in our series “Lost in Translation between R and Python”. The aim of this series is to provide high-quality R and Python code to achieve some non-trivial tasks. If you are to learn R, check out the R tab below. Similarly, if you are to learn Python, the Python tab will be your friend.

This post is heavily based on the new {shapviz} vignette.

I appreciate the effort to include both R and Python code in this analysis, and recommend you peruse both sets of code listings.

Comments closed

Creating a Hierarchy for Power BI Field Parameters

Published 2023-06-08 by Kevin Feasel

Gilbert Quevauvilliers needs a simpler method for navigation:

I was working with a customer where they had a lot of measures which they wanted to use with the awesome Field parameters for measures.

The challenge was that there was a LOT of measures, and I wanted to see if there was a way to create a hierarchy in my field parameters so that it would be easier to find the measure.

Click through to see how you can group field parameters.

Comments closed

Speeding up a Power BI Report via Constant Line

Published 2023-06-08 by Kevin Feasel

Nikola Ilic tries something different:

Essentially, the idea here is, since these four lines are not changing based on the numbers in the visual itself (they have constant value based on the slicer selection), to leverage the Constant line feature from the Analytics panel. Since no Constant line is available with Line and clustered column chart visual, let’s duplicate our visual and change its type to a regular Clustered column chart.

This is a good reminder that the best outcome isn’t always the most straightforward one.

Comments closed

Updating Database Mail Settings via SP

Published 2023-06-08 by Kevin Feasel

Chad Callihan doesn’t have time for the UI:

If you need to make changes to multiple servers, you may want to avoid the GUI approach and all of the clicks that come with it. In that case, msdb contains a stored procedure called sysmail_update_account_sp that might be a more efficient approach. Let’s take a quick look at sysmail_update_account_sp and what it can do for you.

Read on to see how the procedure works and what you can do.

Comments closed

Killing Multiple YARN Applications at Once

Published 2023-06-07 by Kevin Feasel

The Big Data in Real World team doesn’t have time to mess around:

If you work with Apache Hadoop, you may find yourself needing to kill multiple YARN applications at once. While you can kill them one by one using the yarn application -kill command, this can be a tedious and time-consuming process. Fortunately, there is a faster way to kill multiple YARN applications at once using the yarn application command in combination with awk.

Click through to see how. I will say, though, remembering some of these sed+grep+awk solutions I’ve written in the past makes me happy that Powershell is object-based…

Comments closed

Deploying to Multiple Power BI Dataset Environments

Published 2023-06-07 by Kevin Feasel

Richard Swinbank configures some deployments:

In earlier posts in this series, I talked about to developing and deploying standalone Power BI datasets and automating report deployment into different environments. I’ll bring together those approaches in this post, to enable deployment of shared datasets into multiple environments. This has consequences for automated report deployment, and I’ll take a look at that too.

Read the whole thing.

Comments closed

Configuring Always Encrypted

Published 2023-06-07 by Kevin Feasel

Matthew McGiffen sets up Always Encrypted on a SQL Server instance:

In this post we’re going to go through the steps to set up Always Encrypted and create an encrypted column. As with my last post we’re looking at the flavour of Always Encrypted without enclaves, we’ll look at working with enclaves in detail later on.

It is a straightforward process to set up everything required for Always Encrypted. In fact, there is a wizard provided in SQL Server Management Studio (SSMS) that will do it all for you. In these examples, however, we will focus on performing the individual steps manually as that gives you a better view of what is going on. For all the objects involved we’ll look in detail at what is created so that you have a good level of understanding.

Click through for the instructions.

Comments closed

Monitoring Datasets with Log Analytics for Power BI

Published 2023-06-07 by Kevin Feasel

Chris Webb has had a busy month:

Maybe the fourth- or fifth-most exciting Power BI-related announcement last month (admittedly it was an exciting month) was that Log Analytics for Power BI datasets is now GA and you can now link multiple Power BI workspaces to a single Log Analytics workspace. This, for me, means that enabling Log Analytics has gone from being useful to essential for anyone interested in monitoring Analysis Services engine activity in an enterprise Power BI/Fabric deployment. It also works with Direct Lake datasets too!

Read on for a few KQL queries which allow you to get pertinent information from your Log Analytics workspace.

Comments closed

Using tsnnames.ora with SSIS and Microsoft Connector for Oracle

Published 2023-06-07 by Kevin Feasel

Meagan Longoria deals with the unpleasantness:

Although you can now specify an Oracle database using the EzConnect format, it’s still very common to use a tnsnames.ora file. This file specifies a connection name and connection details for an Oracle database. It’s helpful in that you can provide a “friendly name” to the database specified in the file and then reference the file from multiple client tools.

Read on to learn how you can use it.

Comments closed