25 February 2008

Real World Access (35)

One of a series of articles about where Microsoft Office Access applications have found a real-world niche.

 Graham Seach's Investment Banking

Many of the Access projects I develop are for very large organisations. One in particular was for a multi-national investment bank.

This $3.9M project was to develop a highly secure enterprise system that allowed the bank to meet its regulatory reporting requirements under both the BASEL I & II accords (similar to Sarbanes Oxley).

This project employed a Project Manager, a Systems Architect, 4 Access developers, a SQL Server DBA, 2 Business Analysts, 2 DataStage developers (data migration), 2 MicroStrategy developers (reports), 2 Test Engineers, plus team managers.

The long-term plan for the project was to create an enterprise-wide intranet solution in DotNet. Access 2OO3 was selected as the Version 1.0 frontend technology due to its flexibility, power, and its RAD (rapid application development) capability.

The SQL Server 2005 data store was designed as a data warehouse, so the front end had to be designed to cater for huge numbers of records (in excess of 100 million).

The Access application provided users with the ability to manage and analyse deals and exposures, capital deductions, limits, on/off balance sheets and collateral allocations, and to reconcile the GL (general ledger).

The Access application's user interface is fast, responsive and extremely stable, having been designed using the principles of human-computer interaction (HCI). The UI design focus is on usability and employs a direct manipulation model.

The system uses techniques not normally found in typical Access databases, for example:

  • Extensibility (user customisation)
  • Proactive integrity checking (the system actively prevents users from making mistakes rather than just responding to them)
  • Screen update delay minimisation techniques
  • Screen fading effects
  • Graphical tooltips
  • Full drag-n-drop facility throughout
  • Dynamic list scrolling during drag operations
  • Automatic user shutdown/lockout during data load operations
  • Ad-hoc environment switching (using XML configuration files)
  • User-defined security model (including screen/action permissions) tightly integrated with SQL Server security.

The project was delivered on-time, despite a highly aggressive schedule.

The intranet version has been delayed because the Access version works so well that it has quickly become the line-of-business application. Management now see no reason to replace Access.


At 29 February 2008 2:45 AM, Blogger grovelli said...

Is it possible to know what tools have been used to carry out the Ad-hoc environment switching using XML configuration files?

At 16 August 2008 1:10 AM, Anonymous Anonymous said...

The total system involved five identical backend databases across 3 separate servers; one for ETL (data upload), one for development, one for SIT (system integration testing), one for UAT (user accepance testing), and of course one for production.

The XML file contained the connection strings for all these environments, and was located in a secure server share. Adding or removing connection strings from this file changed the environments users could see in the UI.

Depending on the environment the user was currently in, the UI displayed menu items to facilitate environment switching.

On selecting an environment, the system retrieved the connection string for the selected environment, and relinked the tables and pass-through queries, and changed the menus accordingly.

From the Options dialog, an authorised user could select the environment the system should be in the next time the application was launched.

Of course, in the production system, this menu was invisible, therby preventing those users from switching environments.

All pretty simple really, but it served our purposes of being able to test features that were only available in certain environments, and to deliver an environment-specific version for selected environments.

- Graham R Seach


Post a Comment

Links to this post:

Create a Link

<< Home