Press "Enter" to skip to content

Month: April 2024

Data Analysis with Window Functions

Published 2024-04-18 by Kevin Feasel

Erika Balla looks out the window:

Window functions are an advanced feature of SQL that provides powerful tools for detailed data analysis and manipulation without grouping data into single output rows, which is common in aggregate functions. These functions operate on a set of rows and return a value for each row based on the calculation against the set.

In this article, we delve into window functions in SQL Server. You will learn how to apply various window functions, including moving averages, ranking, and cumulative sums, to achieve comprehensive analytics on data sets. 

Click through for several examples.

Comments closed

pl/dotnet Version 0.99

Published 2024-04-18 by Kevin Feasel

Brick Abode announces F# and C# support within Postgres:

pl/dotnet adds full support for C# and F# to PostgreSQL. 0.99 is our public beta release; we wish to share its amazingness with the world.

  • We support all PL operations: functions, procedures, DO, SPI, triggers, records, SRF, OUT/INOUT, table functions, etc
  • We natively support 40 out of 46 standard user types, the most of any external PL
  • Fully NPGSQL-compatible, and SPI is exposed through the NPGSQL API for maximum compatibility
  • In our benchmarks, C# and F# are the fastest Procedural Languages in PostrgreSQL
  • All features are fully tested for both C# and F#, with 1013 unit tests
  • 100% free software under the PostgreSQL license

This is a beta release; we invite usage and welcome feedback.

Look at me, side-eyeing SQL Server and how SQLCLR still doesn’t have F# support. I still maintain that the single biggest mistake Microsoft made around SQLCLR was adopting the “safe” and “unsafe” mode language. C# developers understood that “unsafe” meant you could get access to pointers and other internals that .NET languages typically hide from us. But try explaining that to a DBA, who doesn’t understand the language or the concepts.

On the bright side, .NET languages are the fastest procedural languages for Postgres, so that’s pretty neat. H/T Sergey Tihon.

Comments closed

Waiting for a Job to Complete in Powershell

Published 2024-04-18 by Kevin Feasel

Patrick Grueanuer waits for a job:

The cmdlet Wait-Job waits until one or all of the PowerShell jobs running in the session are in a terminating state. In this blog post I will show you an example you can build on. Let’s get started.

Start-Job creates one or more PowerShell background jobs. These jobs are running hidden in the background and enable you to continue your work in PowerShell. This example starts a port scan background job.

Click through to see how Start-Job works and what you can do with other job-related cmdlets.

Comments closed

Refreshing a Power BI Semantic Model via Fabric Pipelines

Published 2024-04-18 by Kevin Feasel

Marc Lelijveld builds a pipeline:

Recently, Microsoft released a new activity type to trigger Power BI Semantic Model refreshes. A great step forward to have a native pipeline activity and no longer need to setup complex steps with APIs and authentication manually. Or is there still a case?

In this blog I will elaborate on what this new Pipeline activity exactly is, various scenarios in which it can be applied and finally some edge cases and shortcomings.

Click through to see how it works.

Comments closed

New Data Governance Features in Microsoft Purview

Published 2024-04-18 by Kevin Feasel

James Serra takes a look:

The changes to Purview, in short, are that the “Data Catalog” section of Microsoft Purview was redesigned and updated with new features, including a “Data management” section and a “Data estate health” section. Within the data management section, you can easily define and assign business-friendly terminology (such as Finance and Claims). Business-friendly language follows the data governance experience through Data Products (a collection of data assets used for a business function), Business Domains (ownership of Data Products), Data Quality (assessment of quality), Data Access, and Data Estate Health (reports and insights). Let’s explore each of these new features in detail:

Read on for a fairly deep dive into these changes.

Comments closed

Specifying Follow-Up Times for Longitudinal Data in simstudy

Published 2024-04-17 by Kevin Feasel

Keith Goldfield updates the simstudy package:

A researcher reached out to me a few weeks ago. They were trying to generate longitudinal data that included irregularly spaced follow-up periods. The default periods generated by the function addPeriods in the simstudy package are {0,1,2,…,n−1}{0,1,2,…,n−1}, where there are n total periods. However, when follow-up periods required more specificity, such as {0,90,180,365}{0,90,180,365} days from baseline, users had to manually add them. Originally, I had intended to incorporate this feature into the function, but unfortunately it slipped through the cracks. Thanks to the clear motivation provided by the researcher, I’ve implemented this enhancement. Users can now replace the default vector with their desired set of follow-up periods using the new argument periodVec. This addition is available in the development version of simstudy on GitHub.

Read on to see how it works. H/T R-Bloggers.

Comments closed

Locking down Branches in GitHub

Published 2024-04-17 by Kevin Feasel

I have a new video:

In this video, I show you how you can prevent people from checking directly into a specific branch, as well as explaining why you might want to enable this feature and how it looks for administrators.

Admittedly, this video would work a bit better with multiple user accounts, but I think I got the general point across.

Comments closed

Deploying Power Apps as Solutions or Apps

Published 2024-04-17 by Kevin Feasel

Deborah Melkin explains deployment:

One of the big challenges I had was how to move the app from different environments. Following best software development practices, we have a development environment in our Power Platform that uses a development database as well as a production environment that points to a different production database. This has been a multi-step process with hurdles along the way.

Read on for Deborah’s thoughts and some of the issues she hit along the way.

Comments closed

Metadata Tables and Azure Data Factory

Published 2024-04-17 by Kevin Feasel

Martin Schoombee brings back metadata tables:

The metadata that drives the execution within a framework is probably the most critical part. Going back to our analogy of building a house, the metadata would be the foundation. It is here where you are going to make some architectural decisions outside of which the framework cannot operate.

One such decision is how configurable or flexible you’d like the framework to be. In other words, how many attributes would you like to be dynamic and/or have the option to change during execution. It seems like an easy choice, and most engineers would lean towards “everything” or “as much as possible” as an answer. In reality however, the trade-off is complexity and the more dynamic you make the framework the more complicated it becomes. And you pay for the complexity later when you need to maintain or add new functionality to it.

Read on to see how it all fits together.

Comments closed

Elastic Jobs for Azure SQL DB

Published 2024-04-17 by Kevin Feasel

Josephine Bush digs into Elastic Jobs:

I know if you are a SQL Server DBA using Azure SQL DB, you’ve been sorely missing the agent. Enter Elastic Jobs to help you schedule jobs more easily against Azure SQL DB. I will cover setting up and scheduling Elastic Jobs to execute Ola index maintenance. If you’ve used Elastic Jobs in the past, there are some very nice improvements with the recent GA release, so don’t feel discouraged if you didn’t like it in the past—it’s way better now!

Read on for a deep dive into Elastic Jobs.

Comments closed