SharePoint24x7 It's all about SharePoint.

4Jun/140

Article – Fixing PowerPivot Management Dashboard Errors

Posted by Joy

We installed SQL Server PowerPivot for SharePoint and we also executed PowerPivot for SharePoint 2013 Configuration tool and configured PowerPivot for SharePoint.

Now we need to make sure that PowerPivot Service Application instance is configured correctly for us to move forward. Unlike any other service application settings/ configuration pages, PowerPivot Service Application instance settings page is considered as the Dashboard for PowerPivot Service Application instance.

Let’s go and explore PowerPivot Service Application instance and it’s configuration.

  • Open SharePoint 2013 Central Administration web site. Click Manage service applications from Application Management section.
  • Click PowerPivot Service Application instance name to navigate to PowerPivot Management Dashboard.PowerPivot Service Application instance
  • Most of the time, you will end up getting following screen. You will see Excel Services error in the Infrastructure – Server health web part and another error in Workbook Activity – Chart web part. The reason for this error is, App Pool Service Account doesn’t have access to Central Administration content database.PowerPivot Management Dashboard
  • Open SQL Server Management Studio and connect to Database Engine.
  • Expand Databases folder and locate the SharePoint Central Administration content database. Expand the Security folder and then expand the Users folder.Granting membership in SQL Server
  • Select the App Pool Service Account, right click the user account and select Properties. Select Membership page from the Select a page list. Select SPDataAccess database role membership.Granting membership in SQL Server
  • Click OK and close SQL Server Management Studio.

Now you can go and verify PowerPivot Management Dashboard to see that both Red X are gone.

3Jun/140

Article – Configuring PowerPivot for SharePoint 2013

Posted by Joy

In the previous articles, we discussed installing SQL Server PowerPivot for SharePoint 2013 and associating PowerPivot SQL Server Analysis Services server instance with Excel Services. Before we get started with PowerPivot, we need to perform initial configuration for PowerPivot.

This article guides you through the steps required for the initial configuration of PowerPivot.

Before we get started, it’s important to point out that there will be 2 configuration options for PowerPivot:

  • PowerPivot Configuration Tool
  • PowerPivot for SharePoint 2013 Configuration

PowerPivot Configuration Tools

You need to use PowerPivot for SharePoint 2013 Configuration to configure PowerPivot for SharePoint 2013.

  • Right-click PowerPivot for SharePoint 2013 Configuration, and select Run as administrator to launch PowerPivot for SharePoint 2013 Configuration wizard.PowerPivot for SharePoint Configuration Tool
  • Select Configure or Repair PowerPivot for SharePoint, and click OK.PowerPivot for SharePoint Configuration Tool
  • Wizard will run set of validation rules, and wait until it finishes the validation process.PowerPivot for SharePoint Configuration Tool
  • After the validation process, PowerPivot Configuration Tool will open up. This wizard has 2 panes. Left hand pane shows all the configuration tasks which needs to be addressed. Right hand side pane shows Parameters, Script and Output for the selected task in left hand pane. We need to make sure that all the tasks in the left hand side pane indicates a green flag to move forward.Configuration Tasks
  • Select the root task – Configure or Repair PowerPivot for SharePoint 2013, and provide parameter values for Default Account Username, Default Account Password and Database Server. Important: make sure to give the database server name for the Database Server parameter even if you install PowerPivot in a SharePoint Server.
    Setting the database server
  • Select the task Create PowerPivot Service Application from left hand side pane. This task make sure it automatically create the Service Application instance for PowerPivot. Provide meaningful parameter values to Service Application Name, Database Server and Database Name from the right hand pane.Creating PowerPivot Service Application
  • Select the task Deploy Web Application Solution from left hand side. Select the Web Application which you want to deploy the PowerPivot solution from the URL drop down. Through this wizard you can deploy PowerPivot solution only to a single Web Application. However, you can use SharePoint 2013 Central Administration web site if you want to deploy PowerPivot solution to any other Web Application.
    Setting the Web Application
  • Select the task Activate PowerPivot Feature in a Site Collection. Select the Site Collection in which you want to activate the PowerPivot Feature using Site URL drop down.
    Setting the Site Collection
  • Select the task Create Unattended Account for DataRefresh. This will create and configure a Target Application in Secure Store Service for PowerPivot data refresh. Enter meaningful parameter values for Target Application ID, Friendly Name for Target Application, Unattended Account User Name, Unattended Account Password and Site URL.
    Setting the Unattended Service Account
  • Click Validate to start validating the configuration details.
    Validating configuration settings
  • If there are no errors in the configuration parameters which we provided, validation success message will show up. Make sure all the configuration tasks in the left hand side pane are flagged with a green flag.
    Validating configuration settings
  • Click Run to start the configuration process.
  • Click Yes in the Warning.
    Running the configuration
  • It runs a set of 15 configuration steps and when it’s done it shows success message. Click OK and then Exit to quit the configuration wizard.Running the configuration
  • Open SharePoint 2013 Central Administration web site. Click Manage service applications link from Application Management section. Verify the creation of PowerPivot service application instance.
    PowerPivot Service Application instance

Let’s conclude this article now and look at the rest of the configuration tasks in upcoming articles.