A Server that Can Go the Distance

To achieve the kind of speed ACNielsen needed, MarketVu II had to provide: 1) storage for vast amounts of data from heterogeneous sources; 2) a flexible database to accommodate various study configurations; 3) the ability to offload batch processes to remote servers to free up client machines; and, 4) automation of repetitive tasks. Just as critical was flexibility: MarketVu II had to be capable of evolving as ACNielsen's business needs changed. G. A. Sullivan recommended a multi-tier architecture. Microsoft SQL Server 7.0 was the obvious choice because of its easy administration, powerful tools and capacity to integrate seamlessly with Microsoft OLAP Server.
To streamline data processing, G. A. Sullivan developed stored procedures to retrieve data based on user-submitted queries. Optimizing them was simple with the power of SQL Server 7.0: the system graphically displays the query execution plan and provides an easy-to-understand view of which indices have been used. The built-in Index Tuning Wizard provides in-depth analysis of indices used for a given workload, even suggesting new indices that can increase performance. In addition, Microsoft SQL Server 7.0 makes database administration easy: built-in wizards schedule routine back-ups and optimize running and maintenance tasks. This advantage became clear as the number of study databases grew.
"The process to import data into our databases used to be a very time consuming, repetitive task and it tied up the person's computer while it was running," Dittrich says. "Now it runs on an application server and is initiated by a simple wizard process. This has saved tremendous time and a priceless amount of frustration for our users."
G. A. Sullivan used Microsoft Transaction Server (MTS) to provide MarketVu II with central business rule management and scalable access to Microsoft SQL Server 7.0 databases. The MTS middle-tier components (Microsoft Visual Basic® 6.0) developed by G. A. Sullivan experts perform business-critical functions to support MarketVu II's data editing capabilities. Components use Active Data Objects (ADO) to communicate with SQL Server. Now, disconnected ADO record-set objects pass query results to the client without maintaining the database connection, thus providing an efficient means of marshaling changed records to middle-tier storage. Microsoft Transaction Server's Shared Property Manager feature also caches frequently accessed data on the middle-tier server. This reduces repetitive querying against SQL Server, increasing scalability and improving response time. Updating is easy: since business rules were implemented in middle-tier components, one only has to replace an MTS component to change the way MarketVu II operates.
To succeed, MarketVu II also had to have a user interface that could support users' advanced editing needs, and allow for integration with off-the-shelf software such as Microsoft Office. G. A. Sullivan selected the powerful Visual Basic 6.0 development system, with its extensive debugging capabilities, as the development tool for MarketVu II's user interface. The cross-debugging feature allows developers to debug several projects at once, stepping into each component's project as it is called. Most of the client-visible code was encapsulated in Microsoft ActiveX® components to provide maximum reusability. By referencing these components, custom Microsoft Office applications, written using Visual Basic for Applications, can use the same code as MarketVu II, making it easy to communicate with middle-tier components.