Tag: powerpivot

Posted on

PowerPivot, PowerView and SharePoint – Snapshots

Gotta love cross team interaction sometimes. In trying to get PowerPivot and PowerView to work on SP2013, I ran into many many issues.  One of which has no items on the internet when you search for it!  So here's my steps to resolve getting this whole thing to work right. 

  1. The first step is to install the PowerPivot Add-In.  The gem of an installer simply copies a bunch of files to the program files directory.  The real work in the configuration tool
  2. Run the PowerPivot Configuraton Tool – this copies all the wonderful items to the SharePoint Root.  Some of this is done via solutions, other by direct file copy. Some important files include:
    1. BINGallerySnapshot.exe
    2. LayoutsPowerPivot directory (specifically the ASRGLoader.htm file)

Once this is done, theoretically you should be good to go, but alas…shit happens.

The biggest issue is the snapshots of the files. I have seen many of these errors before, but this time things were a bit different.

When it comes to the actual GallerySnapshot.exe tool, it is a very interesting beast, some details are found here:

 And for all other issues, you have this great post:

Which one part that I had not seen before was the Group Policy being applied for the Trusted sites.  Because I had set this up in my image, the code could not set its own policy and would error.  This post was helpful to find that issue (which was to remove the policy):

For my particular situation, I had what this poor soul had:

This error is "NullReferenceException in Microsoft.AnalysisServices.SPAddin.ReportGallery.SnapshotHandler.RegisterGlobalExtensionHandlers()"  Ugly.  So of course, I went in and found the assembly just as he did and sure enough, the section is missing in my web.config file of my BI Center site.  What does it need to look like?  Here ya go (answer in bold):

<?xml version="1.0" encoding="UTF-8"
standalone="yes"?>
<configuration>
  <configSections>
    <sectionGroup name="SharePoint">
      <section name="SafeControls"
type="Microsoft.SharePoint.ApplicationRuntime.SafeControlsConfigurationHandler,
Microsoft.SharePoint, Version=15.0.0.0, Culture=neutral,
PublicKeyToken=71e9bce111e9429c" />
      <section name="RuntimeFilter"
type="System.Configuration.SingleTagSectionHandler, System,
Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
      <section name="WebPartLimits"
type="System.Configuration.SingleTagSectionHandler, System,
Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
      <section name="WebPartCache"
type="System.Configuration.SingleTagSectionHandler, System, Version=4.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089" />
      <section name="WebPartWorkItem"
type="System.Configuration.SingleTagSectionHandler, System,
Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
      <section name="WebPartControls"
type="System.Configuration.SingleTagSectionHandler, System,
Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
      <section name="SafeMode"
type="Microsoft.SharePoint.ApplicationRuntime.SafeModeConfigurationHandler,
Microsoft.SharePoint, Version=15.0.0.0, Culture=neutral,
PublicKeyToken=71e9bce111e9429c" />
      <section name="MergedActions"
type="System.Configuration.SingleTagSectionHandler, System,
Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
      <section
name="PeoplePickerWildcards"
type="System.Configuration.NameValueSectionHandler, System,
Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
      <section name="WorkflowServices"
type="Microsoft.SharePoint.Workflow.ServiceConfigurationSection,
Microsoft.SharePoint, Version=15.0.0.0, Culture=neutral,
PublicKeyToken=71e9bce111e9429c" />
      <section name="BlobCache"
type="System.Configuration.SingleTagSectionHandler, System,
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
/>
      <section name="OutputCacheProfiles"
type="System.Configuration.SingleTagSectionHandler, System,
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
/>
      <section name="ObjectCache"
type="System.Configuration.SingleTagSectionHandler, System,
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
/>
      <section name="MediaAssets"
type="System.Configuration.SingleTagSectionHandler, System,
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
/>
      <section
name="ApplicationAuthentication"
type="Microsoft.SharePoint.IdentityModel.ApplicationAuthenticationConfigurationSection,
Microsoft.SharePoint.IdentityModel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c"
/>

      <section
name="CustomCaptureSection"
type="Microsoft.AnalysisServices.SPAddin.ReportGallery.CustomCaptureSection"/>
    </sectionGroup>
    <sectionGroup
name="System.Workflow.ComponentModel.WorkflowCompiler"
type="System.Workflow.ComponentModel.Compiler.WorkflowCompilerConfigurationSectionGroup,
System.Workflow.ComponentModel, Version=4.0.0.0, Culture=neutral,
PublicKeyToken=31bf3856ad364e35">
      <section name="authorizedTypes"
