PowerPivot Developer’s Career Is Full of Twists and Turns

REDMOND, Wash. – May 12, 2010 – Donald Farmer knows a thing or two about pivoting. A principal program manager of the SQL Server Business Intelligence management team, he has had a career filled with turning points that have taken him from fields as disparate as medieval archaeology and fish farming. Farmer’s eclectic resume prepared him well for his most recent undertaking: transforming business intelligence (BI) analysis and reporting from a specialized skill practiced mainly by IT professionals to a standard tool at the disposal of all decision-makers.

The Microsoft SQL Server 2008 R2 release, available today for volume licensing customers worldwide, delivers managed self-service BI through SQL Server PowerPivot, one of its most popular features. Starting today, customers can download the trial version of Office 2010 and SharePoint 2010 and the Web downloads of PowerPivot for Excel and SharePoint. Farmer describes PowerPivot as a “game-changing” technology that alters the way business decisions are made, enabling users to create their own reports and conduct their own analyses.

Until now, if business users wanted to take advantage of the large volumes of data their company collected and the powerful analytic tools available, they had to ask their IT department to generate a report. “But with the managed self-service BI capabilities in PowerPivot that complement our existing BI technologies, they’ll be able to do such analyses themselves,” Farmer says. “That means they’ll be able to answer questions more quickly, experiment more and test ‘what-if’ scenarios. Organizations can become more agile. That’s exciting.”

Spinning Data Into Gold

Surprisingly for someone in his position, Farmer does not have a formal background in computer science. Scottish by birth, he studied Gaelic, Celtic history and philosophy at the University of Glasgow, but soon hit one of his many pivot points. After doing stints as an archaeologist and a teacher of creative writing, he consulted for some of Scotland’s traditional rural industries, including forestry, fish farming, and oil and whiskey production.

Farmer gravitated toward the power of data analysis throughout his pre-Microsoft career. For example, to produce practical recommendations for fish farmers he analyzed past, present and predicted rainfall data. By the time he made another career pivot and joined Microsoft in 2001, he was hooked on BI.

The assignment Farmer’s team was given two and a half years ago: Do for BI analyses what Microsoft’s PowerPoint program did for presentations — make them a do-it-yourself business staple. Their solution, PowerPivot, is a bridge of sorts between SQL Server 2008, SharePoint Server 2010 and Excel 2010.

PowerPivot delivers unmatched computational power directly into Excel, letting users transform millions of rows of data from multiple sources into meaningful information. “The most radical thing about PowerPivot is what end users don’t have to learn,” says Farmer.

Using PowerPivot for Excel, end users can build the reports they need by themselves, even incorporating live connections to both internal and external data sources. They can then share the reports with others in their organization using PowerPivot for SharePoint. Once in SharePoint the report will automatically refresh periodically to keep the data and analysis current. In addition, the IT department can monitor activity. So for example, if there is a report that is receiving a lot of hits, IT can suggest that it be shared more broadly within the enterprise, or even consider making it a de facto company standard.

Donald Farmer, who was a leader in the development of PowerPivot, is a principal program manager on the SQL Server Business Intelligence management team.

New Customers, New Colleagues

Before the dream of PowerPivot could become a reality, the user interface team had to accomplish two critical things.

“First, we had to learn about the specific needs and preferences of end users. We always do extensive user research, but for PowerPivot we had to find a new community of users to work with.” That’s where Farmer’s unusual background proved invaluable. “We spoke with hundreds of customers from a wide range of industries and geographies to try to understand how they would like to be able to use data analytics,” he says. “These customers aren’t IT or BI professionals. Some have never even heard the term ‘business intelligence.’ They speak a different language than we had been used to. But they knew what they wanted.”

Since Farmer and some other members of his team had come to the software industry as outsiders and had worked in so many different industries, they were able to have detailed conversations with a wide variety of customers about their specific information requirements and what drives their business decisions. For example, among the customers with whom his development team consulted were police crime analysts. “Unlike most of the business people we spoke with, they couldn’t care less about financial data,” says Farmer. “Their need for advanced analytics couldn’t be described in traditional business terms.”

The team’s work with customers definitely paid off, says Farmer. “For example, we were working with Microsoft Research on an algorithm that discovers relationships between data tables. The customers we worked with helped us understand which types of relationships would be relevant for most business users.”

The second key challenge was to create an application that is fully integrated with two existing products — Excel and SharePoint — and that mimicked those products’ user interfaces. One of the ways the team attacked this problem was with a characteristically out-of-the-box idea: a handful of SQL Server developers were embedded in the team that was developing the latest version of Excel for the Microsoft Office 2010 suite. These “double agents” helped ensure the seamless integration of the two products.

Microsoft has long been tracking on a vision of bringing BI to more users in an organization — taking BI out of the C-level suite and giving all employees in an organization the kind of decision support that top business analysts have had access to. It’s the employees on the front lines that make the day-to-day decisions that lead to a business having a competitive advantage in the marketplace, Farmer says. For more on Microsoft’s managed self-service BI strategy, see Microsoft Brings Business Intelligence Deep Into the Enterprise With SQL Server 2008 R2.

Customer Reactions

Most recently, Farmer has taken his message on the road. He has been attending conferences and visiting customers all over the world, evangelizing the benefits of managed self-service BI. “Everywhere we’ve gone there’s been tremendous interest in the new SQL Server release. A lot of people seem to be surprised at how many new features are contained in this release. Also, the self-service BI concept has been quite popular, especially among the more senior-level people I’ve spoken with.”

Even the eruption of the volcano in Iceland couldn’t keep people away from Farmer’s recent European information-sharing events — thanks to Microsoft’s Live Meeting service.

This globe hopping has allowed Farmer to make some interesting comparisons of the business environment in different regions. He noticed, for example, that in China many of the companies he met with had never used any BI software before, and were interested in SQL Server 2008 as a low-barrier-to-entry way to make the BI leap. “On the other hand, China is way ahead of the West in the use of social networking for business collaboration,” he says.

BI Is About People

Farmer says he gravitates toward BI because he is fascinated by how facts, opinions and emotions all play a role in business planning. “Decision-making can be very technical, yet very human as well,” he says.

Had Farmer had access to PowerPivot as a medieval archaeologist some 20 years ago, tasks that took him a year to accomplish could have been done almost instantaneously, he muses. For companies today, PowerPivot is taking analyses that used to require months and cutting them down to a few days. And it’s enabling users of all stripes to do advanced analytics, combine data sources, and discover patterns and trends. “With PowerPivot, we are helping people make decisions that have an impact,” Farmer says.