Microsoft has grown by bundling increasingly capable business intelligence (BI) functionality into SQL Server, SharePoint Server and Office, but has had comparatively weak ad hoc analysis capabilities relative to other leading vendors. On 12 May 2010, it took a significant step in addressing this limitation. The introduction of SQL Server PowerPivot, Office 2010 and SharePoint 2010 extends the capabilities of Excel, giving business users the capabilities to create and analyze very large, diverse data sets.

The following questions and answers summarize feedback from three initial Microsoft-supplied SQL Server PowerPivot reference customers contacted by Gartner. All three customers participated in Microsoft's Community Technology Preview (beta) program for SQL Server PowerPivot and were asked the same questions.

Summary of Strengths:

• Fast analysis of large data sets.
• Easy to use for business users; "slicers" are an excellent interactive analysis feature.
• End-user data mashup and modeling capabilities do not require programming.

Summary of Cautions/Areas for Improvement:

• Handling of many-to-many relationships in the modeling environment is a challenge.
• Business users must learn SQL Server PowerPivot's new expression language, DAX, for advanced time series and other analysis.
• Limited facilities for data quality during the data load process.

Reference Customer 1: "PowerPivot has allowed us to access historical data that we no longer have access to under the original software application. In addition, we are able to add value to existing data by cross joining it in Excel with other databases, Excel files or even just plain text files. Before PowerPivot we would, at minimum, need to create a custom SQL query and in some cases a custom reporting application."

Reference Customer 2: "We are using PowerPivot with Excel 2010 by importing data from relational databases and data marts to analyze and develop BI reports. We are working with clinical, research and auditing data. We deal with data sizes of 120 million to 160 million of records."

Reference Customer 3: "Used in a host of places. We have been using SQL Server Analysis Services for a number of years for our BI strategy. The initial place for PowerPivot is another extension to provide data content to managers. It means moving away from standard reports to delivering content of data to manipulate themselves. This will create less and less Reporting Services reports over time and more and more PowerPivot workbooks. Moreover, views will be pulled directly from transactional systems rather than through a data warehouse. There will be less customized reports because managers will have the entire extraction of the data. The user interface is so fantastic that managers can do slicing in such an intuitive fashion. You can also see what the data looks like through the disabled elements of the slicers. It delivers to users summarized data that is easy to manipulate on the screen. On the development side, you have a very small team. Developers use PowerPivot workbooks to let them see data and see relationships for rapid prototyping. You can easily see slice throughs and understand large amounts of data."

Reference Customer 1: "Project managers, account managers, business analysts, directors and executives. We create templates with relevant data and either share them via SharePoint or a direct file share. The end user then is able to slice and dice the data that is relevant to them."

Reference Customer 2: "Information workers and data analysts are the main users using PowerPivot."

Reference Customer 3: "Developers, analysts and consumers."

Reference Customer 1: "We did not. However, we were given an opportunity to test drive the Excel Services in Microsoft's development environment."

Reference Customer 2: "Yes, we set up Excel Service on the SharePoint 2010 environment so our users can publish their reports and share them with others. It is a straightforward and easy setup."

Reference Customer 3: "Yes, which is extremely useful since not everyone has Office 2010 on their machine. Thus they can have the same rich experience in a Web browser. It saves a load on Exchange Servers, as users aren't e-mailing PowerPivot files. Very straight forward once they were at the Release to Manufacturing (RTM) version."

Functionality

Reference Customer 1: "We had success using PowerPivot to analyze everything from project profitability to project and resource planning. There were some situations where date and time grouping were a little more difficult. However, we found that these issues were easily solved with some simple workarounds."

Reference Customer 2: "Building cubes/dimensions on the fly is a great functionality in PowerPivot to view data from different perspectives."

Reference Customer 3: "Excellent for data manipulation and interactive analysis."

Ease of Deployment

Reference Customer 1: "Super easy! Since this was just a plug-in we were able to easily distribute the application for manual install."

Reference Customer 2: "Very easy and smooth deployment."

Reference Customer 3: "Very easy."

