4 cases where DB thinking (and a bit of coding) has benefited the oil and gas projects where I‘ve worked.
This was my first post that I originally published on Linkedin, October 2016.
From easy to complex and regardless of the project size, however, engineers, coordinators, administrators, and managers are more frequently facing situations that require solutions demanding data management skills and techniques (i.e. extraction, consolidation, sorting, filtering, securement, distribution, .., etc.).
For example, in oil and gas projects a very common task for the completions group is the extraction of the list of isometrics that are not in any of the test packs provided by the contractor, which is basically a comparison between 2 lists (isometrics and setbacks). Although each completions database administrator can bake their cake their own way (VLOOKUPs, spreadsheets, …etc ), myself, I’ve felt more comfortable creating and customizing my own database applications using MS Access, VBA coding, and SQL as a complement of the completions database systems that I have administrated.
Here below, I’m presenting a glimpse of 4 cases that have optimized my performance as completions database administrator role in different projects:
1.- Mapping and Scoping of tags
Completions and/or turnover groups are typically responsible (sometimes with commissioning team) of the system/subsystem definitions by marking (or coloring) the systems in the PIDs. Some of the specialized software on the market features the automatically “pick up” of tags electronically into a database but, otherwise, it is very likely that completions group has to roll up their sleeves to do it.
If so, most common and tentative approach is by distributing several sets of PIDs/SLDs among the team and enter the tags manually in spreadsheets for a later consolidation then input into the completions database but, experience has shown me the more spreadsheets, the more prompt to errors.
- My approach
I’ve developed the MS Access database application as below:
With this application, I was able to assign PIDs to the various technical assistants (John or Jane) and simultaneously map tags into a common and unique tables. (saved emails too!)
With a bit of VBA code and some queries configuration in MS Access, I was able to produce “clean” data ready for upload/update into the completions database.
2.- Punch list Subcategorization.
Project management team (PMT) had already turned over some systems to commissioning team but, they have many “C” Punch list items. “D” category was meant to be Operations’ responsibility.
Commissioning proposed a plan of using some of the contractors to fix those punch lists and suggested a subcategorization (C1, C2,…, etc.) to identify some priorities inside the C category but without changing the main category. (Remember paraphrasing Forrest Gump:”Projects are like a box of chocolates… you never know what you’re gonna get”). It might also happen that Completions Database system doesn’t allow subcategorization: “a separate tracker was imminent.”
- My approach:
One of the reasons I’ve found MS Access database more suitable for trackers than using a spreadsheet is the automatic per record saving feature which I’m sure it saved me from many headaches.
By using the .csv exporting features (a common feature in most of the completions database systems); I was able to produce reliable and suitable sub-reports and left the “C” category intact in the completions database.
3.- Consolidating Reports
The tracking of check sheets (A, B), punch lists, and discipline acceptance certificates (DACs), SMCC (system mechanical completions certificates) was already in place in the completions database and accurately reported in a general summary.
Then, project managers needed to see the completion status of SQ (site queries), CAR (corrective actions requests) and NCR (non compliance report) added to the general summary. SQ refers to RFI (request for information) in some projects.
A separate group kept the tracking of those variables in a simple spreadsheet.
Then I developed a consolidator database in MS Access:
Consolidation of information from different sources is always a bit stressing when data lacks consistency and integrity because it demands data normalization, manipulation and sometimes a bit of coding but at the end, the work pays off.
4.- What isometrics are not in found in any Test Pack?
As mentioned at the beginning of this post, this is a typical case that I’ve found in almost every project. In one hand you have the list of isos provided by engineering and on the other constructor, the contractor is submitting their ongoing list of test Packs and isometrics.
Project team now needed to identify the isometrics that are not part of any of the contractor test packs, and what type of test (service, pneumatic, hydrostatic, etc.) for the rest.
Running VLOOKUP function wouldn’t be a bad option as long as we keep an eye on the range every time. In practical terms, both isometric and test pack lists are likely to grow in every submission.
Then I configured MS Access database for the comparing, consolidation and reporting as below:
Good data management has allowed me to produce better results in the projects where I’ve worked not only in Completions but, beyond in other areas and disciplines which I will discuss in future articles.
I’d like to conclude this post paraphrasing Gordon Gekko: “Database thinking is right, database thinking works; it clarifies, cuts through, and captures the essence of the evolutionary spirit.”
I’m marking this day of October as my first article-post; I welcome your comments, feedback, and ideas, you can contact me: email@example.com
Eric Ocampo is a Project Management Professional and Microsoft Access Certified Specialist with an Electronic Sciences Degree. He is a proactive Database Administrator/Developer that has worked in Construction, Oil & Gas projects in North and South America, and the Middle East since 2001. He has worked in Mechanical Completions as SCDBA of QEDI and WinPCs systems, and as a Database Developer, he has developed applications for Project Turnover, Completions, Materials, Preservation, Dossier reviews, Weld Tracking, Instrument loops, etc.
His early experience includes a role as MWD (Measure While Drilling) Operator with Anadrill Schlumberger.