Showing posts with label Excel Services. Show all posts
Showing posts with label Excel Services. Show all posts

Wednesday, April 1, 2015

What is PowerPivot for SharePoint? Part 2.

What is PowerPivot for SharePoint? Part 2.

Now that we have a PowerPivot workbook in the Excel Rich Client, we are ready to publish that workbook to SharePoint.

In "What is Power Pivot for SharePoint? Part 1" I discussed the Data Model and how it is a database inside of Excel.  When you publish/upload a regular Excel workbook to SharePoint, that file gets chunked up into blobs and stored in the Content Database.  With a PowerPivot workbook the Excel workbook also gets stored in the Content Database, BUT the Data Model gets created and stored (after the initial (successful) refresh in browser) on the server running POWERPIVOT.

Note: In SharePoint 2010, the Analysis Service POWERPIVOT instance is going to be on a SharePoint server in the farm.  In SharePoint 2013, we recommend using a SQL Server off the farm.  Step by step install instructions here.

I am now going to touch on the three refreshes of PowerPivot 2013 and how they work.

1. Browser Refresh

When you open workbook and click a slicer, you are merely accessing the Data Model.  In other words, (in this example) the PowerPivot workbook stored in a SharePoint Report Library is going our to the SQL Server running PowerPivot > Databases > PowerPivot20Test20Workbook and is pulling whatever data (fresh or stale) that is currently in that cube.

