Press "Enter" to skip to content

Curated SQL Posts

The Importance of Orchestration in E(L)TL Processes

Published 2024-04-10 by Kevin Feasel

Martin Schoombee begins a new series:

In the context of what we’re talking about throughout this series – facilitating the execution of an ETL process in a platform like Azure Data Factory – orchestration means that we’re using the ETL tool primarily for the “E” (Extract) part of the process. In addition to that, most people I know would also use the ETL tool to facilitate the workflow, in other words the order of execution and any constraints that go along with that.

In what I’d like to call the “traditional” approach for lack of a better term, all parts of the ETL process are performed natively by the tool (image below), using whatever built-in tasks are available and of course accounting for any nuances. With this approach, transformations are typically performed in transit and in memory.

Read on to see how the Orchestration approach differs from the traditional ETL approach.

Leave a Comment

Creating DAX Measures Directly in the Power BI Service

Published 2024-04-10 by Kevin Feasel

Gilbert Quevauvilliers skips the desktop step:

There have been some updates to Power BI with Visual Calculations, and as of the March 2024 release you can now create and edit visual calculations in the Power BI Service.

Here are details from the blog post: Power BI March 2024 Feature Summary | Visual Calculations Update

I then saw an email which made me think, could I then use the visual calculations to create a DAX measure directly in the Power BI Service on an existing Power BI report.

The answer, it turns out, is yes. Read on to see how.

Leave a Comment

Trying out Microsoft Fabric Mirroring of Cosmos DB

Published 2024-04-10 by Kevin Feasel

Kevin Chant gives it the ol’ college try:

In this post I cover some initial testing of Mirroring Azure Cosmos DB Databases in Microsoft Fabric that I performed.

I wanted to do this post for various reasons. Including the fact that it was announced during the Microsoft Fabric Community Conference that Mirroring is now in Public Preview.

Which means that you can now mirror data from Azure SQL Database, Azure Cosmos DB and Snowflake into your own Microsoft Fabric tenant. Even trial tenants.

Kevin takes us through the process and gives it a try, sharing with us the results of some testing, including a test insertion of 100 million rows.

Leave a Comment

EXPAND() and COLLAPSE() in Power BI Visual Calculations

Published 2024-04-09 by Kevin Feasel

Marco Russo and Alberto Ferrari play the accordion:

In previous articles, we introduced the concepts of visual context, the visual lattice, and the two visual context navigation operations: EXPAND and COLLAPSE. In this article, we build on that knowledge to first compute a simple percentage over the parent: a simple calculation to consolidate the understanding of EXPAND and COLLAPSE. Next, we move on to a much more complex scenario, computing the inflation-adjusted sales using visual calculations.

Read on to see what the two functions do, using a pair of examples.

Leave a Comment

Common Table Expressions in Postgres

Published 2024-04-09 by Kevin Feasel

Ryan Booz builds a CTE:

In the first article in this transforming data series, I discussed how powerful PostgreSQL can be in ingesting and transforming data for analysis. Over the last few decades, this was traditionally done with a methodology called Extract-Transform-Load (ETL) which usually requires external tools. The goal of ETL is to do the transformation work outside of the database and only import the final form of data that is needed for further analysis and reporting.

However, as databases have improved and matured, there are more capabilities to do much of the raw data transformation inside of the database. In doing so, we flip the process just slightly so that we Extract-Load-Transform (ELT), focusing on getting the raw data into the database and transforming it internally. In many circumstances this can dramatically improve the iteration of development because we can use SQL rather than external tools.

Read on for the scenario and how you can use common table expressions in Postgres. They’re very similar to what we have in SQL Server, though a few differences do exist.

Leave a Comment

Cloud Governance Guidance in the Cloud Adoption Framework

Published 2024-04-09 by Kevin Feasel

Stephen Sumner notes an addition to the Microsoft Cloud Adoption Framework (CAF) for Azure:

We are thrilled to announce the latest enhancement to Microsoft’s Cloud Adoption Framework for Azure. We comprehensively updated our cloud governance guidance in the Govern section of the Cloud Adoption Framework (CAF). The updated governance guidance represents Microsoft’s commitment to supporting your organization’s cloud journey, offering a clearer, more accessible, and comprehensive path to effective cloud governance. It encompasses identity, cost, resource, data, and AI governance among other areas of governance categories.

Whether you’re a startup looking to scale efficiently or a large enterprise aiming to refine your governance practices, we designed this governance guidance to meet your needs and guide you to where you need to be.

Read on to learn more about what cloud governance means and the tooling available.

Leave a Comment

SQL Server Index Included Columns and Log Bloat

Published 2024-04-09 by Kevin Feasel

Forrest McDaniel explains that TANSTAAFL:

Let me share the conclusion now: included columns in an index always get included in the log record, even if they weren’t modified. The same is not true for the clustered index. This can really matter if log generation is important to your environment, e.g. using an AG.

Click through for the proof. We often think of included columns on indexes as being nearly free performance gains, but “nearly” is carrying a bit of water here. This shouldn’t dissuade you from using included columns on indexes, but is a valid reason not to include all 50 non-index columns in a table “just in case.”

Leave a Comment

Microsoft Fabric Workspace Soft Deletion and Retention

Published 2024-04-09 by Kevin Feasel

Marc Lelijveld fills us in on the details:

This morning, I was looking for a specific tenant setting in the Fabric admin portal. While scrolling through a few of the tenant settings, I came across a specific one, which I like to elaborate on a bit more in detail, given I recently also had some questions around this topic from customers.

What happens if you delete a Fabric workspace? Is there a way to recover? How and for how long? Well, we now have a tenant setting to control this!

Click through for the details.

Leave a Comment