Microsoft Excel

Introducing PowerPivot

Today we have a guest author from the SQL Server Analysis Services team, Ashvini Sharma, to tell us about the PowerPivot (née Gemini) feature that you may have heard about recently.

PowerPivot is the recently announced name of technologies this blog previously referred to by its codename, Gemini. This article describes why there is a need for such a tool, and briefly what PowerPivot provides. More information is available on the PowerPivot blog.

The Need for PowerPivot

PivotTables continue to be indispensible for allowing users to analyze their data flexibly and interactively. If you’re a subscriber of this blog, you’ve already read some of the recent articles on investments the Excel team continues to make around PivotTables for Excel 2010.

However, using a PivotTable that connects to an OLAP data source of course requires such a data source to exist. While a corporation may have many OLAP data sources where a single version of the truth and a unified model for looking at the business is necessary, this is not always the requirement.

For personal or workgroup-oriented solutions, our customers tell us there’re shortcomings in technology available: 

1. Requires advanced technical knowledge: Creating OLAP cubes is a non-trivial effort which requires highly technical understanding of concepts such as dimensions, measures, MDX, etc. As such, IT staff is frequently called upon to create such models on behalf of business users.
2. Incurs higher cost to solution: Since IT groups have limited bandwidth, only a few of an organization’s analysis projects get the necessary attention and resources. In order to increase efficiency, IT may also attempt to consolidate similar solutions, which incur higher coordination cost and increased time to delivery.
3. Produces solutions that are hard to customize: Business users frequently ask for data sets or analysis paths that they could not have predicted earlier. This is typical of ad hoc analysis that PivotTables support – an answer frequently leads to the next question and it is very hard to predict all possible questions, and time consuming to bake them in the model a priori. In addition, some data, may be so specific to a business problem that one user of the model may have it on their desktop, and it is not appropriate to share it across all users of a cube.
4. Increases cost of ownership and friction: Some business teams hire technical consultants or volunteer one of their own to take on this “burden”. Unfortunately, this responsibility goes beyond learning new technology into also developing skills and devoting time for managing and maintaining any delivered solutions. In addition, IT stays unaware of such underground applications and get rightly concerned about business decisions being made on solutions not supported by them.

Lets take a step back to make a few key observations:

• A significant gap exists between an organization’s need for deriving insights from their data and the organization’s capacity to satisfy that need.
• IT and business user resources are being stretched beyond their natural competencies: IT has to become more familiar with business users’ domain, and business users need to become more technical so that they can “speak” IT.
• In our view, what’s missing is simply technology that allows business users to help themselves while providing visibility to IT, a scenario we call “Managed Self Service Business Intelligence”.

PowerPivot

The PowerPivot functionality is delivered by SQL Server’s Analysis Services team in collaboration with the Excel team and is based on our experience delivering the Microsoft Business Intelligence platform over the last decade.

There’re two components of PowerPivot: PowerPivot for Excel 2010 and PowerPivot for SharePoint 2010.

Designed for business users, PowerPivot for Excel 2010 is a data analysis tool that delivers unmatched computational power directly within the application users already know and love — Excel. Leveraging familiar Excel features, users can transform enormous quantities of data from virtually any source with incredible speed into meaningful information to get the answers they need in seconds. PowerPivot for Excel consists of the following components:

• The Excel 2010 addin that delivers the seamless PowerPivot user experience integrated within Excel.
• The VertiPaq engine that compresses and manages millions of rows of data in memory with blazing fast performance.

PowerPivot for SharePoint 2010 enables end users to effortlessly and securely share their PowerPivot applications with others and work seamlessly in the browser using Excel Services. PowerPivot for SharePoint also helps IT improve their operational efficiencies by tracking PowerPivot usage patterns over time, discovering mission-critical applications, and improving system performance by adding resources. PowerPivot for SharePoint consists of the following components:

• PowerPivot Gallery – a Silverlight based gallery where users can share PowerPivot applications with others and visualize and interact with applications produced by others using Excel Services and Reporting Services.
• PowerPivot Management Dashboard – a dashboard that enables IT to monitor and manage the PowerPivot for SharePoint environment.
• PowerPivot Web Service – the “front-end” service that exposes PowerPivot data via XML/A to external applications such as Report Builder.
• PowerPivot System Service – the “back-end” service that manages the PowerPivot application database, load balancing, usage data collection, automatic data refresh, etc.
• Analysis Services – the Analysis Services server running the VertiPaq in-memory engine and integrated with SharePoint to load and manage the data within PowerPivot workbooks.

We’ll drill into these features in the next few blogs. Stay tuned!