top of page

Innovative Master Data Query Engine

In this post, I am going to describe the Master Data Query utility that was developed using Postgres which is a part of an enterprise system platform that has an inherent capability to exchange data between systems.


The Master Data Query engine gives you a complete picture of any master record. It not only returns the data about the master record itself but drills down to child, grandchild data, and beyond.


For example, in a government system it could return the following data about a land parcel based on its legal land description, limits results to three levels deep, and return the data that was effective on May 31, 2023.


1. Details about the Land Parcel as of May 31, 2023:

1.1 Title holders of the land parcel as of May 31, 2023

1.1.1 Details about title holders

1.2 Oil wells located on the land parcel as of May 31, 2023

1.2.1 Revenue journal entries for the oil well

1.2.2 Production data for the oil well

1.3 Encumbrances registered against the land parcel as of May 31, 2023

1.3.1 Details about the holders of the encumbrances


The data returned for each entity is based on views automatically generated by the system based on the data dictionary.


Considering the procedure's power, flexibility, and usefulness, it is created with a small amount of code. There is a procedure to generate the master data index which is 164 lines of code and a function that returns the select statements for the data which is 320 lines of code.


https://github.com/bkjenner/3dEnterpriseSystems/blob/main/500%20Procedures/spsysMasterDataIndexGenerate.sql


https://github.com/bkjenner/3dEnterpriseSystems/blob/main/200%20Functions/fnsysMasterDataQuery.sql


The Master Data Query procedure is possible as a result of all of the critical components of the 3dES platform such as primary keys, data dictionary,


If you are interested in learning more about how the Master Data Query Procedure works, please check out www.3d-ess/videos (020.110 Master Data Query)


Thank you for your attention. I appreciate your likes, questions, and comments.

5 views0 comments

Recent Posts

See All

Recursive selects to rollup financial data

In this post I am going to describe the financial roll-up procedure that was developed using Postgres that is a part of an enterprise system platform that has an inherent capability to exchange data b

Need help creating an application framework

In this post, I have a #QuestionForGroup about creating an application framework to connect to the open-source platform I created using Postgres. The platform allows us to create enterprise systems th

Comments


bottom of page