Chapter 18. PowerPivot Issues
Because PowerPivot is an add-in to Excel with a complementary service in SharePoint, troubleshooting PowerPivot issues essentially comes down to troubleshooting Excel and SharePoint.
Those familiar with SharePoint will know that the Unified Logging Service (ULS) logs are the best place to look for issues with web front-end servers (WFEs), service problems, and so on.
Those familiar with troubleshooting databases will know that tracing the interaction with the database is the best place to look for data issues. In addition, because PowerPivot for Excel can serve as a sort of database, Microsoft has added tracing capabilities to Excel to aid with troubleshooting.
This chapter explores how to use these tools and also identifies some common PowerPivot issues.
Troubleshooting PowerPivot for Excel
Tracing the interaction between an application and a database is typically performed from a database server. However, because PowerPivot for Excel can connect to multiple data sources, including traditional and nontraditional ones, it would be useful to be able to trace this interaction from within Excel. Thankfully, Microsoft has included the ability to generate trace files from within the PowerPivot add-in for Excel.
Enabling Tracing from PowerPivot for Excel
Tracing is enabled from the Settings menu of the PowerPivot tab on the Excel ribbon toolbar, as shown in Figure 18.1.
Figure 18.1. Accessing the PowerPivot Settings menu.
Enabling a trace is as simple as opening the Settings menu and selecting the Enable PowerPivot Tracing for the Current Excel Session check box on the Support and Diagnostics tab. However, there’s a slight catch. For the trace to begin, the PowerPivot database engine, known as the VertiPaq engine, needs to be instantiated into memory. Because the Excel file could be used to simply view the existing data, the VertiPaq engine is not automatically loaded. As a result, if you enable tracing before the VertiPaq engine is instantiated, the check box will now state “Client Tracing will begin soon. Waiting for the Server to respond,” as shown in Figure 18.2.
Figure 18.2. Tracing enabled, but the VertiPaq engine is not loaded.
You will also notice that there is a Snapshot button, but it is disabled. The Snapshot button captures the current state of the database as part of the trace file. This can be useful for troubleshooting, and it is considered a best practice to snapshot at the beginning of the trace. However, because the VertiPaq engine has not started yet, this snapshot cannot be created yet. At this point, the user would have to click OK to close the Diagnostics screen, load the database into memory, and then return to this Diagnostics screen to take the snapshot. For this reason, it is simpler to load the database into memory before beginning the trace process.
To load the VertiPaq database engine into memory, just click the pivot table data region, which opens the PowerPivot field list. Alternatively, open the PowerPivot window. After doing either of those two actions, once tracing is enabled in the PowerPivot Settings screen, the trace filename will be displayed and the Snapshot button will be enabled and can be selected.
The trace file is placed onto the current user’s desktop by default, and there is no option to change that setting. This is likely not a problem because trace files are usually generated only to solve an immediate problem and the desktop is a convenient location for the file.
Disable Tracing from PowerPivot for Excel
Because trace files are verbose and can grow in size quickly, it is a good idea to stop the trace after the issue has been captured. To stop the trace from continuing to build, you should open the Settings window and clear the check box that was selected earlier. Note that in the initial release of PowerPivot (build 10.50.1600.1), there were reports of the trace file not stopping even after clearing the check box. In fact, if you clear the check box and close the Settings window, and then reopen the Settings window, you might notice that the check box is still checked. If this occurs, the tracing activity can only be stopped by closing the Excel file. This is believed to be a bug and may have been fixed by the time you read this, however you should watch out for this behavior.
In summary, when creating a trace file to troubleshoot PowerPivot for Excel, the following steps are recommended:
- Open the file in question.
- Either click a data region or open and then close the PowerPivot window to instantiate the VertiPaq engine in memory.
- Open the Settings window from the PowerPivot tab of the ribbon, enable the trace, and then take a snapshot.
- Close the Settings window and perform whatever analysis that you want to capture in the trace file.
- Close the Excel file to terminate the trace (Note that the Excel file does not need to be saved to save the trace file.)
- Analyze the trace file or provide it to someone who can.
Analyze Traces from PowerPivot for Excel
The trace file produced from Excel with PowerPivot is the same type of trace file produced by SQL Server Profiler. As a result, the best tool to analyze these trace files is SQL Server Profiler. However, only Profiler from SQL Server 2008 R2 can read these trace files because the trace definitions needed to be updated to support the VertiPaq engine.
Because the PowerPivot VertiPaq database engine is essentially an in-memory Analysis Services cube, the information in the trace files will be similar to what is produced when tracing Analysis Services queries, including connection information and the actual multidimensional expression (MDX) being used to query the data.
If a snapshot is created in the trace file, it will result in trace entries of “Server State Discover Begin,” followed by several “Server State Discover Data:” entries, and then end with a “Server State Discover End” entry, as shown in Figure 18.3.
Figure 18.3. Snapshot entries in a trace file.
Troubleshooting PowerPivot for SharePoint
PowerPivot applications that are loaded into SharePoint present a bigger challenge for troubleshooting because there are many layers involved including SQL Server, SharePoint Services, the SharePoint web application, the network layer, and so on. Each of these layers has different tools for troubleshooting.
After a PowerPivot application is uploaded into SharePoint, its data becomes part of the Analysis Services in VertiPaq Mode instance on the database server. As such, if users are having problems with applications in the PowerPivot Gallery on SharePoint, SQL Server Profiler should be used to generate trace files from the PowerPivot instance on the database server. Because the PowerPivot databases are actually Analysis Services databases, the trace files produced will be similar to traces produced against standard Analysis Services databases.
If users are having issues with SharePoint itself, the SharePoint Unified Logging Service (ULS) logs can prove useful for troubleshooting, because the SharePoint WFEs and application servers write to the ULS logs. This is the best way to troubleshoot issues with Excel Services and the PowerPivot System Service. The default location for the logs is C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions14LOGS.
Keep in mind that in a multiserver farm environment, logs from several servers might have to be analyzed to troubleshoot a particular problem.
The logs are not very readable in their raw format but can be opened with one of the ULS log viewers available on CodePlex. Figure 18.4 is an example using Stephan Gordon’s ULS Log Viewer and filtering a log file to only PowerPivot entries.
Figure 18.4. ULS Log Viewer for troubleshooting PowerPivot on SharePoint.
Log files by their very nature are verbose, and it can be difficult to locate the relevant issues. For this reason, it is important to narrow down when the error occurred or, if possible, to re-create the issue and then look at the most recent log files.
Common Issues
Some of the more common issues encountered during PowerPivot installation and usage are covered here. You can find many more issues and solutions online from Microsoft and several PowerPoint-specific websites, including the great PowerPivot Twins of Dave Wickert and Denny Lee at http://www.powerpivottwins.com.
Installing PowerPivot
• The PowerPivot for Excel add-in is available in both 32-bit and 64-bit versions. Ensure that the bit level of the add-in matches the bit level of the Excel installation. You can determine this by launching Excel and selecting File, Help and checking the version number under About Microsoft Excel.
• The PowerPivot for Excel add-in uses the Visual Studio Tools for Office (VSTO). These need to be installed as part of the Excel 2010 installation. If the VSTO were not installed with Excel 2010, Excel will need to be uninstalled and reinstalled with the VSTO option selected. Only then can the PowerPivot add-in be installed successfully into Excel.
• If installing PowerPivot for Excel on Windows XP, be sure to have SP3 installed and .NET3.5 SP1.
• Installing PowerPivot in an existing SharePoint farm is a complex process that can raise numerous issues. The best way to avoid these issues is to carefully follow a step-by-step installation guide. General installation steps are provided in Chapter 10, “PowerPivot for SharePoint.” If necessary, you can find more detailed installation steps at http://msdn.microsoft.com.
Using PowerPivot
• Relationships: PowerPivot can scan the tables and automatically create relationships. However, if the relationship is from one dimension to another (that is, not from a dimension to a fact table), it might not be automatically detected and you should manually create it.
• Hierarchies: The initial version of PowerPivot does not support hierarchies (also known as drill paths, such as from Year to Quarter to Month to Day).
• SharePoint list data in PowerPivot: Using a SharePoint list as a data feed is supported but requires the Data Services Update for .NET Framework 3.5 SP1.
• Excel Web Access warning in SharePoint: Because PowerPivot applications require Excel Services to query external data, the Excel Web Access warning will pop up frequently. This is designed behavior with Excel Services, but should be disabled to avoid annoying PowerPivot users. To disable, open Central Administration, select Manage Service Applications, and select the Excel service application (that is, ExcelServiceApp1). Then click Trusted File Locations, Trusted File Locations and select http://. On the Settings page, under the External Data section, clear the Warn on Refresh check box.
• User credential delegation error in SharePoint: Users familiar with the MOSS 2007 double-hop scenario that requires Kerberos might think that this is a Kerberos issue, but SharePoint 2010 uses the Claims to Windows Token service, and both Excel and PowerPivot are Claims to Windows Token aware. If getting an error regarding user credential delegation, this is likely due to the Claims to Windows Token service not running. To check this, go to Central Administration and navigate to System Settings, Manage Services on server. Select the correct server from the Server drop-down list and confirm that the Claims to Windows Token Service is started.
Summary
Troubleshooting PowerPivot essentially comes down to troubleshooting Excel and SharePoint. The ULS logs are the best place to start troubleshooting SharePoint issues. Data issues are best investigated by tracing the path to the data using SQL Server Profiler and the Excel trace capability provided with the PowerPivot add-in.