In this post I am going to describe the data warehouse generate procedure (DW procedure) that was developed using Postgres that is a part of an enterprise system platform that has an inherent capability to exchange data between systems.
A conglomerate like government or healthcare, will have systems implemented by business unit. Each business unit will have a core enterprise system that is augmented to meet their unique needs. The conglomerate will use the DW procedure to create a data warehouse that encompasses data from all business units.
The DW procedure starts by aggregating all data dictionaries from all systems. From there it generates the schema based on the consolidated data dictionary and then populates the schema with data from all systems. Finally, it programmatically generates views to access the data warehouse based on the consolidated data dictionary.
The DW Procedure has two key features worth noting.
1. It can be run in update mode. In this case, it will refresh a data warehouse even if there are schema changes in one of the contributing systems.
2. It is capable of generating multiple data warehouses with data customized to user’s access privileges.
The data warehouse will be useful for seeing a consolidated view of all tables and columns for all schemas for all business entities within a conglomerate. The data warehouse will be crucial for AI, reporting and master data queries.
If you are interested in checking out the source code for the DW procedure, please click on the following link. www.3d-ess.com/sourcecode (500 Procedures\spsysDatawarehouseCreate.sql )
There is a video to describe this procedure at www.3d-ess.com/videos (020.100 Data warehouse generate).
I hope the source code for this procedure will be of use for you in your next project.
Thank you for your attention. I appreciate your comments, questions and likes.
Note: This post ties together functionality from the last several posts.