Press "Enter" to skip to content

Month: September 2021

Create and Update Columns on Tables

Published 2021-09-29 by Kevin Feasel

Kenneth Fisher adds four columns to a table:

One of the easiest ways to collect information about table activity is to add a series of audit columns to the table. The most common set of column consists of four columns.

– When was the row created?

– Who created it?

– When was the row last updated?

– Who last updated it?

Read on to see how to create these, but definitely read the comments. Joe Celko has a good reason to avoid this style, and there’s another good reason as well: if you update the columns and the updated values are longer than what they replace, you can end up generating a lot of page splits as full pages get broken up to handle those in-page updates.

If you do need something like this, I’m growing more and more fond of an activity log table which is append-only and tracks these operations separately. That removes the page splits, allows you to deal with deletions, and gives a better idea of what happened over time for a third party. It does come at a cost if you frequently need to show the create and last updated dates (or users), but my experience has been that a vast majority of the time, we include those for internal purposes, not to display to users.

Comments closed

The Alt Key in SSMS

Published 2021-09-29 by Kevin Feasel

Barney Lawrence shows off vertical selection in SQL Server Management Studio:

I went years without knowing how useful the Alt key was, it’s not well documented but I can guarantee that once you know about it and give it a little practice you won’t be able to live without it.

While I’m filing this under SSMS Tips and Tricks but it works equally in Visual Studio, VS Code, Azure Data Studio and even Notepad ++ (but not plain old vanilla notepad). It’s worth a test in other places too.

As a quick note, it does not work quite the same way in Visual Studio Code or Azure Data Studio as in Visual Studio or SSMS: Alt+Shift+Down arrow copies the current row and pastes it in the row below. Holding Alt and Shift while using the mouse does work, but if you’re big on keyboard commands, you’ll be a bit disappointed.

Comments closed

SCD Type 2 with Delta Lake

Published 2021-09-28 by Kevin Feasel

Chris Williams continues a series on slowly changing dimensions in Delta Lake:

A Type 2 SCD is probably one of the most common examples to easily preserve history in a dimension table and is commonly used throughout any Data Warehousing/Modelling architecture. Active rows can be indicated with a boolean flag or a start and end date. In this example from the table above, all active rows can be displayed simply by returning a query where the end date is null.

Read on to see how you can implement this pattern using Delta Lake’s capabilities.

Comments closed

Rolling Means with MazamaRollUtils

Published 2021-09-28 by Kevin Feasel

Jonathan Callahan has an interesting R package for us:

The initial release of MazmaRollUtils provides all the basic rolling functions with features like alignment and missing value removal along with additional capabilities for smoothing, damping and outlier detection — all common activities in time series analysis.

Click through for an explanation of the process, and then check out the package itself on GitHub. H/T R-Bloggers.

Comments closed

Tools Are Not Documentation

Published 2021-09-28 by Kevin Feasel

Ray Kim has a good reminder for us:

I spoke to the form’s owner (this is where the political part comes in). I explained what I was doing. However, he keeps insisting: “it doesn’t have to be documented, because the form is the documentation.”

He showed me a screen shot of text on the form that explains how the particular request works. The text made a lot of sense, and it would have been ideal to fulfill at least part (if not most) of my needs. I decided that I would create a reference to it. So I looked around the form for it… and could not find it anywhere.

He finally told me that “you had to click a specific button on the form to view the text.”

If you’ve developed a process or product, it’s easy to forget that you have a fuller mental model than anybody else, and so things which are blindingly obvious to you probably aren’t to users.

Comments closed

Displaying Blob Storage-Based Images in Power Apps

Published 2021-09-28 by Kevin Feasel

Paulina Nowinska has a tutorial for us:

Today, I explain how to create a simple app in Microsoft Power Apps where:

– the data are located in Excel,

– the table contains the path to the images from public Azure Blob Storage,

– the app displays images directly from Blob Storage based on the path defined in the database (Excel file).

If you haven’t used Power Apps before, I recommend checking it out. It’s not perfectly intuitive, but it does offer a much lower-code experience than classic app development.

1 Comment

Using Powershell in Azure Cloud Shell

Published 2021-09-28 by Kevin Feasel

Hope Foley shows how you can set up Powershell to be your Azure Cloud Shell language of choice:

Part of my job is doing POCs with customers to help with Azure Data Services.  Anything that helps me move quicker is helpful so I’m a tad bit obsessed with automating things.  I have used PowerShell for more years than I’m willing to admit to help me automate what I can.  There are a lot of ways to automate things like ARM templates and DevOps, but PoSH has been my preferred hammer.  As much as I love it, I’ve ran into issue sometimes with installing modules locally on folks machines and not to mention if they have a Mac.  I wondered recently if Azure Cloud Shell would help make things easier, and it very much did and I’m super pumped to share!  This post will help run through how to get setup to run PowerShell scripts in Azure.

For people who prefer Powershell to bash, check it out.

Comments closed

Troubleshooting Login Timeouts

Published 2021-09-28 by Kevin Feasel

Grant Fritchey shows us another use of extended events:

I was recently approached at work about a company that was seeing tons of timeouts in SQL Server. I was asked how to troubleshoot this. There are lots of posts by people on this topic, but I found something I didn’t see anywhere else, let me share it with you.

Read on to see how, though as Grant notes, it’s not as easy as searching for “login_timeout” or something named similarly.

Comments closed

Excel PivotTable Drillthrough

Published 2021-09-28 by Kevin Feasel

Marco Russo and Alberto Ferrari show how you can control drillthrough when using PivotTables in Excel:

When you double-click on a cell in an Excel PivotTable, you invoke the drillthrough feature of the PivotTable which shows the underlying data for that particular cell. This feature was initially designed for Multidimensional databases in Analysis Services. In a Multidimensional model, it is also possible to add different drillthrough actions that can be activated through the context menu in Excel. While the customization of actions is not feasible for a Tabular model, the drillthrough feature is active by default. It returns all the rows visible through the filter context in the table that includes the measure definition. In many scenarios, this default behavior does not provide a result consistent with the data computed in the result that you see visible. Through the Detail Rows Expression property of a measure in the Tabular model, you can customize the drillthrough behavior in Excel, thus controlling the rows and columns returned to the user.

This article explains how to use the Detail Rows Expression property to customize the Excel drillthrough behavior for a PivotTable connected to a Power BI dataset or an Analysis Services database – they can be identified as Tabular models in the remaining part of this article.

Read on for a demonstration, plenty of explanation, and even some elucidation.

Comments closed