type="System.Workflow.ComponentModel.Compiler.AuthorizedTypesSectionHandler,
System.Workflow.ComponentModel, Version=4.0.0.0, Culture=neutral,
PublicKeyToken=31bf3856ad364e35" />
      <section name="authorizedRuleTypes"
type="System.Workflow.ComponentModel.Compiler.AuthorizedTypesSectionHandler,
System.Workflow.ComponentModel, Version=4.0.0.0, Culture=neutral,
PublicKeyToken=31bf3856ad364e35" />
    </sectionGroup>
    <sectionGroup
name="microsoft.sharepoint.client">
      <section name="serverRuntime"
type="Microsoft.SharePoint.Client.ClientServiceServerRuntimeSection,
Microsoft.SharePoint.Client.ServerRuntime, Version=15.0.0.0, Culture=neutral,
PublicKeyToken=71e9bce111e9429c" />
    </sectionGroup>
    <sectionGroup name="ReportingServices">
      <section name="DataExtensions"
type="System.Configuration.DictionarySectionHandler" />
    </sectionGroup>
    <sectionGroup name="Bpm">
      <section name="FCODaoProviders"
type="System.Configuration.DictionarySectionHandler" />
    </sectionGroup>
    <sectionGroup name="reportserver">
      <section name="redirection" type="Microsoft.ReportingServices.SharePoint.Configuration.RSRedirectConfigSection,
RSSharePointSoapProxy, Version=11.0.0.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91" />
    </sectionGroup>
    <section name="microsoft.identityModel"
type="Microsoft.IdentityModel.Configuration.MicrosoftIdentityModelSection,
Microsoft.IdentityModel, Version=3.5.0.0, Culture=neutral,
PublicKeyToken=31bf3856ad364e35" />
  </configSections>
  <microsoft.sharepoint.client>
    <serverRuntime>
      <hostTypes>
        <add
type="Microsoft.SharePoint.Client.SPClientServiceHost,
Microsoft.SharePoint, Version=15.0.0.0, Culture=neutral,
PublicKeyToken=71e9bce111e9429c" />
      </hostTypes>
    </serverRuntime>
  </microsoft.sharepoint.client>
  <SharePoint>
<CustomCaptureSection createProcessMethodForSnapShot="0"
snapshotCaptureTimeoutSecods="300" maxSnapshotsCount="19"
>
<Handlers>
</Handlers>
</CustomCaptureSection>
…
</configuration>

After adding these entries…your PowerPivot and PowerView snapshots will work.

Enjoy,
Chris

 

Posted on

PowerPivot and Excel Services

While working with getting PowerPivot and Excel Services working, I ran into this very annoying error:

ServerSession.ProcessServerSessionException: An exception during ExecuteWebMethod has occurred for server: http://servername:32843/811dd9f4d4ae48779f1dc7a03ba5d555/ExcelService*.asmx, method: GetHealthScore, ex: Microsoft.Office.Excel.Server.CalculationServer.Proxy.ServerSessionException: An error has occurred. —> System.ServiceModel.FaultException`1[System.ServiceModel.ExceptionDetail]: The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040) (Fault Detail is equal to An ExceptionDetail, likely created by IncludeExceptionDetailInFaults=true, whose value is: System.IO.FileLoadException: The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)    at System.Reflection…    

Nothing in the event log…only this error with no reference to the assembly name or anything…annoying.  I found this nice post about debugging these type of errors:

http://blogs.msdn.com/b/suzcook/archive/2003/05/29/57120.aspx

You must download the SDK to get the tool:

http://www.microsoft.com/downloads/details.aspx?familyid=6B6C21D2-2006-4AFA-9702-529FA782D63B&displaylang=en

After installing, I wasn't seeing any binding errors.  In the documentation, you're suppose to restart the services that are using .NET after setting the logging.  I rebooted the server and the error didn't return…weird.

Chris

Posted on

PowerPivot and Claims based authentication

Unfortunately, I have NOT gotten this to work.  Only on a web application that has "Classic Authentication" set has it worked.  If you setup a web application to have claims based auth with both a "Forms" and a "Windows" login capability, the PowerPivot code will fail in its login method:

<nativehr>0x80070005</nativehr><nativestack>OWSSVR.DLL: (unresolved symbol, module offset=00000000000BB1EC) at 0x000007FEEFEFB1EC mscorwks.dll: (unresolved symbol, module offset=00000000002CF777) at 0x000007FEF62BF777 Microsoft.SharePoint.Library.ni.dll: (unresolved symbol, module offset=00000000000E7BAA) at 0x000007FEF23A7BAA Microsoft.SharePoint.ni.dll: (unresolved symbol, module offset=0000000001A5D823) at 0x000007FEEC87D823 Microsoft.SharePoint.ni.dll: (unresolved symbol, module offset=0000000001AD201F) at 0x000007FEEC8F201F </nativestack>Access denied.