If you login to SQL Management Studio > Analysis Services > Servername\POWERPIVOT > Databases you will see the Data Model (in this 

Troubleshooting:  I have seen the refresh in browser fail periodically.  This was caused by not having the correct ASOLEDB and ADOMD.Net drivers (located here: for SQL 2008 R2 SP1 & SQL 2012 SP1) on the SharePoint Servers running Excel Services and/or the Claims to Windows Token Service not running on these machines/lacking proper permissions.

2. Scheduled Data Refresh

In SharePoint 2010, running the Scheduled Data Refresh is the only way to get fresh data into the PowerPivot workbook (this is designed to run once daily during "after business hours").  A timer job runs, the Data Model is updated with fresh data and workbook is republished back to SharePoint.  When you open the workbook and click on a slicer, the Browser Refresh occurs a you will see data from the time the Scheduled Data Refresh ran (data from the time the Scheduled Data Refresh ran that night).  In 2013, you are not 100% dependent on the "Scheduled Data Refresh" (if your backend data source accepts Windows Credentials; SQL or Analysis Services) to see fresh data.  In 2013, you can use the "Interactive Data Refresh" which I will touch on next.  If you want to refresh from a data source that does not accept Windows Credentials (Oracle, IBM (in some cases) SQL, etc.)  you will need to use the Scheduled Data Refresh in both versions of SharePoint (2010 & 2013).


For this functionality to be present is SharePoint 2013, you need to install the PowerPivot.msi on a SharePoint server.
Troubleshooting: For detailed instructions on how to set this up please follow this article. For help pulling from a data source that does not accept Windows Credentials, please see our blog.

 3. Interactive Data Refresh

The "Interactive Data Refresh" is only available in SharePoint 2013.  The "Interactive Data Refresh" passes the SharePoint user's credentials all the way to the backend and pulls real-time data into a PowerPivot workbook.  To do this, you need to choose Data > Refresh All Connections


What is PowerPivot for SharePoint? Part 1.

What is PowerPivot for SharePoint? Part 1.

Part 1: PowerPivot for Excel

PowerPivot for SharePoint is nothing without the Excel Rich Client and the PowerPivot Add-in.  Pre-Excel 2013, the PowerPivot Add-in was a separate COM Add-in that you had to download.  In Excel 2013, the PowerPivot Add-in ships with the product.
Before you can do anything in SharePoint, you need to create a PowerPivot workbook in Excel.  To do this, open thePowerPivot tab choose Manage > From Database (choose your datasource, servername and database name).


You (potentially) will be pulling in a large amount of data into Excel.  I have seen people with Excel (PowerPivot) workbooks 1GB in size (which is huge since .xlsx (Open Office XML) files are zipped).  This large amount of data is essentially a database and you can see this in the workbook.  To view the Data Model, rename workbook file extension (.xlsx to .zip), open the workbook > xl > model >

After you have pulled this data (essentially a database) into the PowerPivot environment, Excel can point to this database and build a PivotTable off it.  In the PowerPivot environment choose PivotTable > PivotTable

Excel is now pointing at the database (Data Model) embedded inside itself.  You can see for yourself via Excel > Data Connections > Properties.  In SharePoint (when we get there), we will call this the "Refresh in Browser".

Keep in mind there is still the PowerPivot Connection that can also refresh.  In SharePoint, we will call this the "Scheduled Data Refresh".  You can view this connection in Excel > PowerPivot > Manage > Existing Connections (choose the PowerPivot Data Connection) > Edit > Advanced.

There is still one more refresh (in SharePoint 2013 & SQL 2012 SP1) called the "Interactive Data Refresh".  I will touch on that later in the "What is PowerPivot for SharePoint?  Part 2".
At this point, we should have a beautiful Excel (PowerPivot) workbook that has a Pivot Table and Slicers and looks something like the below sample.

Now that we have a beautiful Excel (PowerPivot) workbook that functions wonderfully in the Excel Rich Client, we are ready to publish this to SharePoint!

Friday, March 27, 2015

Verifying the Excel Services Configuration for PowerPivot in SharePoint 2013

Verifying the Excel Services Configuration for PowerPivot in SharePoint 2013

In SharePoint 2013, Excel Services natively supports core PowerPivot functionality. This is an important improvement over previous releases. Without requiring an extra installation of PowerPivot components in the farm, users can interact with workbook data models in the browser. You only need to register a SQL Server Analysis Services (SSAS) server in the Excel Services configuration, as in the following screenshot, so that Excel Services can load and query the data models.
The specified SSAS server can reside anywhere in the local TCP/IP network but must exist in the same Active Directory forest as the SharePoint farm. The Excel Services service account must be granted server administrator permissions in the Analysis Services configuration. Furthermore, for SharePoint 2013 Preview, the specified server must run SQL Server 2012 SP1 CTP3 Analysis Services and the server must operate in SharePoint deployment mode. Note that SQL Server 2012 RTM Analysis Services and previous versions are not sufficient because these versions don’t support the new level of integration between Excel Services and Analysis Services.
When you register a SSAS server in Excel Services, it might take two or three minutes for the configuration changes to take effect. Of course, you can make the system apply the changes immediately by restarting Internet Information Services (IIS) using IISReset /NoForce or by restarting the Excel Services application in SharePoint Central Administration. I prefer IISReset because it’s uncomplicated. But what if Excel Services continues to tell you that data models cannot be loaded, as in the next screenshot? By following a few basic steps, you can quickly examine the Excel Services configuration and identify possible root causes.
Note   In order to verify that Excel Services can load a workbook data model, upload and open a sample workbook in the browser, and then click on a slicer. Excel Services typically shows cached data without loading the data model when opening a workbook. Clicking on a slicer requires Excel Services to update the data cache, which in turn requires actually loading the data model.
As always in SharePoint, start troubleshooting by checking the ULS logs. You can use UlsViewer, which is a handy tool available at, or use the Get-SPLogEvent cmdlet in the SharePoint Management Shell, or simply open the ULS log files in Notepad. If you use the Get-SPLogEvent cmdlet, start the Management Shell as Administrator and then set a filter for events from “Excel Services Application”, Category “Data Model”, and perhaps specify other criteria as well, such as the verbosity level, to narrow down the list of events that you must investigate. For example, I used the command Get-SPLogEvent | ?{$_.Area -eq "Excel Services Application" -And $_.Category -eq "Data Model" -And $_.Level -eq "Monitorable" } | Format-List to produce the output illustrated in the following screenshot.
The message in my example contains the error hint “Can't operate on empty server pool.” which means that Excel Services doesn’t have a server to load the data model. The solution is straightforward. Just register a SSAS server in the Data Model Settings of Excel Services and the server pool is no longer empty. Make sure you specify the SSAS server name correctly. Don’t forget to include the instance name, which is typically called POWERPIVOT. So, a server named AS2012SP1 running Analysis Services in SharePoint deployment mode would be specified as AS2012SP1\POWERPIVOT.
Note   As a best practice, do not use the label “localhost” or “.” in the SSAS server name. These labels refer to the local computer, which means that each Excel Services instance in a multi-machine farm would interpret the SSAS server name differently. Always specify the actual server name.
If you happen to mistype the server name, Excel Services writes a different error message to the ULS log, shown in the next screenshot. The message would state “There are no servers available or actively being initialized” which means that Excel Services knows about a SSAS server, but this server can currently not be located. The command Get-SPLogEvent | ?{$_.Area -eq "Excel Services Application" -And $_.Category -eq "Data Model" } | Format-List would include this ULS log entry in its results.
It is important to point out that a mistyped server name is only one of many possible causes of this error message. The server name isn’t necessarily incorrect. The server might just be down or rebooting for some reason. This could be a temporary situation that doesn’t require any configuration adjustments. Of course, it could also be a more permanent issue on the SSAS server, such as an incorrectly configured firewall blocking incoming connections. See if the ULS log contains additional hints. In the following screenshot, the error message states that the SQL Browser service is inaccessible. Opening the firewall solves this problem. For details about firewall configurations for Analysis Services, check out the article “Configure the Windows Firewall to Allow Analysis Services Access” at
Having fixed the firewall configuration, the next common source of problems revolves around the service account permissions in Analysis Services. As mentioned, the Excel Services service account must be granted Analysis Services administrator permissions. If this is not the case, you can find the following error message in the ULS Log.
The message “Check Administrator Access (AS2012SP1\POWERPIVOT): Fail.” means that the Excel Services service account does not have the required permissions on the Analysis Services instance AS2012SP1\POWERPIVOT. If you grant the permissions in SQL Server Management Studio (SSMS), the administrator access check will pass, as hopefully will do all the other Analysis Services server checks that Excel Services performs at regular intervals. Here’s a successful sequence of all the server checks taken from the ULS log on a properly configured and functioning SharePoint 2013 application server:
  1. Checking Server Configuration (AS2012SP1\POWERPIVOT) ...
  2. Check Administrator Access (AS2012SP1\POWERPIVOT): Pass.
  3. Check Server Version (AS2012SP1\POWERPIVOT): Pass (11.0.2809.24 >= 11.0.2800.0).
  4. Check Deployment Mode (AS2012SP1\POWERPIVOT): Pass.
  5. Check Server Configuration (AS2012SP1\POWERPIVOT): Pass.
The ULS logs should give you all the information you need to verify the Excel Services configuration for PowerPivot, but investigating ULS logs on busy SharePoint servers can be tedious and time consuming. Running an ad-hoc PowerShell script in the SharePoint Management Shell is often an efficient and more interesting alternative. In my next blog post, I’m going to introduce such a PowerShell script to verify the SharePoint 2013 configuration for all shared services that might want to consume a workbook as a data source. Stay tuned!

How to export a schema.xml file for a list from a SharePoint site

How to export a schema.xml file for a list from a SharePoint site In sharepoint, we can retrieve the XML schema of any list by using ...