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.
"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."
This dual nature of PowerPivot (acts like OLAP cubes in some ways, Excel PivotTables in other ways), may cause confusion with some users familiar with Excel's PivotTable features. For example, although you can place a PowerPivot measure in the Row or Column area, the measure is treated as text. The main reason why people place numbers in the Row or Column area is for grouping the numbers.
A similar problem exists with dates, but it's easier for the model developer to anticipate date grouping by creating common date-part calculated fields (Year, Month, Quarter etc.) in the PowerPivot source table. There's some flexibility in this approach in that you can create a whole bunch of date-parts for calendars that Excel date grouping doesn't support (fiscal, marketing, manufacturing etc.)
Other potential user interface confusion resulting from PowerPivot's OLAP nature include its sorting behavior, inability to create even text groups, enabling What-If options in the Ribbon(which it doesn't support) and disabling Summarize Value As in the Ribbon (which it does supported). These may be minor or major issues, depending on your disposition.