Export (0) Print
Expand All

IT Cuts Costs and Improves Access to their Enterprise Data Warehouse

Business Case Study

Published July 2014

To improve the management of human resources information, Microsoft deployed its new data warehouse technology. The solution tripled performance, reduced the storage requirement, and simplified management. Users, operations, and engineers became more productive. As a result, the company cut costs, increased user adoption, and improved user satisfaction.

Download

Download Business Case Study, 308 KB, Microsoft Word file

Situation

Solution

Benefits

Products and Technology

Many people at Microsoft rely on the internal MSPeople data warehouse. As the data warehouse grew, its performance slowed, and maintenance became increasingly time-consuming.

The MSPeople team implemented SQL Server 2014 to take advantage of the optimized database engine and the clustered columnstore index feature.

  • Solution tripled query speed, so now users worldwide can make better business decisions faster.
  • Engineering team reclaimed 30 days each year for other projects.
  • Better performance, easier maintenance, and a lower storage requirement mean that more groups can benefit from enhanced BI tools.
  • Windows Server 2008 R2 Enterprise
  • Windows Server 2012 Enterprise
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2014 Enterprise

Situation

Microsoft Corporation has operations in 112 countries and more than 103,000 full-time employees and 92,000 vendor employees worldwide. The company maintains current and historical employee information in data warehouse called MSPeople. A database is used to store data while a data warehouse is mostly used to facilitate reporting and analysis.  MSPeople is used internally by human resources and finance specialists. Approximately 2,500 employees rely on MSPeople for staffing and recruiting information.

To accommodate multiple business scenarios, the Microsoft team that's responsible for MSPeople designed a data mart that pulled together information from 125 dimensional tables. This data mart is the access layer of the data warehouse that is used to get data out to the users. End users then created dynamic ad hoc queries by using a user interface that's based on an internal solution called the Microsoft Reports Analytics (MSRA) add-in for Microsoft Excel spreadsheet software.

These Excel based tools are commonly known in the industry as business intelligence (BI) tools. These BI tools worked well, but performance waned as the data warehouse grew in both size and complexity. The largest table had approximately 70 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. "And performance tuning cost the engineering team more than 30 days a year."

To improve performance, the MSPeople 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. Celestine Joseph, Senior Software Development Engineer at Microsoft, says, "Over a period of time, some of our indexes took up more space—30 percent of the database size—than the actual tables themselves and also became a maintenance nightmare."

The MSPeople team looked for a solution that would improve query performance and reduce the database size. At the same time, the team sought to reduce the management cost and burden.

Solution

The MSPeople team decided to implement Microsoft SQL Server 2014 Enterprise data management software. "But even more intriguing, there was a new clustered columnstore index feature that we thought could improve query performance for end-user reporting and reduce the database footprint."

In December 2013, the team began deploying the new solution and finished the project within 30 days. "Deployment was amazingly easy," says Joseph. "We had a great partnership with the SQL Server product team, and the upgrade from SQL Server 2012 to SQL Server 2014 was almost effortless."

The MSPeople infrastructure includes the analytics software running on a server computer that's connected to two reporting servers, one with SQL Server 2012 and the other with SQL Server 2014. The Microsoft Reports Analytics applications and the databases run on existing HP ProLiant DL585 G2 server computers that use the Windows Server 2012 Enterprise operating system. The server configuration includes eight CPUs and 6.6 gigabytes of memory. This is the existing hardware that the MSPeople team purchased in 2009.

The team optimized star join schemas by taking advantage of the SQL Server 2014 clustered 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 six fact tables and approximately 125 dimension tables.

The team eliminated most of the indexes based on row storage in its larger dimension tables and instead structured its fact tables by using clustered columnstore indexes."Our processes run for 11 A star join schema joins a large, central fact table to descriptive dimension tables. hours, and we wanted to take advantage of the optimized database engine in SQL Server 2014," says Saraf.

"SQL Server 2014 delivers a win-win solution. With the built-in clustered columnstore index feature, end users can generate reports in near real time, engineering teams benefit from reduced costs and easier maintenance and operational cost reduces storage requirements."

Shreekant Saraf
Senior Program Manager
Microsoft Corporation

Benefits

By using SQL Server 2014, the MSPeople team at Microsoft increased performance and productivity while cutting costs and improving user adoption.

Faster Access to Information

With the new solution, queries run nearly three times faster. Human resources and finance employees can find the information that they need more quickly and make better decisions as a result. "Before, the average query time was 220 seconds," says Joseph. "But by using clustered columnstore index, we've cut the average response time to 60 seconds."

According to Nazeer Shaik, HR Solution Delivery Manager at Microsoft, "The users are getting more than 200 percent better performing queries, and a large new user group joined the MSRA users without degrading the performance for the whole user base. These performance gains have allowed the users to run bigger and more complex queries without having to wait for a long time."

Improved Productivity

Instead of spending hours trying to fine-tune queries and accelerate performance, the MSPeople team can focus on projects that add value. "In the short term, our SQL Server 2014 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."

Reduced Maintenance Costs

Microsoft expects to save both time and engineering maintenance costs by using the new solution. "We anticipate saving about 30 days of user query time and 30 days of engineering time yearly with SQL Server 2014 and clustered columnstore index," says Saraf.

Increased Adoption of BI Solution

Because queries perform better, the solution can serve more users faster. The MSPeople team believes that the improved flexibility and streamlined maintenance will increase adoption throughout the company. Saraf says, "Because of the improvements to SQL Server 2014, we can finally offer other Microsoft groups faster results with less effort."

Karthikeyan Muthukrishnan and Sushil Darekar, Operations Leads at Microsoft, indicated that the reduction in storage requirement significantly affects the maintenance cost of the data warehouse. Clustered columnstore index is one step in the right direct to cut that cost.

By using clustered columnstore index, the MSPeople team is transforming the way that it manages data. "SQL Server 2014 delivers a win-win solution," says Saraf. "With the built-in clustered columnstore index feature, end users can generate reports in near real time, engineering teams benefit from reduced costs and easier maintenance and operational cost reduces storage requirements."

For More Information

For more information about SQL Server columnstore index, go to http://social.technet.microsoft.com/wiki/contents/articles/sql-server-columnstore-index-faq.aspx.

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

http://www.microsoft.com/microsoft-IT

 

© 2014 Microsoft Corporation. All rights reserved. Microsoft, SQL Server, 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.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft