Tag Archives: PowerPivot

Troubleshooting PowerPivot on SharePoint

I recently had a battle with a SharePoint 2010 farm and PowerPivot. Customer reported “weird things are going on”, and that was an understatement. This blog post is a transcript of the errors and what I did to resolve them. I hope this helps someone else in the future.

First off, I checked the server’s status (Central Administration > Manage Servers in this Farm) and saw that an upgrade was available. I tackled the upgrade first.

Now that I know my farm is happier, I continued dealing with PowerPivot. First thing I did was go to the service application to view if it was even running properly. When I clicked on the service application I got a nice generic error, but the ULS logs told me more

03/30/2012 12:37:07.88 w3wp.exe (0x2B10) 0x23C4 SharePoint Server Logging Correlation Data xmnv Medium Result=Id=OpenWorkbookAccessDenied; Microsoft.Office.Excel.Server.CalculationServer.FileOpenException: You do not have permissions to open this file. —> Microsoft.Office.Excel.Server.Host.HostFileException at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.GetSPFile(IClaimsIdentity claimsIdentity) at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.CheckForPermissions(IClaimsIdentity claimsIdentity) at Microsoft.Office.Excel.Server.MossHost.MossHostHelperMethods.<>c__DisplayClass4.<TryExecuteWithUserContext>b__2() at Microsoft.Office.Excel.Server.MossHost.MossHostHelperMethods.WithEnsureClaimsIdentitySetOnThread(IClaimsIdentity claimsIdentity, MethodToRun action) at Microsoft.Office.Excel.Server.MossHost.MossHostHelperMethods.TryExecuteWithUserConte 6a104842-30b5-4ca9-b27a-e1f454312700
03/30/2012 12:37:07.88 w3wp.exe (0x2B10) 0x23C4 Excel Services Application Excel Calculation Services ech0 Medium ExcelService.PostProcessRequest: web method: OpenWorkbook, got exception Id=OpenWorkbookAccessDenied; Microsoft.Office.Excel.Server.CalculationServer.FileOpenException: You do not have permissions to open this file. —> Microsoft.Office.Excel.Server.Host.HostFileException at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.GetSPFile(IClaimsIdentity claimsIdentity) at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.CheckForPermissions(IClaimsIdentity claimsIdentity) at Microsoft.Office.Excel.Server.MossHost.MossHostHelperMethods.<>c__DisplayClass4.<TryExecuteWithUserContext>b__2() at Microsoft.Office.Excel.Server.MossHost.MossHostHelperMethods.WithEnsureClaimsIdentitySetOnThread(IClaimsIdentity claimsIdentity, MethodToRun action) at Microsoft.Office.E… 6a104842-30b5-4ca9-b27a-e1f454312700
03/30/2012 12:37:07.88* w3wp.exe (0x2B10) 0x23C4 Excel Services Application Excel Calculation Services ech0 Medium …xcel.Server.MossHost.MossHostHelperMethods.TryExecuteWithUserContext(IIdentity userIdentity, Action`1 method) at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.Init(Uri uri, Guid requestSiteId, IIdentity currentIdentity, IExcelServerDocumentContext documentContext, FileLoaderHostInfo& outFileLoaderHostInfo) at Microsoft.Office.Excel.Server.CalculationServer.SharePointFileLoader.Init(Uri uri, Guid requestSiteId, IExcelServerDocumentContext documentContext) — End of inner exception stack trace — at Microsoft.Office.Excel.Server.CalculationServer.SharePointFileLoader.Init(Uri uri, Guid requestSiteId, IExcelServerDocumentContext documentContext) at Microsoft.Office.Excel.Server.CalculationServer.FileLoader.CreateFromTrustedLocationAndInit(Uri uri, TrustedLoca… 6a104842-30b5-4ca9-b27a-e1f454312700
03/30/2012 12:37:07.88* w3wp.exe (0x2B10) 0x23C4 Excel Services Application Excel Calculation Services ech0 Medium …tion trustedLocationSettings, Guid requestSiteId, IExcelServerDocumentContext documentContext) at Microsoft.Office.Excel.Server.CalculationServer.BaseWorkbookManager.GetBaseWorkbookAndMarkUsedAsync(AsyncHandler`1 callback, Object userState, Request request, Uri uri, Boolean newWorkbook, Boolean useCollection, Boolean loadedOnDemand) at Microsoft.Office.Excel.Server.CalculationServer.Session.OpenWorkbookAsync(AsyncHandler`1 callback, Object userState, Request request, Uri url, Boolean loadedOnDemand) at Microsoft.Office.Excel.Server.CalculationServer.Operations.OpenWorkbookOperation.StartExecution() at Microsoft.Office.Excel.Server.CalculationServer.Operations.Operation.RunOperationAsync() at Microsoft.Office.Excel.Server.CalculationServer.Operations.OperationSite.Prepar… 6a104842-30b5-4ca9-b27a-e1f454312700
03/30/2012 12:37:07.88* w3wp.exe (0x2B10) 0x23C4 Excel Services Application Excel Calculation Services ech0 Medium …eComplete(PrepareAsyncArgs args) 6a104842-30b5-4ca9-b27a-e1f454312700
03/30/2012 12:37:07.88 w3wp.exe (0x2B10) 0x23C4 Excel Services Application Excel Calculation Services 2018 Information Failed to load ‘http://SHAREPOINT:25557/1033/Server%20Health.xlsx&#8217; with error: ‘Access is denied to the specified file.’. [Session: 1.V21.67MiloLbpn+Z+LMu/ertG90.5.en-US5.en-US73.+0300#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.c63405b0-88db-466c-a8d9-f2d11f672c6d1.N User: 0#.w|domain\sp_admin] 6a104842-30b5-4ca9-b27a-e1f454312700
03/30/2012 12:37:07.88 w3wp.exe (0x2B10) 0x23C4 Excel Services Application Excel Calculation Services ech8 Medium ExcelService.PostProcessRequest: Excel Server Recoverable Exception: Id=OpenWorkbookAccessDenied; Microsoft.Office.Excel.Server.CalculationServer.FileOpenException: You do not have permissions to open this file. —> Microsoft.Office.Excel.Server.Host.HostFileException at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.GetSPFile(IClaimsIdentity claimsIdentity) at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.CheckForPermissions(IClaimsIdentity claimsIdentity) at Microsoft.Office.Excel.Server.MossHost.MossHostHelperMethods.<>c__DisplayClass4.<TryExecuteWithUserContext>b__2() at Microsoft.Office.Excel.Server.MossHost.MossHostHelperMethods.WithEnsureClaimsIdentitySetOnThread(IClaimsIdentity claimsIdentity, MethodToRun action) at Microsoft.Office.Excel… 6a104842-30b5-4ca9-b27a-e1f454312700
03/30/2012 12:37:07.88* w3wp.exe (0x2B10) 0x23C4 Excel Services Application Excel Calculation Services ech8 Medium ….Server.MossHost.MossHostHelperMethods.TryExecuteWithUserContext(IIdentity userIdentity, Action`1 method) at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.Init(Uri uri, Guid requestSiteId, IIdentity currentIdentity, IExcelServerDocumentContext documentContext, FileLoaderHostInfo& outFileLoaderHostInfo) at Microsoft.Office.Excel.Server.CalculationServer.SharePointFileLoader.Init(Uri uri, Guid requestSiteId, IExcelServerDocumentContext documentContext) — End of inner exception stack trace — at Microsoft.Office.Excel.Server.CalculationServer.SharePointFileLoader.Init(Uri uri, Guid requestSiteId, IExcelServerDocumentContext documentContext) at Microsoft.Office.Excel.Server.CalculationServer.FileLoader.CreateFromTrustedLocationAndInit(Uri uri, TrustedLocation… 6a104842-30b5-4ca9-b27a-e1f454312700
03/30/2012 12:37:07.88* w3wp.exe (0x2B10) 0x23C4 Excel Services Application Excel Calculation Services ech8 Medium … trustedLocationSettings, Guid requestSiteId, IExcelServerDocumentContext documentContext) at Microsoft.Office.Excel.Server.CalculationServer.BaseWorkbookManager.GetBaseWorkbookAndMarkUsedAsync(AsyncHandler`1 callback, Object userState, Request request, Uri uri, Boolean newWorkbook, Boolean useCollection, Boolean loadedOnDemand) at Microsoft.Office.Excel.Server.CalculationServer.Session.OpenWorkbookAsync(AsyncHandler`1 callback, Object userState, Request request, Uri url, Boolean loadedOnDemand) at Microsoft.Office.Excel.Server.CalculationServer.Operations.OpenWorkbookOperation.StartExecution() at Microsoft.Office.Excel.Server.CalculationServer.Operations.Operation.RunOperationAsync() at Microsoft.Office.Excel.Server.CalculationServer.Operations.OperationSite.PrepareCom… 6a104842-30b5-4ca9-b27a-e1f454312700
03/30/2012 12:37:07.88* w3wp.exe (0x2B10) 0x23C4 Excel Services Application Excel Calculation Services ech8 Medium …plete(PrepareAsyncArgs args) 6a104842-30b5-4ca9-b27a-e1f454312700

I couldn’t find much on this error online so I went ahead and deleted the service application. I then tried to add a new one back but received another error

Could not load type ‘Microsoft.AnalysisServices.SharePoint.Integration.GeminiCreate’

I wasn’t pleased to see this one. I checked the solutions (Central Admin > System Settings > Manage farm solution) and confirmed both powerpivotfarm.wsp and powerpivotwebapp.wsp were both deployed. I retracted both solutions and then repaired the SQL installation for POWERPIVOT instance (Control Panel > Programs and Features, select SQL Server. During the repair process, select the instance for POWERPIVOT).

I deployed the two solutions, and had to deploy the powerpivotwebapp.wsp a second time to include Central Admin. Tried to add the service application again and it worked.

I logged into the PowerPivot site and was able to open up files and use the PowerPivot functionality.

Advertisements

More iPad support for SharePoint: Dashboards, PowerPivot, & Excel!

It’s nice to see Microsoft playing nice with others. The cumulative update 2011-12 (CU Dec 2011, or Feb 2012) includes improvements around the iPad. Specifically, users can now access dashboards, Excel services, and PowerPivot services via their iPad!

For complete details, check out http://technet.microsoft.com/en-us/library/hh697482.aspx. There’s a great video on that page that walks through how well it works.

Taking my PowerPivot walk through I posted about previously, I accessed it via my iPad and found it worked very well. Check out the screen shots below.

Initial screen after opening the document

Same experience and messages as using on a PC

Quickly and easily selecting a slicer, see the data update

Going deeper into the data, fast and easy!

Tried opening in Excel, fair enough!

PowerPivot Walkthrough Using SharePoint Lists

Before we can just walk through using PowerPivot and SharePoint, we need to make sure things are in order. Some prerequisites

I am using the Northwind sample data provided by Chris Beckett, you can download it here. If you don’t want to load up the Northwind sample, target a list or two of data you want to work with. Having two which are related (using a lookup) is preferred.

First thing, open Excel and create a new workbook. Click PowerPivot tab then PowerPivot Window.

You’ll get a nice empty window. Now, you would think there would be a simple button that says Import SharePoint List. But there isn’t. Actually, what we’re going to use is the list data feed option.

If PowerPivot is installed on your farm correctly, you can simply go to your SharePoint list, click the List tab in the ribbon, then click the Export to Data Feed button.

This will prompt you to open or download a file. Save the file locally, then open it. When you open it, PowerPivot will ask where you want to import the feed. Select Book1, or another book name if you’re using an existing book.

That will then send you to the Table Import Wizard in PowerPivot.

  • On the first page, name it something meaningful, like the list name you’re working with.
  • Click Next.
  • On the next page you can preview and filter your data, if you don’t want to pull it all in, you can filter out data. Only filter out data that your users will absolutely not want (like from 5 years ago).
  • Click Finish.

That’ll take a little time, but it’ll load up your list into PowerPivot. Continue the same steps for all lists you want to import.

Once you have your lists in, we need to setup a relationship between them. In the PowerPivot window, click the Design tab, then click Manage Relationships.

