You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
In this blog article, we’ll step through using PowerPivot for Excel 2010 for building a rich application in Excel.
Note: following screenshots describe the SQL Server 2008 R2 August Community Technology Preview (CTP) functionality for a feature codenamed Gemini. As recently announced, Gemini will be released under the PowerPivot brand.
After installation, Gemini appears on the Excel 2010 ribbon:
Selecting the ‘Load & Prepare Data’ button launches the PowerPivot client window:
As you would expect from any modern tool, the ‘From Database’ button launches a wizard to step you through getting data from a database. More information about data import process and functionality is available on the PowerPivot blog here. For this article, we’ll bring in following tables:
During the import process, a snapshot of all these tables is imported into PowerPivot and stored in memory using a highly scalable engine. The table Purchase, for example, contains 100 million rows. Saving the file will not only save any contents on the workbook but also all PowerPivot data. i.e. the PowerPivot data is stored as a blob within the Excel workbook to simplify transport as well as remove the management overhead of managing separate database services.
After the import, each table shows up as a tab in the PowerPivot client window. In this case, our database had relationships already defined between these tables and they were automatically recognized up by the PowerPivot Data Import Wizard. Users can, of course, define their own relationships as well.
The PowerPivot client window allows you to operate with this large data set very quickly: common operations such as sorting and filtering typically complete in under a second on common hardware available today (< $1000).
PowerPivot also allows you to extend imported data using calculations which are maintained through data refresh. Various types of calculations and mechanisms for creating them are described on the PowerPivot blog here.
So far, we’ve imported data only from a single source. You can, of course, combine data from a variety of data sources – databases, text files, ATOM data feeds, as well as just Copying/Pasting data directly – and create calculations and/or relationships as if they were a table imported from a single database.
Once you have the data, PowerPivot enhances the Excel experience by providing quick templates for frequently used layouts:
Selecting ‘Four Charts’, for example, creates a worksheet with four Pivot Charts and one worksheet each for the PivotTable source of the data, speeding up the view creation process:
PowerPivot overrides the default PivotTable Field List by providing its own Task Pane:
For those users familiar with OLAP Pivot Tables, there are several interesting features.
Instead of seeing dimensions and measures within measure groups, PowerPivot shows a tabular view of the data – just tables and columns. In addition, based on whether a column is dropped in the Values area or on Axis or Legend, the PowerPivot Task pane creates a measure or uses the field as an attribute. For example, if Distributor is dropped on the Values area, a measure (Count since this is a string column) is created automatically. If, however, Distributor is dropped on AxisFields, it is used to group the data. This highlights a few points:
Within a matter of seconds a quick view is created…
… which can be easily made more appealing using standard Excel formatting features:
As mentioned earlier, saving the workbook at this point will save both the view above as well as the PowerPivot data and any enhancements such as calculations.
PowerPivot also provides helpers for adding slicers to the workbook:
The ‘Slicers Vertical’ and ‘Slicers Horizontal’ areas create zones on the left and top of the Pivot controls that help layout, align and resize slicers easily:
The combination of usability of Slicers and the performance of PowerPivot engine provides a very interactive set of views that one could easily mistake for a rich custom application built by IT after weeks of effort. This – Self Service Business Intelligence – is the key value that Gemini provides end users.
This completes a quick run through of PivotTable for Excel 2010. In the next article, we’ll dig in to PowerPivot for SharePoint to see how it enhances the SharePoint collaboration experience.
Thanks for the comprehensive overview.
While I like the pivoting and the slicing capabilities, i am wondering how i can track trends / find outliers or discrepencies using PowerPivot. Excel's conditional formatting was very useful here.
Can you tell us how Powerpivot supports trend analysis, status monitoring, etc...?
Nice demo code.
Can we download the sample data somewhere?
Visvapriya: PowerPivot users will continue to use functionality in Excel for this behavior, the key enhancemens PowerPivot provides are ability to work with larger data sets as well as mashing up multiple tables across data sources.
Frederik: Unfortunately this demo data cannot be shared in its current form. We will release sample data likely in the next few months.
Thanks for this overview. This really gives us a good impression of what PowerPivot will be. I do have a few questions. I hope you can answer them to even give us a better impression!
1. I do believe PowerPivot will work very fast even on 100 million rows of data. But what impact will this have on corporate networks, often still with only 10 Mbit capacity? How fast will 100 million rows load into memory?
2. For a future article, it would be great to show something about the way users will be able to define relationships between different tables, especially when they load data into memory coming from several data sources. Can you shed a light on that?
3. PowerPivot will save the data with the workbook. What options will there be to refresh that data? And again, what will be the implications on network load? Will there be any support for the IT department to control this?
4. PowerPivot works without a connection to an SSAS instance. That's great, you don't have to create a cube first. But can you use existing cube data from within PowerPivot?
I'm very interested in how Excel 2010 will work with relationships as well!
of Wyndham Worldwide
I know this isn't the right post to make this comment, but you've closed posting to the Charting Posts
Can you please fix the Charts object model so that we can use custom chart formats from within VBA
ie: ActiveChart.ApplyChartTemplate ("c:\ians.crtx") doesn't work
or fix the macro recorder so that it will give us code that will actually run when played back when modifying charts
This all worked beautifully in XL 2003 and was broken in 2007 and still broken in 2010.
You submitted a bug report on Microsoft Connect and was told that the problem won't be fixed??
In the latest build of Excel 2010, ActiveChart.ApplyChartTemplate is working correctly. Also, macro recording of chart formatting options is supported.
Hi, can all the steps you went through be automated with vba?
1. Yes, the initial import of a large data set will take time and communicate to data source over the network. The rate of data loading and the bandwidth used will depend on the type of data, server configuration as well as the capacity of the machine running PowerPivot. Frequently we see better bandwidth between SharePoint and data source machines because they’re managed by IT directly. In this case, refresh of the workbook on the SharePoint server – using PowerPivot for SharePoint – may provide a better experience.
2. An interesting part of the relationship story will be unveiled for PowerPivot’s November CTP and we will blog about the process of importing, creating and managing relationships on the PowerPivot blog in the near future.
3. In the November CTP, PowerPivot for Excel will introduce support for refreshing the snapshot from the client. Resource utilization may be smaller if only a subset of tables are re-imported. On PowerPivot for SharePoint, IT will be able to set policies for throttling data imports as well as configure the notion of business hours which allows “after hours” processing to be formalized. More details on the PowerPivot blog in the future.
4. The November CTP for PowerPivot will support importing data from Analysis Services.
sebastien: PowerPivot for Excel does not provide a scripting interface and therefore cannot be driven by VBA. We look forward to more feedback to understand how important this is as we plan the next release.
Many of us yet buying Excel 2010, do you mind to review about the pros and cons of the latest excel version, if we compare it with previous one?
Note that I will be showing off relationships using the CTP3 build in tomorrow's post on http://powerpivotpro.com
Still very early in the re-implementation of a professionally-built BI solution using PowerPivot.
For background on the project, here is the first post:
Very cool that it will count when you drop a string column into values but what about If you drop a measure on the axis, will each value slice as if it were a member of a dimension?
Hi Tom. I believe that if it's an OLAP pivot in Excel, Excel will prevent you from placing a measure on a slicer, just like it prevents you from placing a measure on rows or columns.
A normal Excel pivot, however, makes no distinction between measures and dimensions - every field is just a field, and can be placed anywhere. And yes, if you place a numeric field on a slicer, you will see each unique value as a tile in the slicer. Useful for things like Quantity, I would think.
Also note that if you are using PowerPivot, which under the hood is still really OLAP, again, we make NO distinction between field types, so you CAN place "measures" on slicers. You can see how this "no distinction" is handled under the hood here: powerpivotpro.com/.../relationships-pivots-and-dax-the-payoff-part-one