McKesson Corporation | Generics Database & Analytics Upgrade
INDUSTRY:
Pharmaceutical, Supply Chain
PRODUCTS:
- SQL Server 2016
- Tabular Model (SSAS)
- SSRS Analytics
- SQL Server Reporting
COMPANY SIZE:
76,000+ Employees
OPERATING BUDGET:
$198.5 Billion (FY2017)
HEADQUARTERS:
San Francisco, CA
PROJECT OUTCOME:
- Significant increase in server storage capacity & performance
- User-friendly report portal with a metadata database that allows users to easily search for reports
- Sucessful implemenation of the Tabular Model that allows users to access three years’ worth of data
OUR CLIENT’S CHALLENGE
Ten2Eleven partnered with McKesson’s IT Department to upgrade the data warehouse and analytics platform used by its US Pharma Generics division. Prior to the project, the Generics team operated its Analytics, Reporting, and Applications functions on a single host with inadequate resources. Additionally, the business lacked a solution for self-service, interactive reporting, and had difficulty managing its inventory of several thousand reports using the out-of-the-box Microsoft portal interface
TEN2ELEVEN’S APPROACH
Ten2Eleven separated the three business functions onto two servers: one for analytics and reporting, and the other for decision support applications. To address reporting concerns, Ten2Eleven created a new reporting portal that allows users to search and monitor report inventory and identify redundant or obsolete reports. Additionally, Ten2Eleven built a self-service analytics tool using the in-memory processing capabilities of SQL Server 2016. This tool allows users to perform analyses on hundreds of millions of rows of data in seconds, as opposed to the minutes (and sometimes hours) it took previously.
Another aspect of the project was the migration of the database servers from physical to virtual hosts. Ten2Eleven architects provided oversight to the performance validation and capacity planning activities for these migrations. Ten2Eleven’s development team configured MS SQL and SSAS instances and configured linked servers and related security.
THE SOLUTION
Ten2Eleven partnered with the McKesson IT team to develop the infrastructure for new application and analytics server platforms, splitting two business entities into two servers.
- Configure MS SQL and SSAS instances, set up linked servers with permissions, verify data integrations
- Configure SQL Server for Reporting Services (SSRS), assist with copying over current reporting to new server
- Develop and manage the process to retire databases from old server
- Develop a new Reporting Portal, including new UI for SSRS end users. T2E provided training documentation for the Portal.
- Clean up and optimize by identifying application code modules with dependencies and relocating databases, develop or modify scripts to re-point existing data sources to new server instances
- Develop a new Reporting Portal, including new UI for SSRS end users. T2E provided training documentation for the Portal.