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.

7May/140

Article – Associating PowerPivot SQL Server Analysis Services server instance with Excel Services

Posted by Joy

In my previous article, I demonstrated how to install SQL Server PowerPivot for SharePoint. After installing PowerPivot for SharePoint, you need to configure it before start using it. One of the configurations is associating PowerPivot SQL Server Analysis Services server instance with the Excel Services.

This article guides you through the process of associating PowerPivot SQL Server Analysis Services server instance with Excel Services.

  • Open SharePoint 2013 Central Administration web site. Select the option Manage service applications from the Application Management section.Application Management
  • Click Excel Services service application name to navigate to Manage Excel Services Application page.Manage service applications
  • Select Data Model Settings option from the Manage Excel Services Application page to navigate to Excel Services Application data Model Settings page.Manage Excel Services
  • Click Add Server from Excel Services Application Data Model Settings page to associate PowerPivot SQL Server Analysis Services instance with Excel Services.Excel Services Data Model Settings
  • Enter the name of the server in the Server Name field including PowerPivot SQL Server Analysis Services instance name and click OK.Add Server
  • Now you can see PowerPivot SQL Server Analysis Services server is associated with Excel Services.
    Add Server

Now you can move on to other configuration steps Smile

2Apr/140

Article – Installing SQL Server PowerPivot for SharePoint 2013

Posted by Joy

Self-service BI was introduced with SQL Server 2008 R2 with the introduction of PowerPivot. PowerPivot for Excel and PowerPivot for SharePoint became very popular among business users and it have them the opportunity to work with large amount of data within Excel with the ability of slicing and dicing data for detailed analysis.

This article guides you through the process of installing PowerPivot for SharePoint.

PowerPivot is not part of SharePoint or Excel and it is part of SQL Server. You need to have SQL Server Enterprise or Business Intelligence edition to use PowerPivot with Excel and SharePoint. You can install PowerPivot in either a SharePoint Server, Existing SQL Server or separate/ dedicated server. If you install PowerPivot on a SharePoint server, to use PowerPivot for SharePoint, you don’t have to install PowerPivot for SharePoint Add-In. If you install on an existing SQL Server or a dedicated server, you need to install PowerPivot for SharePoint Add-In in all the SharePoint Servers to get full feature set.

  • Mount SQL Server installation media. I used SQL Server 2012 with Service Pack 1 for my installation.
  • Double click setup.exe and select Installation from left navigation options. Select New SQL Server stand-alone installation or add features to an existing installation option.SQL Server Installation Center
  • Installation will execute set of Setup Support Rules and click OK if you don’t see any errors.Setup Support Rules
  • Installation will check for any available product updates and include if any. Click Next to install any updates available.Product UpdateProduct Update
  • Installation will execute another set of Setup Support Rules and click Next if there are no errors.
    Setup Support Rules
  • Select the option Perform a new installation of SQL Server 2012 and click Next.
    Installation Type
  • Enter the Product Key and click Next.
  • Accept the License Terms and click Next.
  • Select the option SQL Server PowerPivot for SharePoint and click Next.Setup Role
  • Click Next from the Feature Selection page.Feature Selection
  • Installation will execute set of Installation Rules. Click Next if there are no errors.Installation Rules
  • Accepts the default values in the Instance Configuration page and click Next.Instance Configuration
  • Click Next from the Disk Space Requirements page.
  • Provide a Service Account for the SQL Server Analysis Services in the Server Configuration page and click Next.Server Configuration
  • Click Add Current User from the Analysis Services Configuration page to grant administrative rights to the current user for Analysis Services.
    Analysis Services Configuration
  • Click Next from the Error Reporting page.
  • Installation will execute set of Installation Configuration Rules. Click Next if there are no errors.Installation Configuration Rules
  • Click Install from the Ready to Install page to start the installation.
  • Installation will take few minutes and Complete page will appear when installation completes. Click Close from the Complete page.

Next, I will guide you through the next steps of configuring SQL Server PowerPivot for SharePoint.