Press "Enter" to skip to content

Month: August 2023

The Concept of Schema in Relational Databases

Published 2023-08-30 by Kevin Feasel

Adron Hall explains how different relational database management systems describe schemas:

From the viewpoint of someone familiar with the general idea of a schema, it can indeed seem unusual that databases like SQL Server, Oracle, MariaDB/MySQL, and PostgreSQL each interpret and implement schemas in slightly (or sometimes, vastly) different ways. While the core idea behind a schema as a structured container or namespace for database objects remains somewhat consistent, the exact nature, utility, and behavior of schemas vary across these systems.

Read on for an overview of these for four products, as well as what the ANSI standard indicates.

Comments closed

Setting Table and Matrix Column Widths in Power BI

Published 2023-08-30 by Kevin Feasel

Kurt Buhler controls the horizontal, Kurt Buhler controls the vertical:

One challenge of the table and matrix visuals in Power BI is that it’s difficult to precisely and consistently set column widths. Unlike in Excel, where you can set the row and column widths in a spreadsheet, you have no option in the visual interface to control the column width property. However, it’s still possible to control it in the report metadata, which is exposed in the officially supported Power BI Projects format (.pbip) which is in preview. Notably, however, opening and modifying report metadata from this format isn’t yet supported. Despite that fact, it still works reliably, so I thought I’d demonstrate how to do this.

There are a fair number of steps involved but it all makes sense in the end.

Comments closed

Comparing the Microsoft Fabric Data Wrangler and Power Query Editor

Published 2023-08-30 by Kevin Feasel

Reza Rad performs a comparison:

Power Query Editor and Data Wrangler are data transformation and preparation tools in Microsoft Fabric. There are similarities between these two tools. However, there are differences, too. It is essential to know the capabilities of each tool to understand which one should be used for what purpose and scenario. In this article, this is our quest.

Reza includes a video and an article. Reza also has a summary chart at the bottom.

Comments closed

Data Type Conversions and Snowflake Performance

Published 2023-08-30 by Kevin Feasel

Kevin Wilkie is implicit in this whole thing:

One of the ways we can get better at speed is to attempt several slightly different ways that can get you (hopefully) the same data. Some tables work better with one query while some work better with another query.

Let’s work through a scenario in Snowflake and we’ll see which one is faster under “normal” conditions.

Click through for a few query examples and how they end up performing.

Comments closed

Versioned State Store in Kafka Streams

Published 2023-08-29 by Kevin Feasel

Victoria Xia announces new functionality in Apache Kafka 3.5:

Since the introduction of stream processing, there have been three certainties in life: death, taxes, and out-of-order data. As a stream processing library built for Apache Kafka, Kafka Streams processes data in offset order. When out-of-order data is present, offset order differs from timestamp order and care must be taken to ensure that processing results respect timestamp order where appropriate. The introduction of versioned state stores to Kafka Streams in the Apache Kafka 3.5 release is a huge milestone in this direction.

In this blog post, I’ll address the what, why, and how of versioned stores in Kafka Streams, including what they are, why you might like to use them, how to get started, and a couple of things to watch out for when upgrading.

Read on to see what this entails and how you can try it out yourself.

Comments closed

Returning Matrix Elements in Spiral Order in R

Published 2023-08-29 by Kevin Feasel

Tomaz Kastrun forgot to remove The Club from his REPL:

Another one from the Leetcode challenge. This time, get the elements (single values) from the matrix in a spiral order with a starting position of [1,1].

So, the basic idea is to retrieve a vector of elements from a matrix in the following order:

Probably not something you’d use with any frequency, but it’s a fun way to learn how to operate within matrices.

Comments closed

Built-In R Datasets

Published 2023-08-29 by Kevin Feasel

Adrian Tam continues a series on getting started in R:

The ecosystem in R contains not only the function libraries to help you perform statistical analysis but also the data library that gives you some famous datasets to test out your program. There are a lot of built-in datasets in R. In this post, you will:

  • Learn some of the built-in datasets
  • Know how to use these datasets

Let’s get started.

Most of these built-in sets are fairly small and able to help you illustrate a specific point.

Comments closed

The Medallion Architecture in Data Modeling

Published 2023-08-29 by Kevin Feasel

Nikola Ilic gets the gold:

The most common pattern for modeling the data in the lakehouse is called a medallion. I love this name – it’s really easy to remember. But, why medallion? Tag along and you’ll soon find out why.

The same as for the lakehouse concept, credits for being pioneers in the medallion approach goes to Databricks.

What I’ve found interesting is the number of people who have taken to disliking the medallion architecture terms because Databricks pushed it so hard that their clients automatically assumed “medallion = using Databricks.”

Comments closed

Managing Security Roles for Hierarchical Organizations in Power BI

Published 2023-08-29 by Kevin Feasel

Marco Russo and Alberto Ferrari are working for The Man:

The security model in Tabular used by Power BI can filter rows of a table based on a DAX expression. When security is applied to a hierarchical structure, every hierarchy level is represented by a different column in the table. This structure can make it challenging to define a dynamic security filter based on the name of a node in the hierarchy, because the DAX expression must filter the column corresponding to the hierarchical level in which that node exists. If the security needs to be maintained dynamically in a configuration table, the resulting code may end up being extremely complex and hard to maintain, as well as create possible performance issues.

Without describing the complexity of solutions based on a filter applied directly to the appropriate hierarchy level, we want to describe a solution that minimizes the effort required in maintaining a configuration table for the dynamic security rules, while also providing good performance at execution time by minimizing the processing overhead required to apply the dynamic security.

Click through for the scenario and how you can implement this kind of security model in Power BI.

Comments closed