The FILTER clause in SQL allows an easy way of adding WHERE conditions to aggregation functions. There were other ways to implement this before, but this syntax extension is easier to understand. In Oracle databases, it is available since version 23.26.1.
Oracle’s new JOIN TO ONE syntax offers several benefits for queries in star schemas. Let’s take a look at some typical use cases involving facts and dimensions, and see how the new join option can help reduce complexity and avoid incorrect results.
The new JOIN TO ONE syntax extension seems to be very useful for queries on star schemas. But before we can use it, we have to prove how they can be combined with different types of constraint definitions.
In the latest version of the Oracle database, it is now possible to define the partition key of a partitioned table using an expression. This was already possible previously, but only with a small workaround.
In the last part of this blog series, we will look at how to load Slowly Changing Dimension (type SCD1 and SCD2) from the Current Views we created in our multi-temporal Data Vault schema.
Loading multi-temporal data into Data Vault is quite simple, a explained in the previous blog post of this series. But how can we extract the current data in a simple and convenient way to populate Data Marts?
Data change tracking is a key concept in any data warehouse and many other analytical systems. Rather than explaining the same concepts over and over again, I decided to write a blog series on this topic. This might also be of interest to others – perhaps even to you.
Date calculations are sometimes a bit tricky. A set of new calendar functions in Oracle 26ai helps to simplify data arithmetic in SQL.
Filtering analytic functions was not straight-forward in Oracle and most other databases. Oracle 26ai now supports the QUALIFY clause in SQL. It works like a WHERE condition, but can be used for analytic functions.
Continue readingIf you are planing to develop a new data warehouse or replace an existing one, the book “Data Engine Thinking” is a good choice to read. Also if you are interested in data architecture, data modelling, code automation or in an interesting story about a fictitious IT company.
