Many of the pricing applications we have implemented are country-based, but are operated at regional or global level. One of the challenges this presented was the requirement to consolidate all controls and auditing processes to a regional or global level, ensuring a single user might review and control a whole region without omitting any countries. This requirement was particularly related to audit data and thus required a high level of control/integrity.

We designed and delivered a process to be driven off the system audit triggers in individual countries, which would consolidate the audit data in near real-time mode and provide a single point of review. This subsequently delivered additional benefits relating to paperless reviews, electronic signing, single process, etc.

Our method used to perform this consolidation process was to use an asynchronous almost-real-time data collection model using the SQL Server XML generation tools and SQL Server Service Broker. Changes to key data are converted to XML at the point of data modification in each country system, and this XML is posted to a Service Broker queue for dispatch to a consolidation server, where the information is consolidated into a single system.

An ASP.NET web front end was provided to allow the user to report on the accumulated data, both in a structured and in an ad hoc manner. Because this consolidation system is not limited to receiving data from a known set of applications, the reporting front end is constructed programmatically from the schema meta-data stored in the database.