PowerShell Scheduled Data Refresh – Call to Excel Services Returned an error

In SharePoint Power Pivot data Automatic refresh from excel services, I got an error saying that “Power Pivot Scheduled Data Refresh – Call to Excel Services Returned an error” as shown the image below

By digging into the issue I have found that I don’t have permissions for Power Pivot service. To fix the issue we have to add the permissions for Power Pivot. To add we need to give the account running Power Pivot Full Control to the Web app under the Policy for Web Application.

Hope this helps.

“The maximum number of allowed sessions per user has been exceeded. This operation cannot be completed” Error in SharePoint Excel services

While working with SharePoint Excel chart and graphs I got an error saying that “The maximum number of allowed sessions per user has been exceeded. This operation cannot be completed” as shown the image below.

We can get this error maximum sessions limit exceeded. By increasing the Maximum Session Per user limit, we can fix this error. We can increase the Maximum Sessions Per User by using the steps below,

Navigate to Application Management page and click on Manage Service Application link in Service Applications section. 

Click on Excel Service application in the list of service applications

 In Manage Excel Services Application page, click on “Global Settings” link

In Excel Services Application Settings page, “Session Management” section, enter Maximum sessions Per User. By default it is 25.  

Sharing SQL Server Analysis Service connection using SharePoint 2013 Excel Services

In my previous post, I have explained sharing SQL Server Data source connections in SharePoint 2013. In this post we can use SQL Analysis service connection. We can create Analysis service connection as shown the steps below,

In Excel, Data tab,  click on From Other Sources and select From Analysis Services option.

In Connect to Analysis services, in the Server name box, we have to specify the SQL Server name to get the SQL Server data. Here Iam entering as “.” to connect the local. We have to specify the Log on credentials in the credentials section. To use Windows Authentication, we have to select Use Windows Authentication or to choose SQL Server authentication Select SQL Server Authentication and have to enter the credentials. Click on Next.

“In the Select the database that contains the data that you want” list; we have to select the database that we want to use. If we don’t want to select any database or if there is no databases, uncheck Connect to a specific table.

On Save Data Connection and File and Finish page, we have to enter File Name, Description, Friendly Name, and Search Keywords boxes. In the Authentication settings, we can choose the type of authentication. Click on finish.

Connection will be saved in default location. Once we save the connection, we can upload the connection file Data connections library as shown in my previous post.

Sharing SQL Server Data source connection using SharePoint 2013 Excel Services

We can connect different data sources using excel services includes SharePoint list, access DB, Windows azure etc... All the data connections in excel will support in excel services. We can refresh the data in excel service reports and dashboards and also supports SQL server tables and Analysis services cubes and OLE DB data providers. We can also save the connections to excel services trusted data connection library. It will help us to access the data sources easily without knowing server names and DB names. Excel service workbooks uses in a trusted data library to easy access the current information.

We can use Excel to create and share connections with database. When uploading a data connection to excel services trusted data connection library, data connections will be available to use data models, reports and score cards. Depending on the data source, data will be refreshed easily in excel service workbooks.

To create a SQL server tabular data connection by using excel, we have to follow the steps below,

In Excel Data tab, click on From Other Sources in Get External Data group and we have to select SQL Server.

In Connect to Database Server page, Server name box, we have to specify the SQL Server name to get the SQL Server data. Here Iam entering as “.” to connect the local. We have to specify the Log on credentials in the credentials section. To use Windows Authentication, we have to select Use Windows Authentication or to choose SQL Server authentication Select SQL Server Authentication and have to enter the credentials. Click on Next.

“In the Select the database that contains the data that you want” list, we have to select the database that we want to use, In the Select Database and table page, To create new connection uses single table, we have to select Connect to specific table and we need to select the table to use. By selecting Enable selection of multiple tables, we can select multiple tables.

On Save Data Connection and File and Finish page, we have to enter File Name, Description, Friendly Name, and Search Keywords boxes. In the Authentication settings, we can choose the type of authentication. Click on finish.

On Import Data page, Click on Only Create Connection and Click on OK. 

To Upload Data connections to SharePoint,

Navigate to BI center site and Open Data Connections Library.Click on New Item and click on Browse to choose the file.

If we save the data connections in Default location, in default location, click on libraries, Documents and navigate to My Data Sources.

Select the ODC file that we wanted to upload and click on Open.

In the Add a document dialog box we have to select Content Type as Office Data Connection File. Need to provide details for Name, Title and Description and Keywords, click on Save. 

New data connection will be added as shown below.

Wow that’s big workbook. Unfortunately we can’t open a workbook larger than 10MB. You’ll need to open this in Excel - Large workbook error in SharePoint Excel Services

While working with excel workbooks in SharePoint, I got an error saying that, “Wow that’s big workbook. Unfortunately we can’t open a workbook larger than 10MB. You’ll need to open this in Excel.”

After checking issues into deep, found that the reason for this error is the size of the workbook to be opened is larger than the maximum size of workbook. We can configure the maximum size in the excel services settings.

To change the value, Navigate to Application Management, select Manage Service Applications link.

Select Excel Service application to configure to configure maximum value.

Click on Trusted File Locations in Manage excel service applications page

We’ll be navigated to Excel Services Application Trusted File Locations. Click on specific address we need to configure maximum size.

In Excel Services Application Edit Trusted File Location page, Workbook properties section, we have to specify the Maximum Workbook Size as per our need, click on OK 

button. 

“An error occurred during an attempt to establish connection to external data source…” error in SharePoint Excel service PivotTable manual refresh

