Auxiliary timelines and partitioned loading

Introduction

QUIPU4 was launched in April 2019 at the BI & DW Summit. During the launch, the team behind QUIPU4 promised that the new modular approach would enable the quick development of modules with new functionality for use by clients. This blog focuses on yet another two features that were delivered shortly.

Model-to-model transformation

QUIPU4 is a platform that supports model-to-model transformation. This is how the product sets itself apart from other commonly accepted ETL tools and other data warehouse automation tools. Although such tools usually work well as far as the transformation and manipulation of data at the entity or attribute level is concerned, they lack the support needed to define and develop transformations at the model level. It is at this higher level that patterns can be found and applied, and therefore can be generated. According to QOSQO, this results in a much more efficient way of developing new data solutions quickly.

Use case: multiple timelines

Two customers using QUIPU4 needed to record the multiple timelines which are available in their source systems. For this reason, the feature set relating to the generation of a historical data archive (HDA) was extended. Besides the already available functionality used to record the time that data is loaded into the warehouse, it is now possible to define as many auxiliary timelines as considered necessary.

Well known examples of auxiliary timelines include the ability to: 1. record the date in the HDA when something happened in the real world; 2. record the date in the HDA when the corresponding data was recorded in the source system. This makes it possible to record arbitrary times of events, even where these have taken place at different times.

Example:

Someone moves house on 1 March (real world event), but only informs the buildings insurer on 17 March (registration in the source system). The associated data is read into the HDA once a month. The next time will be on 1 April.

Just as with the regular delta detection mechanism, it is also possible to detect changes for these two timelines and to end-date these so that changes are ‘stacked’ in the HDA in the same way as with delta detection.

In the above example, this may be the case if the notified house move took place not on 1 March, but on 1 February. From a business perspective, it could be useful to find out about this.

Use case: partitioned loading

Once again, a customer request was the main trigger for us to develop this feature. Partitioned loading is sometimes also referred to as FUELTA, a contraction of full load and delta load. This form of data loading allows a fixed partition of data to be loaded. This avoids a situation where delivery of a subset of data causes the available history in the data warehouse to be deleted (based on end-dating). This is useful, for instance, when loading data into financial systems which only supply the most recent financial year and where all previous financial years have already been loaded into the data warehouse. This feature has been implemented generically, which means it can also be applied to other types of data partitioning, for instance, based on region.

This feature only applies a ‘delete’ detection to the data already available from the partition. To use the function, a so-called ‘partition expression’ is required. This is easily created by the architect or developer.

For more information, a white paper and a comprehensive manual visit www.quipu.nl. Interested in QUIPU4? You can request a free, one-month trial licence on our website. If desired, we can arrange for a demo.