As a result of the work undertaken with the national Regional Development Agencies, Amjec were invited to work with the London Development Agency on their Portfolio Director implementation project.

This involved the working closely with the LDA project team in:

• analysis of LDA's existing business processes
• analysis of Portfolio Director's configuration
• gap analysis of the differences
• determining which gaps represented changes required to LDA business processes
• determining which gaps represented changes required to Portfolio Director
• documenting changes to business processes for LDA team to progress with business areas
• preparation of business and system requirement documents for "changes" to Portfolio Director (in reality, changes were implemented in extensions to the product, not as changes to the core product itself)
• testing of changes
• configuration of Portfolio Director and its extensions

One of the requirements / extensions delivered by Artemis was a financial import, to allow finance data to be imported from a financial system into Portfolio Director. This was delivered as a bulk insert with a java wrapper, which took a strictly formatted text file and inserted the data into the finance tables.

Amjec was responsible for the development and delivery of an overnight batch process which extracted lowest level transactional records from their finance system (CFACS), applied several transformations, aggregated the transactional records into a variety of groupings, and then loaded the transformed data into a file that exactly matched the input file for the Artemis financial import utility.

This was developed and delivered as a series of Microsoft SQL Server Data Transformation Service (DTS) packages, which:

• backed up the appropriate databases before changes
• FTPd the low level transactional data from the company that managed LDA's finance package
• extracted the transactional data
• did an element of error checking and reporting on the transactional data
• performed a complex series of transformations to aggregate this low level data into higher level monthly records, by different criteria, such as Capital or Revenue, or income or expenditure, and other LDA specific groupings
• created a text file exactly to the specifications required by the Artemis financial import extension
• ran the import extension
• moved the error report to a network share so the LDA finance team could investigate any errors
• ran some housekeeping and audit routines, such as copying the input transactional file, and output file with time and date stamps to a folder on the LAN so each days run could be fully audited
• several key steps where also systemically monitored, and any failures of those steps would generate emails to the development and support teams notifying them of the stage that had failed, likely reasons for failure, possible fixes and the impact of the failure upon the business

Considerable reconciliation work also had to be done by Amjec, the LDA project team and LDA financial team to prove the correct transformation of data.

Some time after the implementation of Portfolio Director, LDA moved away from a managed financial package, purchased Open Accounts and moved their accounting in house.

Amjec worked closely with the Open Accounts project team to ensure that the move to this package could be assimilated into the Portfolio Director overnight batch process, and provide the same financial data. This required major changes to the extraction and transformation sections of the processing to ensure the same output file was presented to the Artemis financial import extension.

Again, considerable reconciliation work was then carried out to ensure the new financial data was being loaded with the same degree of accuracy.

Amjec also worked closely with the LDA project team to implement a data warehouse and reporting portal. LDA conducted their usual tendering and review process for a reporting solution, and Intelliview, from Synaptris, was chosen.

This package comprises of a server based web portal and a (free) workstation client.

We worked with the LDA project team to implement:

• A datamart / data warehouse that extracted data from the Portfolio Director database and created denormalised tables of project data
• A web portal to access reports, featuring full access control
• Updated overnight batch process to backup and update both the product and development datamart databases automatically

We also worked with the reporting team to provide many (in excess of one hundred) SQL Server views to support a wide ranging variety of reports. The reporting team would process requests from the business areas for reports, and then approach us to provide the data. We would then develop view(s) to support the data requirements of the report, and go through a formal test and sign off of each view.

We also administered the web portal. After each report was tested and signed off, the reporting team would need to come to us to have the report

• published to the web portal
• moved into the appropriate menus
• have the appropriate level of access changed so that only the appropriate users could use the report