In SharePoint server 2013, to refresh the data manually in PivotTables need to go for Data -> Refresh All Connections as shown in the image below.

While refreshing data I got an error like “An error occurred during an attempt to establish connection to external data source…” 

After checking the error in Logs I have find that the issue with identity currently set. When we access to refresh the data in PivotTables Claims to windows token service (C2WTS) will be used. If C2WTS is running with domain account that domain account needs permissions on the SharePoint server where it is running.

C2WTS domain account must be in SharePoint server’s local administration group where it is running and it has to impersonate the client after the authentication, Works to log an as service and it needs to be act as part of the operating system.

To access the local security policy of the server, Go to Administrative Tools -> Local Security Policy -> Local Policies -> User Rights Assignments

By adding the permissions to the account that running Claims to windows token service, we need to restart the service on each server it is running. 

Add charts to SharePoint site using excel services

By using SharePoint excel services we can show excel worksheets, graphs and charts in the SharePoint site. We can create the charts and graphs by using excel power pivot. To create a chart in excel workbook, take an excel workbook with sample data to create a chart.

Select Insert tab on the top, select Power Pivot button on the top left as shown below.

We can see a window asking to create a Pivot Table. Click on OK button.

Name the Pivot table.

Select the fields to create power pivot report and drag the fields in to Sum values column and Rows column to create a report.

Once done with the report, Select Insert tab and click on the charts to insert the pie chart in excel sheet.

After creating the chart, Name the Chart. Here it is “Chart 1”  

Save the excel sheet and upload it to SharePoint library.

Add Excel Web Access web part in the SharePoint page to show the chart.

Click on the link “Click here to open the tool pane” to specify the worksheet and chart details to the web part.

Add Excel worksheet in Workbook and Chart Name in Named Item in the Workbook display settings of the Excel Web Access web part.

Click on “Apply” button. We can see the Chart in SharePoint page once done with applying the settings.  Click on “OK” button to save the settings. Click on Save Page to save the page.

We have the options to change the view by changing the drop down option on the right side.

Configuring Excel Services in SharePoint 2013 - II

In my earlier post, I have mentioned configuring excel services Global settings and Trusted file location. In this post we can see trusted data providers, trusted data connection libraries, user defined function assemblies and data model settings.

Trusted data providers works as data providers for excel services to access the data. A data provider is a protocol that combined with database type for accessing data. By default excel services will not access data without trusted data providers. Excel services will contain the data providers as entries. We have to add additional data providers as per our requirement.

By clicking on the trusted data providers link in Manage Excel Service Application Page, we will navigate to Excel Services Application Trusted Data Providers page. We can see a list of trusted data providers from excel service. To add new data provider, click on “Add Trusted Data Provider” link on the top of the page.

On the Excel Service Application Add Trusted Data Provider page, Provider section, we need to add the provider ID in Provider ID text box. In the Provider Type, we have to select the one of the following options, OLE DB, ODBC, ODBC DSN. OLE DB option will access the data by using Object Linking and Embedding. By selecting ODBC option, data will be accessed using Open Database Connectivity. By selecting the ODBC DSN, data will be accessed using Open Database Connectivity with Data Source Name.

To Configure trusted data connection libraries for excel services click on Trusted Data Connection Libraries in Manage Excel Service Applications page. Trusted data connection libraries are the data connection libraries for trust configuration in SharePoint 2013 excel services. All the Data connections for excel services will stored in trusted data connection library.

To create new trusted data connection in SharePoint 2013 excel, Click on Add Trusted Data Connection Library in Excel Service Application Trusted data Connection Libraries page.

We will navigate to Excel Service Application Add Trusted Data Connection Library page. In the Location section, we need to enter the document library URL that permit to access the excel service application. We have to add the description of the trusted data connection in the description text box.

To add the User defined assemblies for the excel service, click on User Defined Function Assemblies link in Manage Excel Services Application page. Click on Add User-Defined Function Assembly button on the page to add new User-Defined Assembly.

In the Excel Service Application Add User-Defined Function Assembly page, Assembly details section, enter the Assembly strong name or path of the assembly contains the user-defined function. Depending on the Assembly type you have entered, select the option Global Assembly Cache or File path. Check Assembly enabled check box to allow the user-defined function assembly to be located and used by Excel calculation service. Unchecking this option, disables the option assembly will not be added for user defined function.

In SharePoint 2013 Excel Services, We can specify the SQL server instances for Analysis services to process the data models. This will provides the backend services for Excel service to load query and refresh the data model. To add the instance of the SSAS, we must have to install the Power Pivot for SharePoint mode. We can add new data model in SharePoint excel service in Excel Services Application Data Model Settings page. By clicking on the Add Server button, we will navigate to Excel Services Application Add Server page to add new data model.

In the Excel Services Application Add Server page, Server section, we have to enter the instance of SQL Server 2012 SP1 Analysis Services (SSAS) Server Name to add the new data model. Description is optional that helps to describe the data model setting.

We can deploy the excel services in three scenarios, Enterprise, Medium or small department and custom. While configuring the enterprise scenario we have to check that, don’t configure support for User defined functions, do not enable workbooks to use embedded data connections or direct access of external data. Need to limit the usage of data connection libraries from external Data source access. For small department deployment, we have to enable the trust for all the locations using the department members. Need to enable the trust children option for all the trusted sites and directories. If any chance to get problems from a file location, just restrict the access. In custom deployment, enable the excel services to open the large work books, we need to configure the long session time-out and large data caches that there is change to delay in the connection. Here we don’t enable the trusted child for trusted location and need to create a single trusted location.