SharePoint Server 2013 SP1
SQL Server 2012 SP1
While getting Performance Point 2013 to use PowerPivot workbook as as data source I came across this issue where I was when giving correct connection string it was still unable to use the workbook. In logs, I was getting following error.
An error occurred connecting to this data source. Please check the data source for any unsaved changes and click on test data source button to confirm connection to the data source.
The Unattended Service Account "XXX" does not have access to the server specified by the data source connection string. Data source location: Data source name: New Data Source Exception details: Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException: A connection cannot be made. Ensure that the server is running. —> 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. at Microsoft.AnalysisServices.AdomdClient.XmlaClient.WCFConnect(String dataSource, String dataSourceVersion
To fix this install the "SQLSERVER2008_ASADOMD10.msi" from SQL Server 2008 R2 SP1 feature pack.
After installing above, I was still getting error in logs but this time it was access denied issue.
The Unattended Service Account "prolifixsvc_spua1" does not have access to the server specified by the data source connection string. Data source location: xxx/PerformancePointConnections/2_.000 Data source name: New Data Source Exception details: System.Net.WebException: The remote server returned an error: (401) Unauthorized. at System.Net.HttpWebRequest.GetResponse() at Microsoft.AnalysisServices.AdomdClient.XmlaClient.GetHttpResponse(String url, String soapAction, String user, String password) at Microsoft.AnalysisServices.AdomdClient.XmlaClient.OpenHttpConnection(ConnectionInfo connectionInfo) at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
I then stumbled upon this article Create a PowerPivot data connection (PerformancePoint Services in SharePoint Server 2013) where it stated further actions necessary to get this working.
EXCERPT from article
PerformancePoint Services in SharePoint Server 2013 supports PowerPivot models as a data source. You can use data in a PowerPivot model and build interactive dashboards using PerformancePoint Services features such as Key Performance Indicators (KPIs), Scorecards, Analytic Charts, Analytic Grids and Filters.
PerformancePoint Services accesses external data sources by using a delegated Windows identity. Consequently, external data sources must reside within the same domain as the SharePoint Server 2013 farm. If external data sources do not reside within the same domain, authentication to the external data sources will fail. For more information, see Planning considerations for services that access external data sources.
To use PowerPivot data in PerformancePoint Services 2013, do the following:
- Install ADOMD.net V11 on the server on which you are authoring the Dashboard in order to access a Power Pivot workbook as a data source.
- Edit the assembly direct to instruct PerformancePoint Services to use the correct version.
Using PowerPivot as a data source in SharePoint PerformancePoint Services 2013
PerformancePoint Services 2010 and 2013 are both compiled with Version 10 of the ADOMD.NET provider. To allow you to use newer provider versions from Analysis Services without having to upgrade your SharePoint version, an assembly redirect has been included with PerformancePoint Services 2013. This is particularly useful if your organization mixes and matches versions of SharePoint and Analysis Services. Perform the following steps on every server running PerformancePoint Services.
To Edit the Assembly Redirect
- On each server running PerformancePoint Services, navigate to Program FilesMicrosoft Office Servers15.0WebServicesPpsMonitoringServer.
- Open the web.config file. It should be located in the same directory as PerformancePointServerice.svc.
- At the end of the file, you should see an block contained within a block, like this:
- Change the values in the element from oldVersion="220.127.116.11" to oldVersion=10.0.0.0" and from newVersion="10.0.0.0" to newVersion="18.104.22.168".
This will allow PerformancePoint to leverage the GAC'd version 11 ADOMD.NET assembly when making calls to Analysis Services 2012 (and prior versions). It will also allow you to use the latest PowerPivot models as data sources in PerformancePoint Services.
Create a PowerPivot data source connection
To use Power Pivot as a data source within PerformancePoint Services dashboard, you must have PerformancePoint Services activated on a SharePoint Server 2013 farm and have Power Pivot for SharePoint 2013 installed. Once a Power Pivot model is created, it must be uploaded to a SharePoint Server site that has Power Pivot for SharePoint enabled.
You must apply the hotfix detailed in the Microsoft Knowledge Base article When you run a .NET Framework 2.0 based application, a System.AccessViolationException exception occurs, or a deadlock occurs on two threads in an application domain (Article ID 975954) in order to use Power Pivot as a data source in PerformancePoint Services.
You must have ADOMD.net V11 installed on the server on which you are authoring the Dashboard in order to access a Power Pivot workbook as a data source.
In PerformancePoint Services, the security setting for data sources is stored in each data source. By using PowerPivot as a data source, you can authenticate either using an unattended service account or a Per-user identity. Per-user identity requires Kerberos protocol.
You cannot authenticate to a PowerPivot data source by selecting the Unattended Service Account and add authenticated user name in connection string option.
To Create a PowerPivot data source connection
- Click the Create tab, and then click Data Source.
- In the Category pane of the Select a Data Source Template dialog box, click Multidimensional and then click Analysis Services. Click OK.
- In the left navigation pane (workspace browser), type the name of your data source.
- In the center pane, click the Editor tab. In the Connection Settings section, select Use the following connection.
- Type the connection string to the PowerPivot data source by using the following format: PROVIDER=MSOLAP;DATA SOURCE=http://contoso/Documents/PowerPivot_Sample.xlsx
- Select the cube from the drop-down menu. The cube name for a PowerPivot model will always be Sandbox.
- In the Data Source Settings section, select the method on which to authenticate to the data source.
- From the Formatting Dimension list, select desired dimension formatting needed for the report.
- From the Cache Lifetime list, type the refresh rate (in minutes) for the cache. Data from this data source will update at this interval.
- Click Test Connection to confirm that the connection is configured correctly.