PowerPivot is Analysis Services, sort of

PowerPivot is essentially a way of making an analysis services cube using excel as the design tool.  When you use the PowerPivot for Excel add-in, then there no backend dependency, but if you want to save a PowerPivot to SharePoint 2010, there has to be a special installation of SQL Server 2008 R2 analysis services associated with the SharePoint farm.

This integrated installation might seem like the way Reporting Services can also be configured to work with SharePoint, however when analysis services is installed in this way it can’t be used as a repository for traditional cubes.  So you can’t use management studio to manage this new environment it all has to be done through SharePoint. Nor can you deploy (build or restore) ‘normal’ cubes to this instance form scripts or the BI Dev studio.

However a good test that your PowerPivot environment is running properly is to post a PowerPivot to SharePoint ..

Now open the PowerPivot you just saved

 

and copy it’s URL..

 

Now open SQL Server Management Studio and connect to an analysis services database..

 

and paste the in that URL to the PowerPivot..

and the PowerPivot looks like any other cube.  However the advice here is to look but don’t touch.  So apart from testing the only other thing I can think of that you might want to do is to script out the data source view or schema.  The use case for this would be to quick start the design of a traditional analysis service database, because the PowerPivot has moved form being a tactical solution to a strategic one.

Another thing to not about the connection string is that it can also be used in Reporting Services or anything that can consume analysis services data to make the PowerPivot a data source e.g. you can write and run a report against a PowerPivot..

 

I have a short video on sharing PowerPivot Data here and there is a complete site dedicated to PowerPivot https://PowerPivot.com

Technorati Tags: PowerPivot,Excel 2010,Office 2010,olap,BI