1. Introduction

The Crossrail project involved hundreds of contractors and sub-contractors – each having their own working procedures and Electronic Data Management Systems (EDMS). The ability to interface with other business systems was therefore critical to Crossrail. Contractors had to manage documents and communications with the client Crossrail on the Crossrail EDMS (Bentley Enterprise Bridge – ‘eB’) and mirror everything on their internal system.
This created “Big Data” in the form of thousands of documents, drawings and communications that were generated on eB for each contract which made it impossible to substantiate lists manually.
In order to deal with substantial amounts of data from various sources the document control team had to devise a way to manage multiple EDMS platforms.

2. Managing Documents on Two EDMS Platforms: Business Collaborator (BC, contractors’ EDMS) and eB (the Crossrail EDMS)

There were various EDMS platforms used by contractors on Crossrail project. For the purpose of this paper, Group BC, Business Collaborator (BC) is the example used.
Whilst not all contractor documents were required to be uploaded to the client EDMS; it was critical that all deliverables on the client EDMS were stored by the contractor as backup assurance. The list of deliverables was agreed between contractor and client in the original Works Information document. In this example, the contractor document controller had to keep track of the two databases and make sure that everything on eB was reflected in BC.

2.1 Determining which documents are held – run a Validation Report in MS Excel

Most EDMS’ have reporting functionality, but to manipulate the data, it has to be exported to MS Excel.
Crossrail was able to export all documents by contract using the eB Lifecycle Tracker report. The report provided 66 fields of metadata on one MS Excel worksheet; however only five fields were necessary to carry out a gap analysis. The key data was in these fields:

o eB Doc Owner
o eB Document Number
o Revision
o Review and Acceptance Status
o Title

By Comparison a BC user-defined report exported data for gap analysis included

o BC Doc Owner
o BC ID Reference
o Document Number
o Revision
o Title

The following fields were manually inserted:

o EDMS hyperlinks (for document access);
o Searched references (eB against BC) (See figure 1).

Figure. 1 – Columns for each worksheet

Data from both reports was consolidated to one MS Excel workbook (but on two separate worksheets; named eB and BC).

Tip: The gap analysis/comparison will not work if documents are inconsistently named and numbered across both systems.

2.2 Determining differences between two data sets – perform Gap Analysis

Before submitting documents to the Client, documents had to be reviewed and accepted internally by the relevant contractor team.
Contractors’ document controllers were querying Contractors’ document owners if the document found on BC, (but not found on eB), was ready to be uploaded and issued to the Client. Some documents were not ready to be uploaded to eB, for example – not signed, not finalised, etc. and therefore would be excluded from task 2.3.
Thousands of documents with multiple revisions had to be matched across the two data sets. The two variables to match were ‘Document Number’ and ‘Document Revision’. The detail of how the comparison was done is shown in the “Gap Analysis” supporting document.  Doing this allowed us to identify where the differences existed on the EDMS platforms and then produce a list of files that needed to be uploaded to each database.

2.3 Gaps identified – uploading missing documents

Identified missing documents then needed to be downloaded from one system to another. Creating hyperlinks to documents gave quick access to files in each EDMS.
EDMS platforms provide direct hyperlinks to documents and files; generally there is a consistent pattern. The HYPERLINK function and VBA codes demonstrated in the “Hyperlink” supporting document were used to simplify the process.

2.4 Creating Assurance Reports

When reporting to different recipients it was necessary to split the data. This enabled us to send only relevant data to the appropriate team and ease the reading of reports. Working with Big Data can be confusing and time-consuming task for inexperienced MS Excel users. Splitting the data eliminated the filtering and/or deleting of data from the master spreadsheet which could lead to removing significant information, and it reduced the file size for each recipient.
Contractors’ document owners received ‘Review and Acceptance Status’ of their documents on eB and were queried if database entries that were found on BC were deliverable and ready to be uploaded to Clients EDMS.
Clients’ document owners were assured that documents were held by the Contractor and backed-up in their EDMS.
The detail of how data was split is shown in the “SplitSheet” document.
Mail Merge Wizard on MS Word was then used to create multiple e-mails with same subject line and content for quick and easy report distribution.

Figure 2 – “Send immediately” on MS Outlook advanced options

Before finishing the last step of the Mail Merge on MS Word, untick ‘Send immediately when connected’ and edit send/receive time on MS Outlook advanced options (Fig 2). This will send emails from Mail Merge to ‘Outbox’ folder before distribution. Drag and drop them to ‘Drafts’ folder. This will allow editing emails and attaching different files before sending.

3. Summary

Validating Big Data for any business; requires effective and time-saving data-manipulation tools.
For the Crossrail project; effectively managing the vast quantities of EDMS-generated data allowed for:

o Contractors (information owners) to monitor and progress any incomplete work.

o The Client to ensure completeness/delivery of contract information in line with the Works Information

o Stakeholder assurance that all requirements had been met.

4. Lessons Learned

This paper has identified the key elements that need to be tracked and the solutions that were found to make this process more manageable.
The MS Excel solutions described in this paper, and detailed in the supporting documentation, provide an automated approach and the assurance required for the project documentation. These proved to be valuable on a large scale project for validating big data, however may be easily adapted for other industries outside of rail construction.