End User Ease of Use for Analyzing Data and Creating Visualizations

Reference Customer 1: "Since the data was presented in a format that everyone has had experience with there was no training necessary. Utilizing the existing and new tools in Excel 2010, the user could easily modify or create their own visualizations."

Reference Customer 2: "A little bit of training and our end users started creating their own PowerPivot reports, and some are complex."

Reference Customer 3: "Excellent."

End User Ease of Use for Data Mashup (Importing and Combining Data Sources)

Reference Customer 1: "Although PowerPivot makes this process very easy, it is still difficult for someone without some experience with a database. However, with just a basic understanding of a relational database a user can perform "mashups" that were once only possible with advance SQL skills."

Reference Customer 2: "It took them no time to import and combine data from different sources since the import process in PowerPivot is much similar to other tools."

Reference Customer 3: "Easy to use for the Excel power user. Plus, PowerPivot detects simple relationships automatically."

Ease of Integration With the Rest of the Microsoft BI Stack

Reference Customer 1: "We did not set up a SharePoint Portal internally; however, we were able to experiment with a SharePoint Portal. It was exciting to see how easy it was to deploy a new report using the Report gallery and how it handled scheduled background updates."

Reference Customer 2: "Very well integrated with the BI stack. Microsoft has done a great job by simplifying the set up of the whole farm from within one setup wizard to create SharePoint, Analysis Services and Excel Service during one installation experience."

Reference Customer 3: "Well integrated."

Overall Business Benefit

Reference Customer 1: "PowerPivot has allowed us to be a more agile company by giving us quick access to custom BI reporting. In addition, the increased flexibility has allowed us to get the data we need without a large investment."

Reference Customer 2: "Dramatic reduction in BI development time and brings more productivity."

Reference Customer 3: "It reduces the time spent by the IT organization. There are shortened development times and a better understanding of business problems. Less and less demands for customized reports could be expected, since users can manipulate data themselves. "

Reference Customer 1:

1. "Mashup of several data sources."
2. "Ease of use — it's Excel on steroids!"
3. "Easily handles large amounts of data."

Reference Customer 2:

1. "In-memory BI (fast data querying) and quick filtering/sorting."
2. "Slicers where we can add filtering to slice and dice the data."
3. "Integrated with Excel and can use Excel native analysis features."

Reference Customer 3:

1. "Out of the box in minutes a business user can use it and see the benefit. Slicers are extremely powerful."
2. "DAX is fantastic — not for everybody, but powerful for developers and advanced Excel users; today you have to do that in MDX. So pushing functionality to end users."
3. "Put the SQL Server Analysis Services engine in memory. Column store compression is very large data with fast performance. Sometimes you forget how much data you actually have on your laptop."

(Please note that in some cases, the reference customers only provided two responses.)

Reference Customer 1:

1. "MySQL support."
2. "Data handling (grouping)."
3. "Automated data refresh."

Reference Customer 2:

1. "Working with source tables that have no relationships."
2. "Understanding DAX for junior Excel users."

Reference Customer 3:

1. "Handling many-to-many relationships."
2. "Data quality."

What Are the Top Three Features Reference Customers Would Like Microsoft to Add in an Upcoming Release?

Reference Customer 1:

1. "MySQL support."
2. "More fully functional table import wizard that allows for joins and queries from multiple databases."

Reference Customer 2:

1. "Build PowerPivot workbook at the top of another PowerPivot workbook."
2. "Additional analysis algorithms."

Reference Customer 3:

1. "Bring in functionality in SQL Server Integration Services for fuzzy logic and matching for data quality and duplication in the PowerPivot engine."
2. "Handling many-to-many relationships is not an easy thing to do even in SQL Server Analysis Services. People want to do aggregations based on many-to-many relationships. It is not supported in PowerPivot and not an easy thing to do in SQL Server Analysis Services."
3. "Keep upping the bar for size of data. It now covers 80% and more types of data, but we would want it to even go further — right now the limitation is 2GB of data. We'd also like to see it start doing complex time-based aggregations; this year versus last by category can get pretty big pretty quick."