Microsoft SQL Server 2012 Bible

Deploying BI Semantic Models to SharePoint

After you create a PowerPivot analysis, you may want to share it with other colleagues in your organization. Typically, you would e-mail this workbook or save it in a network share. This way of sharing PowerPivot workbooks creates several challenges:

• Increased mailbox size due to potentially large files sent as an attachment.
• The workbook can be modified, creating the risk of multiple colleagues having different versions of the same workbook.
• The workbook's content is static unless the data is refreshed from the underlying data sources.

To overcome these and other challenges, Microsoft released a dedicated Analysis Services engine based on the xVelocity engine that integrates with SharePoint 2010 called SQL Server PowerPivot for SharePoint.

SQL Server PowerPivot for SharePoint enables you to deploy and manage PowerPivot workbooks in SharePoint to special libraries called the PowerPivot Gallery. The main benefits of deploying PowerPivot workbooks to a SharePoint PowerPivot Gallery include:

• Centralized and secure storage
• Less storage wasted by avoiding multiple copies of the same workbook sent out to user mailboxes
• Ability to generate workflows and alerts when a PowerPivot workbook is modified
• Scheduling of automatic data refresh

The scheduling of automatic data refresh is a nice feature because it enables you to keep the PowerPivot workbook up to date with no manual intervention. For example, you can schedule a data refresh every night ...