Troubleshoot on Setup of PowerPivot for SharePoint

I know it can be really frustrating when you spend long hours just trying to setup something on your developer box. Especially, when you had followed the instructions exactly (maybe not) as in the MSDN article. 

The following MSDN article guides you to setup PowerPivot for SharePoint. 

http://msdn.microsoft.com/en-us/library/ee210616.aspx#Geneva 

I will try to highlight some of the gotchas that you need to watch out for, while you perform the setup.

The following were the configuration that I have on my dev box.

• Domain Controller with Active Directory 
• Windows Server 2008 R2
• SharePoint 2010
• SQL Server 2008 R2 Dev edition (With SSIS, SSAS & SSRS)
• Office 2010
• .Net 3.5 SP1 or above
• PowerPivot Add-in for Excel 2010  (http://www.powerpivot.com/download.aspx)

While following the Setup article, you may find some weird error like.

Failed to load receiver assembly "Microsoft.AnalysisServices.SharePoint.Integration, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" for feature "PowerPivot" (ID: f8c51e81-0b46-4535-a3d5-244f63e1cab9).: System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.AnalysisServices.SharePoint.Integration, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
File name: 'Microsoft.AnalysisServices.SharePoint.Integration, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'

If you see the above error then you will be really interested in the following detail of important steps.

1. In the Installation Type step of the wizard, select “New installation or add shared features”.  DO NOT select the “Add features to an existing instance…” option.
2. In the Setup Role step in the wizard, select the “SQL Server PowerPivot for SharePoint” radio button.  DO NOT select the SQL Server Feature Installation option.In the Instance configuration step, keep the Instance ID as POWERPIVOT. Keep the SQL Server Analysis Services of default MSSQLServer seperate from this instance required for PowerPivot 
3. In the Service configuration step, provide a domain account that has rights on both the SQL instances. I know it is not a good practice, but what the hell, this is supposed to be a developer box. I used the domain admin :)
4. In the Read to Install step, you will have to browser to the configuration file path shown at the bottom of the dialog. Modify the FARMADMINPORT value to point to the existing SharePoint Central Administrator port.
5. Before you move to the next step in the wizard,
• Ensure that you have the Microsoft.AnalysisServices.SharePoint.Integration.dll in C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\x64. This dll can be found in GAC from one of your previous failed attempts. 
• Remove all instances of the Microsoft.AnalysisServices.SharePoint.Integration.dll, especially from the GAC. Find other instances of it (temp folders of GAC) and delete them as well. 
•  Note: If you are not able to delete some file instance because some process is holding it up then find out the process and go to the Task manager or the Windows Service to terminate/stop the process. Usually it will be w3wp.exe, OWSTimer, Web Analytics services. You can use the Process explorer to identify the holding process. http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx 
6. Good Luck here.. before you press that Install button. 

Have patience in the Last step. it could take a while to finish the last bit. I had to start the SharePoint timer service thinking, maybe the FarmDeployment workflow was not getting completed because of a timer service was terminated in the step 5 (I am not sure about this. Just in case you face some problems).

A comprehensive guide to setup is avaialble here

Post the installation, for configuration you can follow the MSDN article.

Just before you leave one Tip,
Once you install PowerPivot Add-in to Excel you may see that loading of Excel becomes slow. If you may wish to enable/disable this add-in then go to Excel -> File -> Options -> Add-ins -> Manage (COM/Disabled Add-ins)



Hopefully, this information has saved you that precious time.

Now, that I have been able to setup, I am waiting to explore PowerPivot for SharePoint in detail. If I come across anything interesting then I will share my views in the next article. Till then stay tuned :)

Excel Services (SharePoint 2010)

Excel 2010 is an authoring tool while Excel Services is more of a reporting tool.

The following are the main uses:

• Sharing & Collaborating spreadsheets through browser.
• Report Building and publishing using dashboards.
• Provide a Web-service interface to allow applications to interact with the Spreadsheets stored at Server.

What's new for Excel Services (SharePoint Server 2010)?

• Multi-user Collaboration : This helps in simultaneously authoring a Workbook by more than 1 person. This feature is there in the SkyDrive. You may want to try it out.
• Slicer feature : The Slicer is a Business Intelligence data filter in Microsoft Excel 2010. It helps in creating an interactive and flexible design layout for analyzing the business data while using PivotTables and OLAP functions.
• Ajax Enabled : Service is Ajax enabled and hence changes to a range of cells will only require to refresh these cells instead of the entire page.
• Improved User Defined Functions : This helps in creating complex calculations over cells of the workbook.
• Manage Service Applications : The Service can be enabled in the Central Administration console.
• Windows PowerShell : Excel Services PowerShell commands aids in Installing/Configuring in Power Shell Command Prompt without going to the Central Administrator console. This also helps in automating the build and setup of the application.
• Trusted Locations : Ensures that all the Workbooks that get loaded are Trusted. These locations are created by default.
• Unattended Service Account : This is a special low privilege account usually will have only Read access. This will be used when the Authentication has been set to None.
• Client Applications: Apart from using the regular Excel services APIs, the service can be exposed through REST (Representational State Transfer) API. You can embed the excel content onto other office apps or on web. Users can also use JSOM or ECMAScript to share Excel content to allow interactive behavior through web browsers.
 

Architecture:

• Excel Calculation Services :  is the engine that loads the spreadsheet and workbook, calculates the spreadsheets, updates external data, and maintains session state for interactivity.
• Excel Web Access :  is a Web Component component that delivers the Excel workbooks.
• Excel Web Services : is a Web Service hosted in SharePoint that provides various methods for developers to create custom applications that are built on the Excel workbook.

To setup and start using the Excel Services you need to understand the following concepts:

Connections and Excel workbooks:

Excel workbooks can contain two types of connections.

• Embedded connections are stored internally as part of the workbook.
• Linked connections are stored externally as separate files that can be referenced by a workbook. Linked connection files can be centrally stored, secured, managed, and reused.

Office data connection files (.odc) are the connection files used in Excel Services Application.    

Trusted File locations Excel SA only loads workbooks from trusted file locations. An administrator has to explicitly mark a directory (SharePoint document libraries, UNC paths, or HTTP Web sites) as a trusted file location. These directories are added to a list that is internal to Excel SA.

Trusted Data providers These are external databases that Excel Calculation Services is explicitly configured to trust when it is processing data connections in workbooks.

Trusted Data connection libraries These are SharePoint document libraries that contain Office data connection (.odc) files.

Authentication: Excel Services Application accesses external data sources by using a delegated Windows identity.

There are three modes of Authentication to the data source

• Windows Integrated (Requires servers to be on same domain. Kerberos setup required if there is a double hop)
• Secure Storage
• None

Cache: 

Excel Services comes with a cache to prevent the load/refresh of the data from the data source for every request. However at times this cache may have to be reloaded for every request. This can be enabled to reload on every open. This will help if different data is to be shown for different set of users. A cache expiration time also can be set to invalidate the cache and reload new data.

References:

http://technet.microsoft.com/en-us/library/ee424405.aspx

http://technet.microsoft.com/en-us/library/ee424401.aspx

Video - http://www.msteched.com/2010/NorthAmerica/BIU06-INT