27 March 2017
Alberta’s Capital Health was a big user of Expedition from the 1990’s through to 2010. They had hundreds of projects that were sitting on a Sybase SQL-Anywhere server that needed to be retired but they needed to have easy access to the documents related to these health care projects.
Emerald solved their problem using the PCM-Archiver. This PCM add-on tool automates the extraction process of all projects and documents, and in this case, ultimately saved the client approximately 140 hours of computing work. For a more in-depth understanding of this project and its results please refer to Part 1 of Saying Goodbye to Expedition 8.5 and Hello to Unifier. In Part 2 we are going to explore a more technical perspective including some of the bumps we experienced along the way of extracting 1,213,318 documents, generating 1,123,318 Excel files, generating links for 39,384 attachments and generating 91,236 PDF files!
When archiving the projects we ran into a few tricky issues, as you would expect with years of data from hundreds of users.
PCM is mostly known today as a web application - when you print a report in version 13, an InfoMaker process is started to generated the PDF with the report contents. In version 14, a BI-Publisher call is made to generate the report. The PCM-Archiver can connect to both the InfoMaker process or BI-Publisher, and it all works because these applications were built with automation in mind - there are no interactive dialogs displayed where the user needs to get involved. Everything is automated.
However, Expedition 8.5 was not a web application and of course that means older technology.
Now, back then, web applications were not as popular as they are now. Expedition 8.5 and older expects a user to be working with it at all times. So, when you print, a dialog is displayed with printing options. Suppose you want to print to a PDF - there will be another dialog to enter the PDF filename, and then the user needs to enter the destination filename and press the OK button to actually save the file.
That is perfectly fine if you want to print one document, or 5, or 10... But we needed to generate the PDF's for over a million documents, and clicking around wasn't an option.
With all our automation tools, we were already very familiar with AutoHotKey, and have dynamically generated scripts that recognize the Expedition printing dialog and automate the clicking. After that, there would be another dialog for the PDF printer, and, again, the script would recognize that dialog, enter the PDF file name, and emulate the user clicking the print button. A few timeouts were added here and there to ensure no resources were left hanging, and it worked like a charm.
Other than that, we had some minor issues that we were able to work around - the tree structure in Expedition 8.5 is archived in a different format than PCM 13 & PCM 14, we had to do some wizardry to understand how the printing process worked in Expedition 8.5, what files were copied where, etc..., and we also had to automate the report generation, since we were comparing literally thousands of documents and dozens of document types, and the only way to properly compare all that was with automation and with proper summaries.
In the end, we were able to overcome all challenges and the process worked very well.
After we had done some testing and worked out all the technical hurdles we then had fun watching the PCM-Archiver do its thing. Of course, we did not watch it exactly, because that would have been like watching paint dry. It took 6 days of a very busy VM (140 straight hours) extracted 1,213,318 documents, generated 91,236 PDFs, 39,384 attachments and 389 ZIP files. 6 days later all files were generated and AHS was able to move forward and retire Expedition after 20 years of great service.
As our VP Solutions and a Lead Risk and Implementation Specialist, Ian leads Emerald’s functional consulting group. With over 20 years of international experience in varied fields and roles from manufacturing, heavy civil construction, pharmaceutical plant construction, hospital projects and oil and gas capital and turnaround projects, Ian brings a wealth of project knowledge to all of our clients.
A visionary in the world of CAPEX, maintenance and turnaround planning processes, Ian has lead many of our large clients through their integration projects between ERP/EAM systems and Primavera products. Some of his integration success stories include Suncor Energy SAP to Primavera integration, BP Maximo to P6 integration, implementation of P6 at the Ontario Power Authority as well as the integration of Primavera Contract Manager with Oracle Financials at Capital Health Authority and Vancouver’s Rapid Transit Project 2000. Other major clients include Milwaukee Metropolitan Sewerage District, Shell Canada and Shell Global Solutions.
Ian has conducted Monte-Carlo risk analysis on CAPEX and turnaround projects for Shell Canada, Suncor Energy, Husky Energy and Bruce Power. He believes that successful Monte Carlo application is a process, not just a tool and has spoken at a number of events on the correct application of risk analysis.
When not assisting clients with their projects, Ian unwinds by riding his BMW motorcycle, listening to music or dragging his kids on long hikes.
Ravi started working with computers as a child, programming with BASIC language on his MSX, a platform once popular outside of North America. In 1998, Ravi started an internship where he further developed his programming skills. After one year, this internship became his first job, where he gained experience developing industrial automation systems.
Over time, Ravi has worked in many different industries and programming languages, developing banking, integration and mobile solutions in C/C++, Java, Visual Basic and C # for a variety of platforms.
From 2007 to 2011, Ravi moved from Brazil, his native country, to Canada with the desire to live and learn another culture, as well as gain experience working in diverse environments.
Since 2009, Ravi has been an important part of the team at Emerald Associates. He has participated in key integration projects with major clients, such as CL&P, Suncor and Nexen, acting as the key technical lead in all phases of the projects. Ravi is also a key contributor at Emerald Associates in the development of unique in-house products such as TAPS, EP-dashboard, CAPPS, PCM-Loader and P6-Loader.
Ravi is a dedicated and insightful developer, one who prides himself on the quality of his work. He truly strives to provide optimal functionality so that each piece of a complex integration system fits together to give the end-user what they really need and more.