Excel and especially PowerPivot are in the latest incarnation of the Microsoft’s BI-suite (Office, SharePoint, SQL Server) hyped as tools giving many more users access to analysis and other business intelligence.

ebs.dk does to a certain degree agree with Microsoft on this.

In this article we discuss possibilities and pit falls in a roll out of PowerPivot.


PowerPivot should be considered as a powerful and relatively easy-to-use complement to a mature BI implementation enabling analysts and new BI users to create analyses much faster than before.

If you cannot beat them – join them! Though BI Competence Centers and IT organizations traditionally and for a number of good reasons tend to be rather skeptical as using Excel for reporting and analysis – the business users seem to love Excel.

The advanced business users have in PowerPivot got a real powerful tool for analysis and insight that previously would have implied much iteration between the requester and the analyst to be produced – if the analysis at all were identified.

The BI Competence Centers and IT organizations might as well surrender right away and focus on providing the now even more needed high-quality end well-documented data marts and management to support the organizations’ ever increasing need for BI.

The PowerPivot related need for training for the business users should focus on:

1) Learning DAX

2) Learning and contributing to the data marts.

The PowerPivot related need for training for the BI Competence Center and IT should focus on:

1) Structuring and configuring SharePoint

2) Management of PowerPivot

What is PowerPivot?

PowerPivot is three things:

1) A ‘role’ (Analysis Services in SharePoint integrated mode) in SQL Server 2008R2

2) An add-in to the client application Excel 2010 – “PowerPivot for Excel”

3) An add-on to SharePoint Server 2010 – “PowerPivot for Sharepoint”

PowerPivot for Excel

In popular terms PowerPivot for Excel is a subset of Microsoft SQL Server Analysis Services, a compression engine and support for a multidimensional query language DAX (a ‘subset’ of MDX).

PowerPivot for Excel incorporates a local version of SSAS giving access to most of the analytical power of SSAS.

PowerPivot for Excel downloads the data to be analysed locally and compresses the data very hard thus enabling the data to be stored and processed in memory, giving very fast response times for the analyses.

The usual 1,048,576 max row limit for Exchange do not apply to PowerPivot the max row limit in PowerPoint is only dictated by the amount of RAM available on the client machine.

The standard functions in Excel are not adequate when it comes to querying multiple dimensions therefore Microsoft has included functions and support for Data Analysis Expressions (DAX).

SQL Server 2008R2 roles

SQL Server’s well-known support for integration of Reporting Services <Link> has in version 2008R2 been augmented by support for Analysis Services integration.

This integration mode enables SharePoint to integrate to and use all the power of SSAS.

PowerPivot for Sharepoint

Same functionality as in PowerPivot for Excel but executed on a web server – thus adding scalability, security, workflows, versioning, manageability, accessibility...

PowerPivot speadsheets published on a SharePoint server can be accessed with full functionality from the web and does not require any locally installed applications except for a web browser and the data sources are not downloaded locally.

The target audience for PowerPivot

PowerPivot is part of Microsoft mission of Self Service BI, empowering the Information workers to collect and analyze data on their own in an environment they work and know best ... Excel.















PowerPivot is designed to do so without IT' active participation but with IT monitoring. To make use of PowerPivot the users don't need to know how to build sophisticated SSAS solutions in the Visual Studio environment.

It is ebs.dk’s expectation that PowerPivot will continue to enhance and will become more and more powerful however you should not expect it to replace the SSAS tool’s offerings. There will be always be differences because of the different needs and abilities of the different audiences it servers.

What is needed

To do ad-hoc analysis on a desktop, all you need is Excel 2010 and the free PowerPivot add-in on the user machine – and of course access to data!

Microsoft supports access to data provided by virtual all major vendors (Oracle, IBM, MySQL, Informix, Teradata, Sybase, SQL Azure …) and formats (SSRS reports, ATOM Feeds, Excel, Access, Flat Files, spreadsheets …).

The most viable data sources are of course the validated and quality assured cubes, that should be available from the organization’ data mart.

If you need collaboration and extended reporting, then you need a server with SharePoint and Analysis Services 2008 R2 (In-memory storage mode).

To implement custom calculations or simple data cleansing in PowerPivot you would also want to learn DAX (Data Analysis Expression).

To support the PowerPivot users you will want to train your IT organization and your BI Competence Center.


Don’t oversell – PP is not that easy to use!

User’s that today are uncomfortable working with pivot tables in Excel will have a hard time using PowerPivot.

It is still kind of Excel

Don’t expect your analysis to be well-documented!

You will still have to structure your BI

Though giving (relatively) easy access to data and analytical power – you will still have to provide cleaned and well-structured data to be able to conduct valid analyses.

No analysis will be more valid than the quality of the data and the analyst’ knowledge of the business processes being analyzed and the data sources used allows.

So you still have to know your business and your data and you will still – and probably even more – have to provide well-structured and –documented data about the business processes to the analysts and the business users. This is not ‘one-version-of-the-truth’ but at least ‘one-version-of-the-data mart’.

You would like to have some approval workflows

As PowerPivot enable ‘anybody’ to conduct and publish their own analyses you probably would find it relevant to apply some policies as to which analyses are published where - and by whom.

Fortunately SharePoint provide good support for approval workflows, versioning …

Many versions of the truth

A popular goal for Business Intelligence is to provide ‘one-version-of-the-truth’ thus enabling the business organization to focus on the business objectives instead of on the counting methods.

With PowerPivot ‘anybody’ can create their own ‘version-of-the-truth’ – which should be considered good for many reasons.

You would though want to make sure that at least the data for “anybody’s” version of the truth is consistent and validated. This is what ebs.dk calls ‘one-version-of-the-data mart’.

Beware of network load

As PowerPivot on the client (PowerPivot for Excel) requires all data to be downloaded from the database server – a lot of network traffic might be generated! Consider someone creates a PowerPivot spreadsheet that gets really popular.

SharePoint provides good tools to manage popular PowerPivot spreadsheets - without generating network load.

Link to further information

Good and comprehensive site on installation and use of PowerPivot: http://powerpivotgeek.com.