Customer Solution Case Study
/ Aflac Speeds Queries by up to 95 Percent and Compresses Data with Data Warehouse
“With SQL Server Fast Track Data Warehouse, we’ve realized up to a 95 percent reduction in data query times.”
Eric Hunter, Senior Manager of Business Intelligence and Marketing, Aflac
Aflac needed to update its data warehouse to improve performance, enhance data availability, and accommodate change. To achieve this, the company implemented Microsoft SQL Server Fast Track Data Warehouse built on the HP technology platform. With its solution, Aflac can speed system queries by as much as 95 percent, refresh data in the warehouse two to three hours faster, compress data to occupy up to 10 times less space, and increase business agility.
This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.Document published July 2010
Business Needs
Founded in 1955 by three brothers, Aflac is a Fortune 500 insurance company with 4,200 full-time employees and 70,000 independent associates. Like most large insurers, the company relies heavily on its mainframe in servicing customers and associates. To provide employees with information about policies, groups, and policyholders without slowing mainframe performance, the company ran about 85,000 reports each month against a data warehouse. Supported by Microsoft SQL Server 2005 data management software and the Windows Server 2003 operating system, the data warehousecontained 2.5 terabytes of information. To ensure that data was consistent between the mainframe and the data warehouse, each night an application ran batch processes to extract, transform, and load (ETL) new information into the warehouse.
By September 2009, rapid corporate growth had pushed the data warehouse to capacity. Securing additional space for the business data on the enterprise storage area network (SAN) became an ongoing and costly challenge. In addition, ETL batch cycles that were supposed to be complete by 6:00 A.M. frequently ran past 6:30 A.M. Report queries also took longer to complete. “Even if employees had to wait one minute for a larger report, that’s one minute that they had to stop their momentum and either wait or switch to another task,” says Eric Hunter, Senior Manager of Business Intelligence and Marketing at Aflac.
IT personnelalso needed to add sales commissions data and other domains to the data warehouse. Because this information only resided on the mainframe, two IT employees had to generate about 15 custom reports each month for employees who could not access the data themselves.
By adding this information, the business intelligence team forecasted data growth to potentially double or triple within one year. Aflac decided to implement a new data warehouse tosupport its expansion, speed performance, boost data availability, and increase access to additional domains.
Solution
Aflac evaluated solutions from vendors but ultimately chose to deploy Microsoft SQL Server Fast Track Data Warehouse. With it, Aflac could use pretested reference architectures, best practices, and familiar technologies. In addition, Hunter says, “Microsoft was willing to invest significant time and diligence in the planning process.”
Between January and March 2010, IT employees responsible for databases, storage, and business intelligence designed the new solution. This process included a trip to the Microsoft Technology Center in Atlanta, Georgia. “Working with the Microsoft Technology Center was a significant accelerator for us,” says Hunter. “We talked through everyone’s concerns and gained a collective philosophy.”
The hardware infrastructure for the solution includes a central HP ProLiant DL585 server computer with four, six-core AMD processors and 128 gigabytes of memory. This system runs Windows Server 2008 R2 Enterprise and Microsoft SQL Server 2008 Enterprise. In addition, a dedicated SAN—built with four Hitachi Adaptable Modular Storage 2100 storage systems—supports the new data warehouse while also providing room for growth.
To facilitate a seamless migration, Aflac developers built a data migration tool using the Microsoft .NET Framework 3.5 and the Microsoft Visual Studio 2008 Professional Edition development system. The tool uses dynamic-link libraries in Windows Server 2008 R2 and multithreaded processes to move data. To boost performance, IT employees also modified the ETL batch processes so that SQL Server 2008 Integration Services manages more of the workload.
Over the first weekend in April 2010, Aflac migrated the data from the old warehouse to the new one. Since then, IT employees have taken advantage of new capabilities such as data compression. They arealso planning to incorporate new information such as sales commission datainto the warehouse. Aflac is also enhancingits core sales and reporting tool by usingnew features within Microsoft Visual Studio 2010 Professional and SQL Server 2008 R2 Reporting Services.
Benefits
With its new Microsoft solution, Aflac can speed system queries and ETL batch cycles, shrink its data footprint, and improve agility.
Accelerates Queries by up to 95 Percent
Today, employees can work more efficiently. “With SQL Server Fast Track Data Warehouse, we’ve realized up to a 95 percent reduction in data query times,” says Hunter. Aflac also expects IT productivity to increase. “Once we move all the sales commission data into our new warehouse, we expect to eliminate a significant portion of our custom report requests.”
Shortens ETL Batch Cycles by Two to Three Hours
ETL batch cycles take less time to complete than before, which boosts both data availability and service levels. “Today, the batch cycles are done between 3:30 and 4:30 A.M.—two to three hours faster than the previous solution,” notes Hunter.
Compresses Data to Occupy up to 10 Times Less Space
Aflac is now better positioned to manage its annual data growth rate. “The data compression capabilities in SQL Server 2008 were an unexpected bonus,” says Hunter. “On average, we can compress data by 2–3 times—and in some cases, we can compress it by 10 times.”
Improves Business Agility
With its new enterprise data warehouse, Aflac can accommodate changing business requirements. The new sales and reporting tool—projected for release later this year—will also provide key enhancements that can increase business value and deliver information in a format that is easier to read and more secure. “Our new reporting tool can make presentation decisions based on the users’ display to provide an improved and more tailored user experience,” says Hunter. “With SQL Server 2008 R2 Reporting Services, we can also use conditional statements to modify how a report displays based on data type, report rendering format, and security requirements.”
This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.Document published July 2010