.png)
Microsoft Cuts Costs and Improves Access to Information with Enhanced Data Warehouse
Business Case Study
Published: December 2011
To improve management of human resources information, Microsoft needed to accelerate query performance and ease maintenance. To accomplish these goals, the company deployed its new data warehouse technology. The solution tripled performance while simplifying management, and both users and engineers are more productive. As a result, the company expects to cut costs and drive user adoption.
|
Download |
|---|
|
|
|
Customer Profile |
Situation |
Solution |
Technologies |
|---|---|---|---|
|
Based in Redmond, Washington, Microsoft is a global leader in software, services, and Internet technologies for personal and business computing. |
To improve management of human resources information, Microsoft needed to accelerate query performance and ease maintenance. |
Solution triples query speed, so now users worldwide can make better business decisions faster. Engineering team reclaims 30 days each year for other projects. Better performance and easier maintenance mean that more groups can benefit from enhanced BI tools. |
|
Situation
Microsoft Corporation has operations in 112 countries and more than 90,000 employees worldwide. The company maintains current and historical employee information in MSPeople, a data warehouse used internally by human resources and finance specialists. Approximately 2,500 employees internationally rely on MSPeople for headcount and recruiting information.
To accommodate multiple business scenarios, the Microsoft team responsible for MSPeople designed a data mart that pulled together information from 100 dimensional tables. End users then created dynamic ad-hoc queries with a user interface based on an internal solution called the Microsoft Reports Analytics add-in for Microsoft Excel spreadsheet software.
The business intelligence (BI) tools worked well, but performance waned as the data warehouse grew in both size and complexity. The largest table had approximately 40 million rows and more than 50 columns. Average queries took more than three minutes to run, and engineers spent hours trying to accelerate performance in addition to other development tasks. "Overall, running queries cost the user base approximately 60 working days each year," says Shreekant Saraf, Senior Program Manager at Microsoft Corporation. "And performance tuning cost the engineering team more than 30 days a year."
To improve performance, the Microsoft team tried multiple strategies, including designing online analytical processing (OLAP) cubes to pre-aggregate summary data and hiring specialists to build indexed views. As a result, the data warehouse ultimately included hundreds of indexes that required ongoing maintenance. Venkata Karumuri, Senior Software Development Engineer at Microsoft Corporation, says, "Over a period of time, some of our indexes took up more space than the actual tables themselves and also became a maintenance nightmare."
Microsoft looked for a solution that would improve user query performance and deliver near-real-time information. At the same time, it sought to reduce the management cost and burden.
Solution
The MSPeople team decided to implement Microsoft SQL Server 2012 Enterprise data management software. "Our processes run for 11 hours, and we wanted to take advantage of the optimized database engine in SQL Server 2012," says Saraf. "But even more intriguing, there was a new ColumnStore Index feature that we thought could improve query performance for end-user reporting."
In May 2011, the team began deploying the new solution and finished the project within 30 days. "Deployment was amazingly easy," says Saraf. "We had a great partnership with the SQL Server project team, and the upgrade from SQL Server 2008 R2 to SQL Server 2012 was almost effortless."
The MSPeople infrastructure includes the analytics software running on a server computer connected to two reporting servers, one with SQL Server 2008 R2 and the other with SQL Server 2012. The Microsoft Reports Analytics applications and the databases run on HP ProLiant DL585 G2 server computers with the Windows Server 2008 R2 Enterprise operating system. The server configuration includes eight CPUs and 6.6 gigabytes of memory.
The MSPeople team optimized star join schemas by taking advantage of SQL Server 2012 ColumnStore Index. A star join schema joins a large, central "fact" table to descriptive "dimension" tables. As a result, users can run multidimensional queries against a relational database. The MSPeople solution includes five fact tables and approximately 100 dimension tables.
The team eliminated most of the indexes based on row storage in its larger dimension tables and instead structured its fact tables with columnstore indexes.
Benefits
With SQL Server 2012, Microsoft can increase performance and productivity while cutting costs and improving user adoption.
Speeds Access to Information
"SQL Server 2012 delivers a win-win solution. With the built-in ColumnStore Index feature, end users can generate reports in near real-time and engineering teams benefit from reduced costs and easier maintenance."
Shreekant Saraf,
Senior Program Manager, Microsoft Corporation
With the new solution, queries run nearly three times faster. Human resources and finance employees can find the information they need more quickly and make better decisions as a result. "Before, the average query time was 220 seconds," says Saraf. "But by using ColumnStore Index, we've cut the average response time to 66 seconds."
Improves Productivity
Instead of spending hours trying to tune queries and accelerate performance, the Microsoft team can focus on projects that add value. "In the short term, our SQL Server 2012 upgrade gives users better performance," says Saraf. "In the long term, it's going to save a huge amount of engineering time, and, as a result, we will be able to produce better quality code and features."
Cuts Maintenance Costs
Microsoft expects to save both time and engineering maintenance costs with the new solution. "We anticipate saving about 30 days of user query time and 30 days of engineering time yearly with SQL Server 2012 and ColumnStore Index," says Saraf. "And we will no longer need to hire specialists, which will cut costs further."
Drives Adoption of BI Solution
Because queries perform better, the MSPeople solution can serve more users faster. The MSPeople team believes that the improved flexibility and streamlined maintenance will drive adoption throughout the company. Saraf says, "Because of SQL Server 2012, we can finally offer other Microsoft groups faster results with less effort."
With ColumnStore Index, the MSPeople team is transforming the way it manages data. "SQL Server 2012 delivers a win-win solution," says Saraf. "With the built-in SQL Server ColumnStore Index feature, end users can generate reports in near real-time and engineering teams benefit from reduced costs and easier maintenance."
For More Information
For more information about Microsoft products or services, call the Microsoft Sales Information Center at (800) 426-9400. In Canada, call the Microsoft Canada Order Centre at (800) 933-4750. Outside the 50 United States and Canada, please contact your local Microsoft subsidiary. To access information via the World Wide Web, go to:
http://www.microsoft.com/technet/itshowcase
SQL Server Columnstore Index http://social.technet.microsoft.com/wiki/contents/articles/sql-server-columnstore-index-faq.aspx
© 2011 Microsoft Corporation. All rights reserved.
Microsoft, SQL Server, Windows, and Windows Server are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. The names of actual companies and products mentioned herein may be the trademarks of their respective owners.
This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.
