Major revision of the DIRECT framework

A new set of improvements have been committed to the data logistics control (‘ETL process control’) framework Github.

This framework, referred to DIRECT (thanks to acronimify.com), assumes the spot of the Data Logistics Process Control in the engine metaphor for flexible data platform management. In case you were wondering, it’s the Data Integration Run-time Execution Control Tool!

This is one of my favourite components, as it’s both so simple but also hard to truly get right. This framework has been evolving for more than 10 years now and is very stable. However, from time to time (once or twice a year) we still find the occasional bug. For me, it just shows how that there is much more than meets the eye on this important component.

One of the main changes is the addition of separate schemas so that the framework can both be deployed as separate database or included in other databases as a reference. This is important because if you follow the mindset of eventual consistency / full parallel loading there is no need for a centralised repository anymore.

In fact, relying on a central repository for process control may be an issue in certain scenarios – for example when you perform a restore of one of the databases in an on-premise environment.

It can happen (obviously depending on the setup) that the control framework repository is located in a different database than the framework itself. This means the points / process execution instances (referred to Module Instance IDs in the DIRECT framework) either need to be backed-up along with the data backups, otherwise inconsistencies in the control framework metadata may occur.

The ability to deploy the DIRECT framework inside each database means that this backup / restore can happen as a logical unit of work. The pointers are backed-up and restored along with the data.

Preferences on the setup of the technical environment vary of course, hence the ability for the framework to be deployed both as a central repository and as part of each database.

For ease of use I have added the updated solution as a database project, so Microsoft users can add this to their solution assuming Visual Studio is used. The database can be referenced inside other databases this way directly, or via a dacpac file.

Some notes on this have been added to the documentation, but there is more to say about the various options for setup. Feel free to drop me an email if you have any questions.

Many other smaller changes have been made to simplify the operation of the framework. Some of these other changes include:

• ‘Layer’ is made optional. There is still a Foreign Key from Area to Layer, but this is now optional.
• ‘Frequency’ has been dropped as a separate table and Foreign Keys have been removed. Both Batch and Module can still be set to a certain frequency, but this is free format. Control of values is now done via the ‘hooks’ into the framework.
• ‘Data Audit’ and ‘Data Audit Type’ have been removed, as their function has been superseded by the Alerting & Notification setup. To logically separate these two components I have removed these from Direct.
• The ‘Data Store’ subject area (including Data Store Type and the Module Data Store intersection entity) have been added to the Module definition. This is easier to manage and query, as the metadata can be directly generated into the Module table now. It also simplifies the retrieval of sources and targets. Note: the source- and target are now ‘data objects’ as per the interface for Data Warehouse Automation.
• Added Active / Inactive flag on Module-Parameter level so individual parameters can be switched on and off.
• Removed the Error Bitmap (including Severity and Error Type). These are better at home in the Alerting & Notification framework, similar to the Audit tables.

Last, but not least, various maintenance and reporting views have been added in a dedicated schema ‘omd_reporting’.

Previously I was able to just share a link to the version of the physical model on SQLDBM, but unfortunately this feature has been removed so the best I can do is share an image of the physical model. This model, including the associated scripts are available on the Github.