Data Integration Solutions - Case Studies

Quintiles

Quintiles are the only fully integrated bio and pharmaceutical services provider offering clinical, commercial, consulting and capital solutions. More than 20,000 Quintiles employees in 60 countries helped develop or commercialize all of 2010’s top 50 best-selling products or compounds.

Quintiles Project Overview

We helped design and develop the SSIS Interface Architecture for one of Quintiles global internal web applications. SQL Server 2005/2008 R2 was used to create a core set of Stored Procedures and the dtexec SSIS command line utility to manage the entire end to end execution of the SSIS Packages. The solution included a complete SSIS run history trail and a centralised exception and error handling process that recorded execution data exceptions and errors. The solution also automatically notified users of any exceptions and errors encountered via e-mail that included a URL link to set parameterised SSRS reports that were used to provide users with details of exceptions and errors.

Working closely with project BA’s and business sponsors, we helped design and develop a bespoke MI SSRS parameterised reporting solution. The reports were used to expand the reporting functionality of one of Quintiles internal web applications and offered employees a rich set of MI business reports that were used extensively for day to day business. Reports included month end reports that were used by Quintiles finance to reconcile timesheet and Adhoc billing costs against the businesses various projects and many of the reports also had to meet stringent SOX compliance rules and regulations.

Working closely with project BA’s and business sponsors, we helped design and develop a custom employee portal. The portal was an ASP.NET web based solution written in VB.NET. The Portal was designed to offer users of an existing internal web application with additional bespoke functionality outside of the core application. One of the key portal applications was the Data Feed Console, which allowed users to manually execute the applications various core SSIS Packages asynchronously on demand. The Data Feed Console utilised a data grid to display the SSIS Interfaces and their run history data and a button to execute each of the Interfaces. An Ajax timer was used to show the execution progress to the user and a Web Service hosted on the web server was used to asynchronously execute the SSIS Interfaces. The Web Service in turn utilised the SSIS Interface Architecture to manage the complete end to end execution of the SSIS Packages. The Data Feed Console also offered users hyper-linked parameter-driven SSRS reports that detailed Interface exceptions and any errors that occurred. SSRS Reports were rendered using Report Server which was embedded in an ASP.NET pop-up window.

EDS (Atlas Consortium)

Electronic Data Systems (EDS), headquartered in Plano, Texas, was established in 1962 by H. Ross Perot. Perot’s goal was to start a company that offered skilled electronic data processing management personnel along with the computer equipment.

EDS Project Overview

We helped design and develop a centralised Microsoft Access reporting data mart. Core data was automatically extracted from the SQL Server 2005 Defect Management Data Warehouse and denormalised and aggregated to improve reporting query performance. Previously, reporting data was stored inefficiently across multiple Microsoft Access databases which seriously impacted performance and made maintenance very difficult.