Press "Enter" to skip to content

Day: August 2, 2024

Stratified Sampling in R

Published 2024-08-02 by Kevin Feasel

Steven Sanderson builds a sample:

Stratified sampling is a technique used to ensure that different subgroups (strata) within a population are represented in a sample. This method is particularly useful when certain strata are underrepresented in a simple random sample. In this post, we’ll explore how to perform stratified sampling in R using both base R and the dplyr package. We’ll walk through examples and explain the code, so you can try these techniques on your own data.

Click through to see how.

Comments closed

The World is CASE Expressions

Published 2024-08-02 by Kevin Feasel

Chad Baldwin re-enacts the astronaut meme:

Well…not really, but a handful of functions in T-SQL are simply just syntactic sugar for plain ol’ CASE statements and I thought it would be fun to talk about them for a bit because I remember being completely surprised when I learned this. I’ve also run into a couple weird scenarios directly because of this.

For those who don’t know what the term “syntactic sugar” means…It’s just a nerdy way to say that the language feature you’re using is simply a shortcut for another typically longer and more complicated way of writing that same code and it’s not unique to SQL.

Here’s where I push up my no-longer-existent glasses and say, “Well, actually, it’s a CASE expression rather than a statement because it always returns a value–or a dreaded NULL non-value–of some explicit data type.” But Chad is absolutely right about several T-SQL functions and operators being adaptations of the CASE expression syntax under the covers and I’m just being annoyingly pedantic for the fun of it.

1 Comment

Finding Uses of xp_cmdshell

Published 2024-08-02 by Kevin Feasel

Steve Jones goes looking:

I saw a post recently where someone was concerned about where xp_cmdshell was in use inside their system. They felt it was a security risk, and decided to get rid of it. I don’t agree with that, and I think xp_cmdshell can be safely used, by restricting who can run it.

That being said, I was happy to help. I saw someone say search in sys.modules, but that’s not enough. This post looks at what I thought was a better solution.

Read on for Steve’s solution. There are also other places where you might find these calls: SSISCatalog if you use Integration Services, specific company metadata tables if you use those to build T-SQL statements, etc. But this does give you a good start.

Comments closed

Execution Plan Analysis in Postgres with StatsViaExplainAnalyze

Published 2024-08-02 by Kevin Feasel

Deepak Mahto shares a tool with us:

Tuning SQL is an integral part of most developer’s or DBA’s primary responsibilities. Understanding and dissecting problematic execution plans are critical steps in the overall tuning process.

One key ingredient of SQL performance tuning, along with the runtime plan, is object statistics. Looking only at the execution plan is often insufficient for making solid recommendations; we need a fair understanding of current object statistics.As the saying goes,

Behind every optimized SQL query lies a deep understanding of the underlying object stats.

Click through to see examples of the StatsViaExplainAnalyze tool and how you can get a copy of it from GitHub.

Comments closed

Power BI Report Server “Couldn’t Connect to the Analysis Services Server”

Published 2024-08-02 by Kevin Feasel

Deepthi Goguri grabs a patch:

When you check the Change log for Power BI Report Server, Version: 1.20.8910.25479 (build 15.0.1115.165), Released: May 28, 2024 release notes mentioned about the security change made during this update to add the environment variable and system variable on the Power BI Report Server machine.

Read on to learn more about this and what it takes to correct the issue.

Comments closed

Losing .NET Code with SSIS 2016 Package Deployment

Published 2024-08-02 by Kevin Feasel

Andy Leonard goes on a quest:

…when one of our earliest SSIS framework customers reached out to share – in the very nicest way possible – that Fail Application on Package Failure in our SSIS framework was “not working.” My response was, “Great Scott!”

The customer is running SQL Server 2016. That’s fine for EDNA’s SSIS frameworks because we have framework versions that work with several versions of SSIS and SQL Server from 2012 forward. I maintain a collection of virtual servers built using the tools available for each version, including the operating systems and versions of Visual Studio, SSIS, and SQL Server.

Read on for the context, the story, and what Andy was able to do about it.

Comments closed

Archiving Files with SSIS

Published 2024-08-02 by Kevin Feasel

Andy Brownsword shuffles things around:

Integration workflows will typically involve handling files. As part of that we’ll need to move them around, for example moving into an archive directory.

The File System Task component can be used for a variety of operations such as creating directories or copying files. Here we’re going to look at two specific operations to help shuffle files around: Move and Rename.

Read on to see how it works and how you can turn this into an archival process for files.

Comments closed