Press "Enter" to skip to content

Month: February 2022

Table-Valued Functions and Dynamic M Parameters

Published 2022-02-21 by Kevin Feasel

Chris Webb uses dynamic M parameters:

My favourite – and it seems many other people’s favourite – new feature in the February 2022 Power BI Desktop release is support for more datasources (including SQL Server, Azure SQL DB and Synapse) with dynamic M parameters. In my opinion dynamic M parameters are extremely important for anyone planning to use DirectQuery: they give you a lot more control over the SQL that is generated by Power BI and therefore give you a lot more control over query performance.

Teo Lachev has already stolen my thunder and blogged about how the new functionality allows you to use a TSQL stored procedure as the source of a table in DirectQuery mode. In this post I’m going to show you something very similar – but instead of using a stored procedure, I’m going to show a simple example of how to use a TSQL table-valued function, which I think has a slight advantage in terms of ease-of-use.

Leaving aside thoughts on table-valued functions in general, dynamic M parameters looks like a really nice feature and as Chris notes, it also works for things like stored procedures.

Comments closed

Multi-Column Transformations in Power Query

Published 2022-02-21 by Kevin Feasel

Imke Feldmann has the need for speed:

In this article I’m going to present a method about transforming multiple columns at once in a fast way. This method also allows you to reference columns that exist in your table already. As I have described in a previous article, this cannot be done using the native Table.TransformColumns function that will be applied if you do column transformations using the UI in Power Query. The function I am sharing here allows you to enter a list of column names to be transformed and a function that defines the transformation itself. So you have to be familiar with defining custom functions to use this approach.

Click through for Imke’s function and explanation but also check out the comments for another take on the problem.

Comments closed

Basics of Risk Management

Published 2022-02-21 by Kevin Feasel

Matthew Roche lays out some of the basics of risk management:

One simple and lightweight approach for risk management involves looking at two factors: risk likelihood, and risk impact.

Risk likelihood is just what it sounds like: how likely is the risk to occur. Once you’re aware that a risk exists, you can measure or estimate how likely that risk is to be realized. In many situations an educated guess is good enough. You don’t need to have a perfectly accurate number – you just need a number that no key stakeholders disagree with too much.[3] Rather than assigning a percentage value I prefer to use a simple 1-10 scale. This helps make it clear that it’s just an approximation, and can help prevent unproductive discussions about whether a given risk is 25% likely or 26% likely.

Risk impact is also what it sounds like: how bad would it be if the risk did occur? I also like to use a simple 1-10 scale for measuring risk impact, which is more obviously subjective than the risk likelihood. So long as everyone who needs to agree agrees that the impact a given risk is 3 or 4 or whatever, that’s what matters.

Read on for a summary of the topic and a good leaving-off point to learn more.

Comments closed

Redis Streams for Apache Kafka Users

Published 2022-02-18 by Kevin Feasel

Paul Brebner gives us an overview of Redis Streams:

The Redis Streams data type is newer than the Redis Pub/Sub data type, and is designed to support “disconnected” distributed streaming applications. The data type itself is essentially an append-only data structure, stored in memory—basically preserved messages!

This differs from Redis Pub/Sub channels, which are focused only on the delivery of messages to currently connected subscribers only—Pub/Sub uses a push-based delivery mechanism, and if there are no current connected subscribers, then messages are simply discarded. And channels don’t remember messages to enable disconnected subscribers to catch up with missed messages, replay messages, or read different ranges of messages, etc. But channels are fast!

I’m still partial toward Kafka but Redis Streams are pretty nice.

Comments closed

What Comes after the Well-Architected Framework Review

Published 2022-02-18 by Kevin Feasel

Ben Brauer takes us through the next step:

Congratulations! You’ve finished your Well-Architected Review of a workload, giving you a better understanding of where it could be fortified along the five pillars: Security, Reliability, Operational Excellence, Performance Efficiency and Cost Optimization. You have received Microsoft’s best practices as recommendations based on your answers to questions specific to each pillar.

The report (example below) shows a Well-Architected score for each pillar, as well as prioritized recommendations that allows for you to focus on biggest areas of impact. A great example is virtual machine right sizing. You can significantly lower your costs if you know which VM is best suited for your workload type.

By the way, if you have Azure resources, I highly recommend checking out the Well-Architected Framework assessment link there. It can take a very long time to go through because of just how many questions there are; that said, the results are also pretty specific and can be immediately helpful.

Comments closed

Deploying an Azure Synapse Analytics Workspace

Published 2022-02-18 by Kevin Feasel

Rajendra Gupta builds out an Azure Synapse Analytics workspace:

In the article, An Overview of the Azure Synapse Analytics, we explored the Azure Synapse workspace and its features as an analytics service combining Big data analytics and enterprise data warehousing.

This article is a practical demonstration of deploying Azure Synapse Analytics workspace using the Azure portal.

Click through for step-by-step instructions on how to do it.

Comments closed

Color Palettes in Powershell and WPF

Published 2022-02-18 by Kevin Feasel

Jeffrey Hicks has been working with color lately:

Let’s continue looking at how to use PowerShell and a Windows Presentation Foundation (WPF) form to display [System.Drawing.Color] values. This article builds on an earlier post so if you missed it, take a few minutes to get caught up. As I did earlier, before running any WPF code in PowerShell, you should load the required type assemblies.

This has been a fun series to watch.

Comments closed

Amazon RDS: Backups and Patching

Published 2022-02-18 by Kevin Feasel

Joey D’Antoni is not impressed:

While some services include other really useful features (for example the query data collected by the Azure SQL Database and Managed Instance platforms), I wanted to focus on the common value adds to PaaS systems across providers. I made the last two of these bold, because I feel like they are are the most important, especially in scenarios where the vendor doesn’t own the source to the applications. Like Amazon RDS for SQL Server.

Click through for Joey’s thoughts on the topic.

Comments closed

Version Control for SSMS Templates

Published 2022-02-18 by Kevin Feasel

Kevin Chant saves some templates:

Previously I wrote a post about how to do version control for SQL Server Management Studio templates using Azure Repos. I wanted to highlight some things I did not point out in that post. In addition, I thought it was only fair that I showed how to do it with GitHub.

Plus, in my last T-SQL Tuesday post I mentioned the SQL Server diagnostic queries provided by Glenn Berry. Which reminded me to do this post. Because I want to do an example based on sharing one of the queries with your colleagues via GitHub. Like in the below diagram.

Click through to see the process.

Comments closed

SSIS Integration Runtimes in Synapse

Published 2022-02-18 by Kevin Feasel

Andy Leonard heard it on the grape vine:

My first response was – and I quote – “WOO HOO!” It’s good to see SSIS getting some love.

A couple years ago, someone claimed SSIS was dying. I first checked it out. Then I blogged about it in a post titled SSIS is Not Dead (Or Dying). It’s been a couple years and SSIS is not dead. One could say SSIS functionality being added to Azure Synapse, arguably Azure’s flagship offering, appears to be the opposite of dying.

I’m not sure I’m as sanguine as Andy is about the future of SSIS but I will say at the very least I agree that it’s not going anywhere anytime soon.

Comments closed