Integration with existing corporate systems - AS/400.

The primary challenge was to integrate with the customer’s existing ERP system. Because the two systems were hosted on different environments this was done via file transfer, which would run every minute to deliver almost-real-time data between the two systems. No end-to-end scheduling tool was available within the client’s organisation, and so the interfaces were implemented as a file sweep using SQL Server Agent. Because of the frequency of the interface runs, and the real-time nature of the data they contained, the emphasis was on robust and efficient interfaces. We were able to use our experience of optimising schema and query design to implement interfaces that ran sub-second, while still making the imported data immediately available to end-users in the online systems. We liaised with the local IT department to specify and develop these interfaces to a very tight project timeline.

By exposing data from their existing ERP system, we provided the users with improved reporting capabilities.

The integration also allowed us to leverage pre-existing communication to the customer via both SMS and direct data transfer to and from the point-of-sale system. The point-of-sale end-to-end communication enabled price decisions made at head office to be implemented automatically on the site, and for confirmation of this to be received back, typically within two minutes.

Google integration

Google Earth was used as an aid to data-visualisation in a rapidly changing market. Being able to see price movements within a geographical context is a great aid to making pricing decisions. We were able to provide dynamic content within Google Earth by developing a C# .NET Web Service which would source data from the SQL Server database and serve it up as KML to Google Earth. This means that the maps always show the most up-to-date information.

WOM

Customer web access to view and enter data into the database was provided via an ASP.NET website which was integrated into the client’s existing portal environment, hosted on a Domino server. This involved developing a custom authentication model for communication between the two environments.

IVR

In addition to web access, customers were provided with a telephone-based communication module, or IVR, which they could use to interact with the system. The interaction is via the pressing of buttons on the telephone keypad, and listening to an automated voice. The IVR is a C++ Windows Service which utilises a Dialogic Service and hardware in order to connect to the client’s PABX, and from there make external calls. The IVR has the capability both to receive and to initiate calls with the customer. It also includes a management module, which allows the phrases spoken by the automated voice to be re-recorded, and thus tailored to the particular business and legal requirements.

SMS

The system included the capability to notify customers of price changes via SMS. This was implemented by leveraging the existing SMS functionality provided via the ERPsystem by sending the text of the messages in an interface file.

Ad-hoc and BI reporting

In order to provide the user with fully up-to-date ad hoc reporting, we implemented an almost real-time data warehouse, from which the user can request data which is prepared and delivered to them in the warehouse in less than two minutes.

In contrast to traditional data warehouses, where the data is built overnight, we were able to use Analysis Services in conjunction with some innovative SQL for extracting the data to bring the cube build times down to a level where the user was able to report on data within the current day. The user is able to configure for themselves a number of aspects of the cube design:

• which “measures” are available within the cube;

• the periodicity of the time dimension (hourly, quarter-hourly, five-minutely etc);

• whether the cube represents the whole country or only a specified part.

All of this allows the user to tailor the cube to his or her specific requirements. The implementation and deployment of these flexible cubes was facilitated by use of the XL3 Workbench application, which was used for the configuration of the cubes and the scheduling of the cube builds.