You should get an empty window with a button Create. Go ahead and click Create. In my two tables, Customers and Orders, there is a standard SharePoint lookup between the two. The Orders list has a lookup into the Customer list. When the data is imported into PowerPivot, it saves the ID of the Customer record, so the field doesn’t have the name of the Customer, like SharePoint does.

In the Create Relationship window, first specify the daughter list and field, and then specify the parent list and field.

Click Create, then click Close on the Manage Relationships window.

That does it! You have SharePoint lists in PowerPivot. Up until this point, we’ve been very specific with SharePoint lists. If you are working with different data sources (SQL Database, Excel file, etc.) you can import them and then follow along for the rest of this post.

Now let’s get some of this data into Excel. Click the Excel button in the PowerPivot Window Title bar.

In the PowerPivot ribbon, click the PivotTable button, and select PivotTable.

Select Existing Worksheet when prompted. Click OK.

Your Excel window now has a whole lot of more going on.

The right pane is all PowerPivot related, and creates the report and slicers.

From here, I want to create a report that lists all Customers with total number of orders, with a slicer for selecting order date’s month, quarter and year; and a slicer for country, and a slicer for region.

In the top window on the right, the PowerPivot Field List, check off all the fields you want to display. I suggest showing only one for now. I am going to select CompanyName. You’ll see that the data is populated and the field was added to the Row Labels at the bottom.

To create slicers, now we can drag the fields down into the either the Slicers Vertical or Slicers Horizontal boxes. Let’s start with the Customer fields. So I am going to drag Country and Region down into the Vertical box. Your new slicers will appear on the left.

Go ahead and try them. Click a value in either slicer box. When I click Canada, two things happen. The Region slicer and the list of Customers both filter. Pretty cool eh?

Click the Clear Filter at any point to reset the filter.

To get the date slicer in there, we need to manipulate the data a little. If you were to drag the Order Date field into the Horizontal box, it would load up every date. That doesn’t help. So now we’ll go back to PowerPivot and create our Month, Quarter and Year values. To go back to PowerPivot, click the PowerPivot Window button in the ribbon.

On my Orders list, I am going to scroll all the way to the right and click Add Column. In the box where the value would normally be, we can enter in a formula. So I am going to enter =MONTH([OrderDate]), which will get me the month value. Right click the column header to rename to OrderMonth. Getting the Quarter is a little more difficult, we’re going to use the formula =INT((([OrderMonth]-1)/3)+1). And I’ll throw in another one for the Year, using =YEAR([OrderDate]).

Now let’s jump back into Excel. Excel will automatically prompt you to Refresh the data, go ahead and click Refresh.

And now let’s drag down the three new fields into the Horizontal box. As you do, the slicers will appear above our list.

And finally, lets add the count of orders, drag the ID field of the Orders list to the Values box. Then click on the field in the box and select Summarize By > Count.

You can rename the field as well by selecting Edit Measure from the previous menu.

Now you can use any combination of slicers to analyze your data.

And now let’s add a chart!

In Excel, move to the right a little, and click the cell where you want the chart to start, top left.

Click the PivotTable button in the ribbon, then click PivotChart. Select Existing Worksheet, click OK.

I want to see total sales based on country and selected time frame. Drag Country into the Axis Fields box, drag the ID of the Orders list into the Values box, and again change it from Sum to Count. We now have a little bar chart.

By default, none of the slicers affect this chart. I now want the date slicers to filter the data in this chart. To add slicers to a chart, right click the slicer and select PivotTable Connections. You’ll see two pivot tables (real unique), but since there’s only two here, check off the 2nd one.

Do the same for each date slicer. Now when you filter your dates, your customer list and the chart will update.

And let’s do the same steps above, but I’m going to throw in one more chart to view order totals based on year, by selected country.

Now when I select a Country or Region, the company list and By Year chart updates. Selecting a Date updates Company and By Country chart.

Now that we have it ready to go, browse to a PowerPivot Gallery on SharePoint and upload this document. Once uploaded, you can now browse and interact with your PowerPivot within SharePoint.

PowerPivot in SharePoint