Only way to get this to work is to reset the web application to "Classic Authentication" and then the code works.  This is with the latest cumulative updates applied.

Another blog post by Denny mentions a bit more into this, but the code should work as it is calling the object model to simply update the workbook in the list on the site 🙁

Chris

Posted on

PowerPivot and SharePoint Farms

When installing PowerPivot in your farm, you will have to manually add the following line to your web.config SafeControls section of EVERY front end server, you will also have to add this back everytime you make a change via the Web Application settings page in Central Administration:

<SafeControl Src="~/_layouts/powerpivot/*" IncludeSubFolders="True" Safe="True" AllowRemoteDesigner="True" SafeAgainstScript="True" />

If you don't do this, then you will get this error:

“The referenced file ‘/_layouts/PowerPivot/ReportGalleryView.ascx’ is not allowed on this page."

This should have been done in the Solution install, but they missed that step.  Hopefully next version of the .wsp will contain the addition

Special thanks to PowerPivotGeek.com for the helpful hint

Chris

Posted on

PowerPivot for End Users Course

After the last two weeks of finishing this course, it is about to be published!  Here's the outline…

  • PowerPivot Introduction
    • Install PowerPivot for Excel
    • Explore new Excel 2010 Features
  • PowerPivot Data Sources
    • Load Data From SQL Server
    • Load Data From Text Files
    • Load Data via Copy and Paste
    • Load Data via Data Feeds
    • Load Data via Reporting Services
    • Cleaning data in PowerPivot
    • Create Relationship between Data Sources
  • PowerPivot Functions
    • Create a Time Table for future labs
    • Use DATE Function
    • Use DATEVALUE Function
    • Use the DAY, MONTH, YEAR, HOUR, MINUTE, SECOND Functions
    • Use EDATE Function
    • Use EOMONTH Function
    • Use NOW and TODAY Functions
    • Use TIME and TIMEVALUE Functions
    • Use WEEKDAY and WEEKNUM Functions
    • Use FIRST* and LAST* Functions
    • Use DATE* Functions
    • Use STARTOF* and ENDOF* Functions
    • Use CLOSING* and OPENING* Functions
    • Use LASTOF* and FIRST* Functions
    • Use PREVIOUS* and NEXT* Functions
    • Use AVERAGE* Functions
    • Use COUNT* Functions
    • Use MAX* and MIN* Functions
    • Use ALL, ALLEXCEPT and ALLNOBLANKROW
    • Use CALCULATE, CALCULATETABLE
    • Use DISTINCT and VALUES
    • Use EARLIER and EARLIEST
    • Use FILTER
    • Use RELATED
    • Use Logical Functions (AND, FALSE, IF, IFERROR, NOT, OR, TRUE)
    • Use Information Functions (ISBLANK, ISERROR, ISLOGICAL, ISNONTEXT, ISNUMBER, ISTEXT)
    • Use TEXT Functions
  • Building Reports using PowerPivot
    • Use PivotCharts
    • Use Slicers
    • Use Sparklines
    • PivotTable Models
  • PowerPivot for SharePoint 2010
    • Install PowerPivot for SharePoint 2010
    • Configure PowerPivot for SharePoint
    • Publish to SharePoint 2010
    • Configure PowerPivot for SharePoint 2010

Chris

Posted on

Installing PowerPivot and Server 2008 R2 with SharePoint

There's a trick to getting this to work.  You can't use ANY 32bit applications!  You MUST install all 64bit applications.  this includes:

  • Office 2010 – you must use the ISO version and install from the x64 directory (do not auto install, it will do the 32bit version)
  • SharePoint Designer 2010 
  • Visio 2010
  • PowerPivot x64

Chris

Posted on

Installing SQL Server 2008 R2 PowerPivot – SharePoint 2010

I wasn't a fan of the first installer, and am still not a fan of the current installer.  It works great if you are doing a new Farm, but unforunately, when you already have a Farm, it doesn't work right.

The various errors include the fact that you have to know what permissions need to be assigned to the Analysis Services instance, what account to install under and a not so simple thing in that the ConfigurationFile.ini on the "install" step is WRONG!

It still thinks you are trying to do a new Farm install and randomly picks a port for the Central Administration site!  Hmm…who codes this stuff???  You have to edit the file to set the port properly for Central Administration.  You should also set the TCP and Netbios setting to "1" while your are at it.

Chris