celedonpartners.com

Installing Power View for SharePoint in a Test VM

by | Mar 26, 2014 | Blog |

An Old-School Developer Learns New Tricks

I am really getting to enjoy the whole concept of self-contained test environments. Honestly, I need them. After decades behind the keyboard and many installation nightmares somehow I still believe that every install should go smoothly … or at least it can be salvaged. Recently a project required an Excel Power View workbook to be accessible through a SharePoint web part. Excel 2013 has no trouble with Power View. Simply activate the Add-In, connect to your data source and display!

 

 

 

 

 

Seemingly one would think that only an extra step or two would be required to view it in SharePoint as it is just another Office document, right? Not exactly. There is an amazing amount of supporting framework required to make this happen.

Let’s Get Started

Even though standard SharePoint displays Excel worksheets this is not the case for Power View. It took a bit of research to confirm my suspicions. The features Power View requires only exist in the Enterprise versions of SharePoint 2013 and SQL Server 2012.

Tip: Use the administrator password for everything. There are enough places to make a mistake. Don’t let multiple accounts add to your troubles.

Three resources provided me the bulk of step-by-step information and links to other resources:

  1. Comprehensive SharePoint 2013 Development Environment Installation and Configuration
  1. SharePoint Server 2013 for Advanced Solutions (70-33) on Pluralsight
  1. SharePoint Server 2013 Administration on Pluralsight

If you do not have a Pluralsight account you can sign up for a free trial and it should provide you enough minutes to watch the pertinent sections.

SQL Server 2012 SP1 Installation

  1. Install SQL Server 2012 SP1 with Database Engine Services, Analysis Services, Integration Services, Management Tools, Multidimensional and Data Mining Mode
  1. Tabular Service with Analysis Services and Tabular Mode
  1. PowerPivot Service with SQL Server PowerPivot for SharePoint setup role, Analysis Services and Analysis Services for SharePoint Integration, Documentation Components
  1. Set Max Degree of Parallelism to 1 in SQL Server Management Studio

SharePoint 2013 Installation

  1. Install prerequisites by clicking Install software prerequisites on the splash screen or run prerequisiteinstaller.exe.
  1. Mount or make the SharePoint 2013 media accessible and run the Complete Installation.
  1. From the Start Menu click SharePoint 2013 Central Administration. Use the wizard for initial setup.

Install Reporting Services for SharePoint

Start the SQL Server Installation Center

  1. Install Reporting Services with Reporting Services SharePoint, Reporting Services Add-in for SharePoint
  1. Download and install rsSharePoint.msi
  1. From the Start Menu click SharePoint 2013 Management Shell
    • Execute Install-SPRSService and Install-SPRSServiceProxy

Create a Reporting Services Application

  1. From the Start Menu click SharePoint 2013 Central Administration
  1. Click Application Management | Manage Service Applications
  1. Click New and select SQL Server Reporting Services Application
  1. When process completes click Provision Subscriptions and Alerts
  1. Click Download Script, save and run in SQL Server Management Studio

Installing PowerPivot for SharePoint

  1. From the Start Menu click SharePoint 2013 Central Administration
  1. Click Application Management | Manage Service Applications | Excel Services Application
  1. Add <SERVER>POWERPIVOT as server in Data Model Settings
  1. Download and install spPowerPivot.msi
  1. From the Start Menu click PowerPivot for SharePoint 2013 Configuration
  1. Modify settings so validation succeeds

Activate Power View for the SharePoint Site

  1. Click Settings (gear icon) at the upper right
  1. Click Site Settings
  1. Click Site Collection Features under Site Collection Administration
  1. Click Activate for Power View Integration Feature
  1. Click Activate for PowerPivot Feature Integration for Site Collections

Troubleshooting

Between blog postings and our experience it is unlikely the Power View rendering in SharePoint will work the first time. Fortunately, SharePoint by default provides comprehensive logging that provides much more detail than the displayed error message.

Log Location

Default location: C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions15Logs

Note that the log files appear to be generated about every 10 minutes and they are verbose. There are a few viewers available through CodePlex and Microsoft. NotePad++ also works and it is a simpler interface.

“We cannot locate a server to load the workbook Data Model” Error

There are several explanations and solutions. Check the error log for this error as it will provide more detail. These links provide some insight:

http://support.microsoft.com/kb/2769345/en-us

http://www.sharepointpitstop.com/2013/10/we-cannot-locate-server-to-load.html

http://blogs.msdn.com/b/psssql/archive/2013/01/09/getting-a-power-view-report-within-excel-2013-to-work-with-sharepoint.aspx

External Data Refresh Failed Error

This only occurs when the user attempts to refresh the data. It loads fine initially. If the Excel Services server path is incorrect it will not load at all. The assumption is that the server path is correct because it is pointing to the correct server and the correct database. These links provide some insight:

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

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

And if All Goes Well

The most stressful part for me at least is this last step. This process takes a few hours and even though everything might point towards success the only proof is to upload an Excel Workbook containing a Power View to SharePoint and click on it. After a deep breath and a few beads of sweat running down my forehead I did just that. I was one of the fortunate.