December 2003 - June 2008
Starting in April 2003, I worked on a project providing support to a large SAP implementation for five years. This contract came to a close in April of 2008.
This project started as a Lotus Notes application development. Lotus Notes databases were being used to manage the project. Shortly after starting this project, it became more than just a Lotus Notes application development project.
One of my first projects here was to develop a web-based "Recognition" application. This application is a Domino application delivered entirely via the web.
This application allows the team members on the project to go to a web page and create certificates for other team members to say thanks, job well done, etc.. It allows the user to control the message that is displayed on the certificate and allows the user to preview and refine the certificate. Once the user is happy with the certificate, they publish the certificate. Upon publication, the recipient is sent an e-mail message with a link to their certificate with instructions on how to print the certificate. Each certificate is also posted to a "bulletin board" where everyone on the project can see certificates.
One cool feature of this application is the web based address book selection dialogs that allow single or multiple names to be selected from the address book.
Another project that I worked on was a Deliverables & Status Report application. This was a project that continued to expand and grow as the project grew.
In this application, track level monthly reports "roll" to sector level monthly reports (one item on a track report may appear on multiple sector reports). The reporting was divided into a different categories: Benefits, Milestones, Key Performance Indicators, Headcount, etc. and the specific line elements were data driven allowing them to be easily changed by the business from year-to-year.
This as a Domino application using a SQL Server Database for data storage. SQL Server was chosen because of the relational nature of the data involved, although it did allow attachments which were stored in the Domino database. To produce nice output, ASP's are used with Crystal Reports ActiveX viewer to display dynamically generated reports.
Another project that I worked on was designing and developing a way to link documents between multiple Lotus Notes Databases in a manner that would support reporting.
The existing method of creating links via Notes was to past document links into rich text fields. This had a few problems which we addressed; the biggest issues were that the links were one-way, and there was no way to build a report to show which documents were linked.
We built a flexible system that allows control of what type of documents could be linked to each other. We made the links bi-directional (i.e., if you link Document A to Document B, the Document B is automatically linked to Document A). The method used in the implementation also supports reporting, we can use the link definitions in reports to "join" related documents. Minimal information is stored on the link document, just document type, document "ID" (a unique key that we maintained in code), and a description. The links are stored as notes documents in the same database as the document being linked and are displayed to the users with an embedded view.
The code to create, update and remove links was encapsulated in a class which was stored in a script library that was shared among several databases. This provided the developers a simple and consistent method for working with the links with a minimal amount of code.
Another project that I worked on was the development of a method to uniquely (and sequentially) number all of the documents in the Notes Databases.
This is a classic problem faced by Notes developers. I solved this problem by developing a system that uses a locking mechanism and a centralized number holding database so that numbers are not duplicated even across replicas. It had many fall-back options for times when it took too long to get a number or if the centralized number holding database was not available.
Exploit MS Project Data model
Generating reports that used data from the project plans that they were using to manage the project was a non trivial task. The MS Project Data Model is a complex to allow for user customization.
We created a number of views to let us see to this data in a "normalized" fashion so we could use Crystal Reports to generate a number of reports.
While Crystal Reports solved many of our reporting needs, we were unable to get the charts that we wanted by using Crystal Reports. We solved this problem by using MS Excel to query the views that we created, then we used VB Script to automate the generation of charts which we presented in MS PowerPoint (using links).
While this project does not sound very exciting, giving the business users the ability to generate comprehensive reports on their progress across multiple project plans provided tremendous value to them.
Key Performance Indicator Data Processing
Another project that I worked on was the development of a process to take Key Performance Indicator Data and present it in a simple understandable report format. The initial data was flat-file dumps from legacy systems as well as the new SAP system.
This data was importing into SQL Server where several DTS packages were created to extract the vital bits of information from this data which were necessary for the reporting.
The end presentation used Microsoft Excel to query SQL Server views to generate tables and charts, resulting in a one-page "scorecard" for each business.
I also created a system that would consolidate all of the e-mail notifications that were generated by the various applications used on the project into a single daily e-mail message. I created a notification class that allowed developers to send notifications with only a few lines of code. The notifications were then consolidated and collected into a single e-mail for each recipient. In the consolidation process, multiple messages about a particular document were reduced to a single line.
Lotus Notes to SQL Server Near Real Time Extract
With much of the reporting being done with Crystal Reports, it was vital to have much of the data from the Notes applications extracted to a place where Crystal Reports could easily access them. The customer decided to not use LEI or some tool to perform this job, so custom code was written. We originally wrote the code using Lotus Script and the LCLSX to extract the Notes data to SQL Server.
We ran into some issues with this approach such as the agent took too long to run and could not be scheduled quickly enough to keep the data in SQL server as up-to-date as the users would like. Our solution turned to a java application. The java application would scan approximately 75 views and make necessary updates to corresponding SQL tables. Depending on the number of updates, the application could scan all 75 views in as little as 5 seconds. We had the application running in a loop with a 20 second pause between runs and an overnight break to process documents that had been deleted.