PowerPivot Attack!

There is considerable interest in implementing BI solutions between multiple OLAP providers – SQL Server Analysis Services & Oracle Cubes for example.  In order to demonstrate the tremendous integration advantages of Microsoft BI, I recently took SharePoint 2010 and PowerPivot from install-to-PowerPivot Gallery!  Here are some interesting facts and the walkthroughs:

Excel 2010 Add-In

  • Think of PowerPivot as two separate installs/environment add-ins:  Excel 2010 & SharePoint 2010
  • PowerPivot is a FREE FREE FREE Add-on to Office!  Potential cost savings are INCREDIBLE (and very uncharacteristic of Mr. Softy!)
  • Excel 2010 Add-in allows you to pull data from multiple sources (SQL Server, Excel, Data Feeds, Access, OLAP) and create relationships between them utilizing PowerPivot.
  • Pulling data from SQL Server Reporting Services is just slick!  We scheduled SharePoint 2010 to produce a data feed off of an existing SSRS report.  Then we setup PowerPivot to consume the feed.  PowerPivot basically allows you to re-mash the data sources within a report and with other reports/data sources.  Extremely powerful!
  • Excel 2010 is required for the PowerPivot POWER users but no Excel client is necessary to view and utilize PowerPivot through SharePoint 2010
  • PowerPivot OLAP integration is something to watch.  Building relationships between say SAP-Oracle and SQL Server Analysis Services could be very powerful – good article.  While you’re waiting, use the SSRS data feed.  SSRS has tons of hookins to various data sources – our most recent experiment was with SAP XMLA.
  • With Excel 2010 Vertipaq compression technology, you can manipulate 100’s of MiLlIoNs of rows of data in your puny pc RAM
  • Go to powerpivot.com and you can see quite a few well made demos and tutorials for using the Excel Add-in

SharePoint PowerPivot Service Application

  • SharePoint 2010 allows you to view the Excel Workbooks and DYNAMICALLY refresh the data
  • PowerPivot for SharePoint 2010 requires an SEPARATE SQL Server Analysis Services + SharePoint install for every PowerPivot server you wish to create
  • AGAIN, no Excel client is necessary to view and utilize PowerPivot through SharePoint 2010
  • PowerPivot Gallery is tricked out document library that utilizes Silverlight to thumbnail preview workbooks
  • In Central Admin, Report Center allows you monitor PowerPivot usage
  • There’s also a PowerPivot Site that sets up the PowerPivot gallery and other features (similar to Document Center)


  1. Download and install SQL Server 2008 R2 on an existing SharePoint 2010 WFE.  Use this PowerPivotGeek article for setup.  These technet articles are also great for background and HIGHLY recommended.
  2. Download and install the BI data set provided by Microsoft.  These are GREAT for demoing a tremendous amount of data (millions of rows for the famous Contoso).
  3. Download and install the PowerPivot Add-in for Excel 2010. 
  4. Watch this cheesy but very informative demo of PowerPivot to get a good overview of the capabilities.
  5. BUG:  At the time of this writing, PowerPivot there is a bug that is characterized by “could not load embedded data“.  Basically after closing your workbook, you will not be able to reopen it or refresh the data in SharePoint.  As a work around, refresh your data sources often, especially after a relationship is created/modified.  I’m in the habit of doing it before every save.  It’s smoooooth sailing from there.

 As a final thought, I would like to find out if PowerPivot utilizes the standard Excel data source import or if it has it’s own proprietary import.  If it utilizes Excels, perhaps ANY multidimensional data source can be related.  Obviously, you would have to install the OLAP provider on the SharePoint server.  Sounds like fun – especially with bEtA owt!

Update:  Installed Office Web Apps and PowerPivot does not appear to be compatible with Excel Web App at this time.


~ by spninjablog on February 3, 2010.

2 Responses to “PowerPivot Attack!”

  1. The embedded database is a backup of an SSAS database. We use the OPENXML file format to insert it as a blob in the workbook. On demand we pull the blob out of the workbook and load it into an SSAS instance that is our cache in the SharePoint app server (part of our SP shared service — we actually have two shared services; one which is a web service for control and one which is a Windows service which is the SSAS engine itself that we use internally. Both are sitting within the SP farm as shared services. Enjoy!

    • Do you know if 3rd party OLAP-OLEDB support is being developed for PowerPivot in SharePoint 2010? So if I create my Excel document pulling in from a 3rd Party OLAP provider (BPC, Oracle or etc), will I be able to upload that workbook to SharePoint and have it refresh data? Of course there would need to be a 3rd Party OLAP provider for the PowerPivot Service App or Analysis Service install as well.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: