Press "Enter" to skip to content

Curated SQL Posts

Power BI Cleaner Gen2

Published 2021-06-28 by Kevin Feasel

Imke Feldmann introduces a new version of the Power BI Cleaner:

Today I’m very excited to share with you my first version of a complete rework of my Power BI Cleaner tool. It is way faster the the initial version, overcomes some bugs and limitations of the old version and doesn’t require creating additional vpax files.

On top of that, I’ve created an Excel-version, that adds some very convenient additional features: The option to analyze thin reports and to generate scripts that delete unused measures and hides unused columns automatically.

Click through for instructions on how it all works.

Comments closed

Optimization Parameters in Oracle 19c

Published 2021-06-28 by Kevin Feasel

Kellyn Pot’Vin-Gorman enters a time warp:

As I and the dedicated CSA were working to optimize the ETL load on Oracle in Azure IaaS, I noticed that there wasn’t a significant improvement with physical VM and storage changes as expected.  As I dug into the code and database design, I started to document what I’ve summarized above and realized that the database was quite frozen in time. Even though I couldn’t make changes to the code, (per the customer request) I was quickly understanding why we had such limited success and why I was failing miserably as I attempted to put recommended practices in place at the parameter level for the Oracle 19c database from what they had originally.

As I thought this through, I had an epiphany-  This database was doing everything in its power to be a 10g or earlier database so why shouldn’t I optimize it like one?

Read on to see what this entails.

Comments closed

A Summary of Time Series Algorithms

Published 2021-06-25 by Kevin Feasel

Gavita Regunath and Dan Lantos give an overview of time series algorithms:

Time series forecasting is a data science task that is critical to a variety of activities within any business organisation. Time series forecasting is a useful tool that can help to understand how historical data influences the future. This is done by looking at past data, defining the patterns, and producing short or long-term predictions.

Click through for an overview, as well as ten examples of algorithms you can use for handling time series data.

Comments closed

LAG() in SQL Server

Published 2021-06-25 by Kevin Feasel

Chad Callihan shows off one of the best window functions:

The LAG function in SQL Server allows you to work with a row of data as well as the previous row of data in a data set. When would that ever be useful? If you’re a sports fan, you’re familiar with this concept whether you realize it or not. Let’s look at an example.

LAG() is outstanding for business reports, such as if you want three-month trailing data.

Comments closed

Moving Artifacts between Folders in Synapse Studio

Published 2021-06-25 by Kevin Feasel

Wolfgang Strasser looks at a recent update:

Another small but very powerful usability extension in Azure Synapse Studio was added at the beginning of June: Move artifacts across folders in Synapse Studio (without extra clicks but with drag&drop)

Once again, the release notes list contained the short sentence that made me curious… hmm… that sound nice… In one of my previous post, I described the “old” way of moving artifacts around in Synapse Studio.

Click through for a demonstration.

Comments closed

SQL Server 2019 on CentOS 7.5 Issues

Published 2021-06-25 by Kevin Feasel

Aaron Bertrand recaps some recent installation issues:

I’ve created countless Docker containers running SQL Server since I first wrote about it back in 2016, but I recently had my first foray into configuring SQL Server 2019 on a real live Linux machine.

It did not go as smoothly as I expected, so I wanted to share the solution to a particular problem I haven’t seen described elsewhere.

First, let me retrace my steps.

Click through for a summary of the issues.

Comments closed

Logging Database-Level Security Commands

Published 2021-06-25 by Kevin Feasel

Kenneth Fisher keeps the receipts:

In my last post I talked about reasons why your permissions might go missing. One of the reasons, and in my experience, one of the more unusual reasons, is that a command was run that changed the permissions. SQL doesn’t natively log these. Well, technically it does. They can be found in the default trace. But I don’t generally consider that to be terribly useful because on a busy server what’s available in the default trace may not last long. Regardless I ended up creating a DDL trigger to collect any database level security commands run. There are other ways to do this but for various reasons I decided to go with a trigger. Primarily because I could create one piece of code and cover every database in the instance.

Read on to learn how to create an appropriate table and a trigger to log that data.

Comments closed

Column-Level Encryption and Hashing

Published 2021-06-25 by Kevin Feasel

Eric Rouach shows off a pair of things:

Using as an example the AdventureWorks2014 database, the first script describes the process of encrypting the “CardNumber” column from the Sales.CreditCard table while keeping the data decryptable.

Our pre-requisite is the creation of a Master Key, a Certificate and a Symmetric Key.

Once having those created, we may proceed to the addition of a new column called “CardNumberEnc” (where the suffix “Enc” stands for “Encrypted”). This column has a VARBINARY(250) Data Type and is nullable.

Read on for an example of using column-level encryption, followed by how you’d decrypt the data. Then, Eric discusses hashing, though I disagree with the nomenclature of “encryption and make the data non-decryptable.” The reason is that encryption is, by its nature, a two-way process and necessarily requires the ability to decrypt. Hashing, meanwhile, is a one-way process without a direct means of reversal. Nomenclature aside, the examples are good and I appreciate Eric using one of the larger SHA2 hashing algorithms rather than MD5.

Comments closed

Categorizing Why Bugs Can Be Tricky

Published 2021-06-24 by Kevin Feasel

Julia Evans has a list:

Hello! I’m very slowly working on writing a zine about debugging, so I asked on Twitter the other day:

If you’ve run into a bug where it felt “impossible” to understand what was happening – what made it feel that way?

Of course, bugs always happen for logical reasons, but I’ve definitely run into bugs that felt like they might be impossible for me to understand (until I figured them out!)

I got about 400 responses, which I’ll try to summarize here. I’m not going to talk about how to deal with these various kinds of “impossible” bugs in this post, I’ll just try to classify them.

Click through for the major categories, as well as explanations and sub-categories. I think an interesting follow-up to this is to ask why we find ourselves in situations where we get these sorts of bugs and what (if anything) we can do to minimize or eliminate the likelihood of their appearance.

Comments closed