Vertica Stored Procedures to Maintain Slowly Changing Dimensions
All popular BI and ETL tools are optimised for working with dimensional data models, with fact and dimension tables.
Dimensions contain reference / master data. Slowly Changing Dimensions also contain the history of their elements.
In order to make this history easily accessible for the front end, sophisticated update policies need to be applied, and in the case of Vertica, in a way to optimally benefit from Vertica’s mass transaction framework.
The advent of Stored Procedures in Vertica now allows for the usage of a group of Stored Procedures, reading the same metadata on Slowly Changing Dimensions, to create the necessary tables, and to create on the fly and run the sophisticated processes (i.e. SQL statements) for initial and incremental load of these SCD tables. We will lead you through the techniques used to make this happen.