Press "Enter" to skip to content

Day: September 28, 2021

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