Using PowerPivot with Excel 2010

re: Using PowerPivot with Excel 2010

Colin Banfield — Wed, 04 Nov 2009 15:46:04 GMT

"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.

re: Using PowerPivot with Excel 2010

Rob Collie — Wed, 04 Nov 2009 13:05:12 GMT

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

re: Using PowerPivot with Excel 2010

Tom — Tue, 03 Nov 2009 19:07:36 GMT

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?

re: Using PowerPivot with Excel 2010

Tom — Tue, 03 Nov 2009 19:07:33 GMT

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?

re: Using PowerPivot with Excel 2010

Rob Collie — Mon, 02 Nov 2009 02:59:03 GMT

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:

powerpivotpro.com/.../announcing-the-great-football-project

re: Using PowerPivot with Excel 2010

Stuart — Sun, 01 Nov 2009 09:58:58 GMT

Hans,

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?

Many Thanks,

Stuart Taylor

re: Using PowerPivot with Excel 2010

Joseph Chirilov — Wed, 28 Oct 2009 15:35:06 GMT

Hans:

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.

re: Using PowerPivot with Excel 2010

sebastien — Tue, 27 Oct 2009 17:32:20 GMT

Hi, can all the steps you went through be automated with vba?

Tanks

Sebastien

re: Using PowerPivot with Excel 2010

Scott Ruble [MSFT] — Tue, 27 Oct 2009 16:49:39 GMT

***,

In the latest build of Excel 2010, ActiveChart.ApplyChartTemplate is working correctly. Also, macro recording of chart formatting options is supported.

Thanks,

Scott [MSFT]

re: Using PowerPivot with Excel 2010

Colin Banfield — Tue, 27 Oct 2009 13:11:43 GMT

***,

You submitted a bug report on Microsoft Connect and was told that the problem won't be fixed??