Press "Enter" to skip to content

Month: January 2023

Performance Comparing DISTINCT to GROUP BY

Published 2023-01-20 by Kevin Feasel

Reitse Eskens does a performance comparison:

A few days ago, I heard someone stating that Group By was much quicker than Distinct. Less disk impact, less memory etc.
So, I thought I’d find out if it’s true or not because I found it interesting. I always thought there was no difference. I tested a single small table and found no difference in speed, reads or execution plan. But that’s no real world example. Usually the tables contain a lot of data and are joined to other tables.

Click through for the results of Reitse’s analysis.

Comments closed

An Overview of the Power BI REST API

Published 2023-01-20 by Kevin Feasel

Reza Rad takes a look at Power BI’s API:

You can interact with the Power BI objects in the Power BI Service through a set of APIs called Power BI REST API. The Power BI REST API can help automate tasks, build tools that work with Power BI, the configuration of Power BI outside of the platform, and embed Power BI into a third-party application. In this article and video, I’ll explain REST API, why it is useful, and a few examples.

Click through for the video and explanation.

Comments closed

Good Practices for Powershell Development

Published 2023-01-20 by Kevin Feasel

Jeff Hicks shares some good practices with us:

Over the course of the last several weeks, I’ve been sharing and demonstrating techniques for writing effective PowerShell functions. I know I’ve mentioned a few recommended best practices. But since they are important, I want to review and re-emphasize them. These practices will make your code easier to write, easier to debug or troubleshoot, and more secure. I’d like to think some of them are simple, common sense, but sometimes we need someone to remind us. These items are not in any particular order.

There are some Powershell specifics here but most of the advice is language-agnostic.

Comments closed

Creating a Multi-Function Powershell Module

Published 2023-01-20 by Kevin Feasel

Patrick Gruenauer builds a module:

In this blog post, I will show you how to create a module with multiple functions using an example. You will see that this is not rocket science. Let’s jump in.

In order to use multiple functions in a module, we have to declare them as functions to export. This also means we need a module manifest file with that statements in it.

Read on to see what it takes to build a module in Powershell.

Comments closed

Object Deconstruction in C#

Published 2023-01-19 by Kevin Feasel

Patrick Smacchia shows off a feature in C#:

C# 7.0 introduced the deconstruction syntax. It allows developers to extract in a single expression, properties of an object or elements of a tuple and then to assign them to distinct variables . Here is a small program candidate to be simplified with deconstruction:

I enjoy (more than I should) being able to say “C# got this from F#.” Object deconstruction is quite useful and it’s good to see that the C# syntax is close to what we get in F# or Python.

Comments closed

Error Handling Patterns in ADF Pipelines

Published 2023-01-19 by Kevin Feasel

Chenye Charlie Zhu begins a new series:

Orchestration allows conditional logic and enables user to take different based upon outcomes of a previous activity. Building upon the concepts of conditional paths, ADF and Synapse pipeline allows users to build versatile and resilient work flows that can handle unexpected errors that work smoothly in auto-pilot mode.

This is an ongoing series that gradually level up and help you build even more complicated logic to handle more scenarios. We will walk through examples for some common use cases, and help you to build functional and useful work flows.

Read on for a few error-handling patterns.

Comments closed

Logic App Errors with Variables in Sharepoint Actions

Published 2023-01-19 by Kevin Feasel

Koen Verbeeck troubleshoots an issue:

I have a Logic App that reads out a SharePoint library and stores all the documents found into Azure Blob Storage (ADF only supports Lists). I was trying to make this Logic App “generic”, meaning I could change the source folder and the destination container by using variables. That way, I have one single Logic App which can read out any SharePoint library, instead of creating a new Logic App for each library.

So I adapted my HTTP trigger to accept a JSON payload, which contains the name of the folder on SharePoint and the name of the blob container.

Read on to see the error message, as well as how Koen resolved the problem.

Comments closed

A Story of Implicit Conversions

Published 2023-01-19 by Kevin Feasel

Tracy Boggiano tells a tale:

Recently I had a situation where I was looking at a SQL Instance due to a contractor who controls the system wanting to drop some indexes and they couldn’t because they couldn’t get a lock request.  Sadly, they were using the GUI to try to accomplish this.  Always makes me sad when we aren’t using scripts.  So, I took a deep drive into what was actually running on this system.  I had them send over scripts for what they were trying to do.  Meanwhile, I looked at the system and discovered some things.  First, we had really had RESOURCE_SEMAPHORE wait types for two days which indicates it waiting on memory.  So, I took a look at the memory clerks.  The top memory clerk was for lock manager at 32 GBs and the system only has 64 GBs, which is abnormal. 

Read on for the full story. This particular fix worked, I presume, because the ORM was sending all parameters as Unicode, whereas none of the strings in the database were Unicode—they were all VARCHAR rather than NVARCHAR.

Comments closed

Securing Temporal Tables

Published 2023-01-19 by Kevin Feasel

Daniel Hutmacher does a little locking down:

You may have already discovered a relatively new feature in SQL Server called system-versioned temporal tables. You can have SQL Server set up a history table that keeps track of all the changes made to a table, a bit similar to what business intelligence people would call a “slowly changing dimension”.

What happens behind the scenes is that SQL Server creates a separate table that keeps track of previous versions of row changes, along with “from” and “to” timestamps. That way, you can view the contents of the table as it was at any given point in time.

But how to you version the contents of a table, while hiding things like deleted records from prying eyes?

There’s not a whole lot we can do, but Daniel shows what we are able to do.

Comments closed

Building a Power BI Audit Log

Published 2023-01-19 by Kevin Feasel

Reza Rad analyzes Power BI logs in Power BI:

Power BI dashboard and reports come with a usage metric, which you can see how users used this content. There is another report for usage metrics across the entire tenant, which you can see if you have access to the Power BI Administrator account under Admin Panel in the Power BI Service. However, what if you want to create your own detailed usage metrics report across the entire tenant? How if you want to see across all workspaces in the tenant, how was the consumption of reports and dashboards? Who created reports, who are users of them, in which workspace these are located and etc. This information is not easily accessible in the Power BI Service. In this article and video, I will show you how to extract the Audit log from Office 365, export it into text files, and create a Power BI report from it, or in other words, How to create your custom usage metrics report across the tenant. If you like to learn more about Power BI, read the Power BI book from Rookie to Rock Star.

Click through for a video as well as the full blog post.

Comments closed