PowerPivot using large SSRS ATOM feed fix

I’ve talked on, blogged about, and Tweeted about PowerPivot for awhile now. By itself, PowerPivot is pretty cool, but add it on to SharePoint and you have the Voltron of BI solutions.

PowerPivot Voltron

I can pull from Oracle, Excel, a CSV, and DB2 all in the same file. CRAZY! What’s even cooler is that I can pull a SSRS ATOM feed in to PowerPivot . . . sometimes.

By default – if you’re using SQL 2012 Integrated mode – the largest SSRS ATOM feed you can pull is 110 MB. We have some pretty ridiculously large reports at Trek and a user was attempting to pull one of these monsters in to PowerPivot and it was choking. To make matters worse, PowerPivot was just throwing a 500 error (Unknown error). Not really helpful…

I opened a MS Case and began troubleshooting. We went back and forth and back and forth (6 weeks!), but we finally found the solution. So to get PowerPivot using large SSRS ATOM feeds do the following:

  1. Open the file Client.Config on your front end servers. The File is located here: C:/Program Files/Common Files/microsoft shared/Web Server Extensions/15/WebClients/Reporting/client.config
  2. Search for “httpStreaming” and “httpsStreaming”
  3. Within both these Bindings, change the value of the following – this will increase the Data Size from 110 Mb to 1.1 Gb:
    1. maxReceivedMessageSize from “115343360” to “1153433600″
    2. maxStringContentLength from “115343360” to “1153433600″
    3. maxArrayLength from “115343360” to “1153433600″
  4. Save the File
  5. Do an IISRESET across all SharePoint Servers.

Happy PowerPivot’ing.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s