Entries in SharePoint (11)


How Often are Thumbnail Images Refreshed in the Power Pivot Gallery?

Overview:  A brief discussion of how and when the thumbnail preview images in a Power Pivot Gallery in SharePoint get refreshed.

The Power Pivot Gallery is a specialized document library in SharePoint which utilizes Silverlight to render preview images of the report.  These previews are very helpful for users to quickly see if they have the correct report selected before executing it.


Ways to Refresh Thumbnail Images

Originally I had thought there would be a timer job which refreshes the thumbnails at a regular interval.  However, that is not the case.  There are 3 ways I’ve found to get thumbnails refreshed:

1.  Upload a new workbook.  The act of uploading a new workbook causes an event handler which will populate the thumbnail image for that workbook.

2.  Modify a workbook. The act of saving an existing workbook causes an event handler which will update the thumbnail image for that workbook.  Even if all you do is Edit Properties and then Save, that’s enough.

3.  Manually execute GallerySnapshot.exe.  This is a Windows service that runs on the app server where Excel Services is installed.  This exe gets called automatically when a file in the Power Pivot Gallery has been added or changed (an itemAdded or itemUpdated event, respectively, as mentioned in #1 and #2 above).  To run it manually, refer to this information:  Note that this service was called GetSnapshot.exe in SharePoint 2010, and has been renamed to GallerySnapshot.exe in SharePoint 2013.  The SharePoint 2013 GallerySnapshot.exe can be found at:  C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\BIN.

The takeaway:  Since the thumbnails only get regenerated in the above circumstances (and not at a regular interval), the preview images shouldn’t be thought of as something that is intended to coincide exactly with data updates.

Finding More Information

Technet – Refresh a Thumbnail Images

MSDN – Refreshing Power Pivot Gallery Thumbnails

Power Pivot Geek – General Problems with Gallery Snapshots Not Being Taken



Things to Consider When Planning a BI System / Report Conversion Effort

Overview:  It’s common for companies to consider moving from another BI platform to Microsoft BI in order to reduce licensing costs, to take additional advantage of familiar tools, or to expand the body of workers capable of developing and supporting the Microsoft BI system.  Following are some considerations when embarking on such a project.

Fact-Finding about Existing Reports

One of the first things you may wish to tackle is creating an inventory of reports to be converted.  Good thinking.  You may want to do this in parallel with some of the other items listed in the other sections.

  • Consolidation opportunities (for example, could 8 reports be consolidated to 1 with use of appropriate parameter selections?)
  • Level of complexity for each report (so that perhaps you can try to start with easier reports before tackling more difficult ones)
  • Priority level of each report (start with: high value, less effort)
  • Category of each report (such as by subject area, or by data source)
  • Data source(s) required for each report (and will all data sources exist going forward?)
  • Interactivity requirements for each report (such as hyperlinks, drill-down, drill-through)
  • Decision of which reporting tool is most suitable for each report (SSRS and Excel and Power View and PerformancePoint all have their particular strengths)
  • Any cosmetic changes, improvements, or fixes permitted during conversion (if any changes are permitted during conversion that is)
  • Export requirements (this may drive decision on which reporting tool in the Microsoft BI toolset is most appropriate)
  • Subscription and alerting requirements (this may drive decision on which reporting tool in the Microsoft BI toolset is most appropriate)

Planning - BI Change Management

This section includes some of the more broad items to consider which can affect the scope and timing of the project.

  • Identify business rules from the previous toolset's metadata layer (here we are looking for logic embedded in another reporting tool’s metadata layer that needs to be replicated in ETL or elsewhere in the BI system – reproducing a Business Object Universe or a Cognos Framework Manager Model can be done with the BI Semantic Model, but it could take you by surprise and expand the scope & the schedule)
  • Identify data movement and ETL processes used by the previous toolset (here we are looking for any data sources that will no longer exist, or need to be replicated in the new system)
  • Understand security constraints for report access and data access (obviously very important – how much work this is depends on the particulars of your environment and the role the old BI system may have played with regard to security)
  • Develop process for testing & validating converted reports (is a comparison of new results vs. old results acceptable for testing, and does the testing require sign-off from a functional user?)
  • Determine if an automated conversion tool will be used, or if all reports will be redesigned from scratch (this is a big decision – no tool can convert every single report 100% perfectly, but it might save some time especially for basic reports)
  • Identify needs and requirements for governance, auditing and change management (such as approvals required and version history to retain, among many other things)

Planning - BI Training and Documentation

You can’t spend too much time on training and documentation.  Trust me on this.

  • Determine how report developers will get trained on new set of tools (perhaps a combination of classroom training, on-the-job training, and assistance from a consulting firm that specializes in Microsoft BI)
  • Determine the extent of user training that will be optimal
  • Finalize naming conventions & reporting standards (to facilitate a consistent user experience & more efficient development experience)
  • Finalize documentation requirements (this may or may not change with the introduction of a new system)

Planning - BI Environment

These items are focused on the technical components of the BI infrastructure.

  • Planning for the new BI portal (this is the delivery piece – I can’t overstate how important it is to plan this out well)
    • Structure of the overall site (often this is a SharePoint BI Center which includes the enterprise site and team/departmental sites)
    • Structure of report libraries (often by subject, by user base, and/or by report type)
    • Structure for supporting objects (such as data sources, starter reports)
    • Structure for training and documentation materials (such as quick start guides, FAQs, how-to videos)
    • Need for custom metadata columns (such as purpose, description, report owner, who to contact for help)
  • Plan for the backend BI environment (this is the ETL and database piece – critical to ensure performance is optimal)
    • Server architecture (including whether you wish to build it yourself, take advantage of a reference architecture, or if an appliance is most suitable for the workload)
    • Software editions & versions (drives which capabilities are available)
    • On-premise vs. cloud vs. hybrid solution
  • Security for the BI environment, including access to ad-hoc reporting tools and data source connections (the structure of the new BI portal will often be very inter-mingled with security requirements)
  • Template(s) for standardization of report sets (to speed developer productivity and ensure a consistent user experience)
  • Preparation for ongoing monitoring of query loads from new system and resources required to continue to monitor
  • Plan for how Self-Service BI will complement the Corporate BI system, if applicable

ROI and Cost/Benefit of New BI System

This type of analysis can be difficult.  The organizations that do formal ROI and payback calculations already have formulas to do this, so I’ll just list a few things to consider with a BI system.

  • Costs:  Include things like hardware, software licensing, cloud services, consulting services, IT support time, developer time, administrator time, time invested in training, ongoing software maintenance costs, etc
  • Benefits: Include things like cost savings, improved productivity, increase in sales, increase in customer retention, new capabilities, increase in agility, support for strategic goals, faster access to information, broader access to information, etc

Strategies for User Adoption of New BI System

Here you want to consider things that will encourage adoption of the new system.  If a user has a poor initial experience with a system it can take a long time to win them over again.

  • Communication plan (such as timing of rollout, what is expected during testing cycles, schedule of reports to be converted, what users should do during the interim)
  • Support plan (first level support, second level, and if “power users” will get involved with support at all)
  • Training plan (if applicable – at a minimum, some helpful documentation is usually a great idea)

Hope this gives you some things to think about.



Displaying a Power View Report on a SharePoint Page

Overview: How to structure the URL for displaying a Power View report on a SharePoint 2013 page, including what parameters to pass in the URL.  This entry presumes the reader is familiar with SharePoint basics, including how to create a site page.

Ways to Render Power View Reports Within SharePoint

Because of its support for previewing the report via thumbnail images, the PowerPivot Gallery is a very common way of storing & accessing Power View reports:


However, perhaps you really would like to display a Power View report on a SharePoint page, possibly as part of a dashboard that combines various types of reports. 

Ways of rendering Power View reports in SharePoint 2013 include:

  • Via the PowerPivot Gallery (as shown above)
  • Via a regular document library (note: it won’t have the thumbnail previews like the PowerPivot Gallery does due to its integration with Silverlight)
  • Within a Page Viewer Web Part
  • Within a Silverlight Web Part (note: since word on the street is that Silverlight has a finite lifespan, this might not be your first choice)
  • Embedded within a PerformancePoint Dashboard (via the web page report type)
  • Within an Excel Web Access Web Part (applicable to Power View reports created with Excel 2013)

Note that even though Power View is part of the Reporting Services family, you cannot use the SQL Server Reporting Services Report Viewer web part – it can only display RDL files (generated from Reporting Services or Report Builder).  It cannot currently display RDLX files (generated from Power View for SharePoint).

The remainder if this blog entry focuses on the Page Viewer Web Part.

Steps to Use the Page Viewer Web Part

First, you’ll want to create a Site Page in SharePoint if you don’t have one already.  Once you are in Edit mode for your page, go to the Insert ribbon.  From there, choose Web Part on the ribbon.  Browse through the Media and Content category to find the Page Viewer.  Make sure you’ve clicked within the section you want the Power View report to be in, then click Add.


Then you’ll want to click the “open the tool pane” hyperlink to access its properties.


There are four sections of properties – only the first two sections are really important for displaying our Power View report.

Page Viewer Properties

  • Web Page radio button – Leave this selected.
  • Link – Specify the URL which will look something like this (below is more detail about constructing the URL):



Hint:  the only piece that is absolutely positively required is the URL as shown in the above Page Viewer section…but if you skip the rest of the settings, you’ll end up with awfully ugly output that isn’t readable at all:


Since we want it to be pretty, we’ll carry on with the rest of the properties…

Appearance Properties

  • Title – Change this from Page Viewer if you intend for the title to be shown (often the report itself will have a title, so often you can omit titling the web part itself too).
  • Height – Here’s where you are going to need to experiment somewhat.  The “Adjust height to fit zone” default doesn’t really do the trick from what I have seen.
  • Width – Chances are you can leave this as-is so it adjusts to fit the zone.
  • Chrome State – You’ll want to leave this as normal to ensure the report itself displays.
  • Chrome Type – Set this based on how you want to see the page.  If you are skipping the Title (i.e., if the report itself has a title), you might set the Chrome Type to have a border only, or perhaps none. 


Layout Properties

Nothing required to be changed here.


Advanced Properties

Nothing required to be changed here unless you’d like.


Constructing the URL for Display of the Power View Report

Now let’s take a step back and look at how the URL is constructed.  It uses the AdHocReportDesigner.aspx page, as well as several parameters, including:

  • RelativeReportURL – Points to the RDLX file
  • ViewMode – Presentation or Edit
  • ReportSection – Reference to which view will be displayed initially
  • Fit – True/False setting which allows for resizing
  • PreviewBar – True/False setting which controls if the menu across the top is shown or not

Here’s how the URL can be broken down into its components:


How the Power View Report Looks In the Page Viewer Web Part

How it ends up looking in SharePoint depends a lot on the parameter values you pass in via the URL, as well as the Height property you set in the properties.  Note that the pagination controls at the bottom left & bottom right are still active which is nice.  This means that even if you set the URL to jump to view 6, for instance, the end user can still paginate through and see all the views (i.e., pages) contained within the single Power View report.

Here’s an example where the Report Section is set to launch page 2 immediately, the View Mode is set to Presentation, and the Preview Bar is set to False within the URL string:


Here’s another example of the same report, except the Preview Bar is set to True within the URL string:


And here’s one more example of the same report, this time with the View Mode set to Edit:



SharePoint needs to be on-premises (as opposed to SharePoint Online) and configured in “SharePoint View Mode” rather than “Office Web Apps Server Mode.”  I’m guessing this is a temporary limitation around OWA support.

The Power View report needs to be created in SharePoint, as opposed to within an Excel 2013 file.  This requires the Reporting Services Add-In for SharePoint.

The browser must support the <iframe> element, which basically nests browsing context separately from the entire page.  All major modern browsers support iframes.

Finding More Information

Dan English Blog – Presenting Power View Reports to Users

Dan English Blog – URL Actions within Reporting Services Power View (RTM)

Himanshu Gupta Blog – Integrate Power View with SharePoint Using Web Parts

Office - Power View in Excel  in SharePoint Server or SharePoint Online in Office 365



Behavior of Power View in Excel 2013 within SharePoint 2013

Overview:  A quick post about what to expect when integrating Power View worksheets embedded in Excel 2013 into a SharePoint 2013 site.  Note that this behavior is a temporary situation…I’m guessing the SharePoint team at Microsoft is planning to getting full integration with Power View working.  Should just be a matter of time. 

Versions Applicable:  This behavior I’ve observed is applicable to Excel 2013 (i.e., the only version of Excel that supports embedding Power View worksheets) with SharePoint 2013.

First, let’s take a peek at the original Excel file.  This first screen shot is from the Excel application, before it’s been uploaded to SharePoint.  Note that there are 5 worksheets, 2 of which are Power View.


Next, let’s look at how the Excel file appears in SharePoint.  This next screen shot is from a PowerPivot Gallery in SharePoint 2013.  Note the display of an icon at the bottom right of each thumbnail preview – the tooltip for these reads “The document contains more items than can be displayed by the PowerPivot Gallery.” 


Next we’ll click on the Excel file to render it interactively using Excel Services.  Note that the Power View worksheets do appear along the bottom.  Note also that the interactivity functionality is present.  That’s the good news!  (Keep reading for the bad news…)


Next let’s download the workbook from SharePoint by doing a File > Save As > Download.  We are then presented with a dialog box stating “Features that we can’t show in the browser and interactive reports will be removed from the downloaded copy.  Continue with the download?”   Say Yes.



After selecting a location for the downloaded file, open it in Excel.  Guess what we see in the downloaded version of the file?  You got it…only 3 worksheets.  The 2 Power View worksheets were not retained because they’re not yet fully supported.


The moral of the story here?  Until we have a release that fully supports Power View embedded within Excel, hang onto your original Excel file!



Resolving Error Connecting Report Builder to a PowerPivot Data Source

Recently I saw the following error while working with Report Builder in SharePoint 2013:

“An error occurred while connecting to the data source.  Only the text-based query designer will be available.  The selected data extension DAX is not installed or cannot be loaded.  Verify that the selected data extension is installed on the client for local reports and on the report server for published reports.”


Huh?  I had tested the connection when I set it up, and knew it worked.  But no dice when using it for Report Builder.  Interestingly, my report executed but had empty results.

It turns out I had a connection with the wrong data source type.  Below is info re: setting up a connection in SharePoint for use with Report Builder.

Creating a Data Source for Report Builder Reports in SharePoint 2013

The first thing we need is a Report Data Source (RSDS) created so we can point to it (as a shared data source) when we create the report in Report Builder. 

The data connection can reside in a Data Connections library (if you prefer to centralize), or within the same library as the reports are stored (if you prefer to keep reports & data connections together).  For simplicity, the library shown below contains both reports and a data connection which is sales-specific.

Under Files on the ribbon, click the down arrow for New Document and choose Report Data Source.


Sidenote:  If you don’t see Report Data Source under the New Document menu, you’ll need to add the content type to the library first.  That’s a two-step process within the Library Settings…first, within the Advanced Settings, set “Allow Management of Content Types” to Yes…then back under General Settings choose “Add from existing content types” and select the various options that are related to BI data connections and/or reports (depending on what you need this library).

Set the various Data Source Properties.  Make sure to use the “Microsoft SQL Server Analysis Services” data source type, as shown here:


In the above screen shot, note the structure of the connection string.  For a bit more information about creating connections, see my blog entry titled “Comparison of Direct URL, BISM, and RSDS Data Connections for a Power View Report.”

The problem related to my original error was that within this RSDS connection, I had a data source configured with a type of “Microsoft BI Semantic Model for Power View” which – as the name implies – will work for Power View but not for Report Builder.  Problem solved when the type was changed to SSAS.

Here’s what the Report Data Source looks like when it’s been added to the document library:


Binding the RSDS Connection to a Report Builder report in SharePoint 2013

To associate the Report Data Connection it to a Report Builder report, click the ellipses next to an existing report name, then the ellipses one more time, then select Manage Data Sources.


Click the link on the name of your data source.


Ensure the “Shared data source” radio button is selected, and paste in the URL to the Report Data Source created previously.  Note this will have an RSDS file extension.




PowerPivot Data Refresh Options

Overview:  Discussion of the Data Refresh options available in SharePoint for updating data contained in PowerPivot.  If row-level security needs to be applied to the source data being extracted out of a source system into PowerPivot, then Option 1 won’t work.

Level:  101

Where Data Refresh Options Are Located

In the world of Self-Service BI, the producer of the data model (aka power user, author, workbook owner, data modeler) is who typically sets up and schedules the timing of data refreshes based on the business need for updated data. However, that doesn’t mean that the administrator of the system is completely uninvolved!

Data Refreshes are managed within the PowerPivot Gallery (a Silverlight-enabled SharePoint document library).  When a PowerPivot model has been uploaded to SharePoint, there are 3 icons visible in the PowerPivot Gallery.  The icon on the far right is used to “Manage Data Refresh.”  Utilizing SharePoint is the way to automate scheduled data refreshes for PowerPivot models.  This does require a special “PowerPivot for SharePoint” environment to be set up – a normal SharePoint document library won’t do the trick.


Data Refresh Options

Following are all of the options available in the “Manage Data Refresh” screen.  The middle screen shot – Credentials – is the focus of the following Option 1, 2, 3 sections.




Credentials Used to Refresh Data

For the refresh Credentials, there are 3 options to choose from.  Each option influences:

  • What account is executing the process on the server?
  • What account is being used to query the underlying data source? <—this affects how row level security is handled when the data is extracted

Option 1: Use the data refresh account configured by the administrator


Process executed by: 

  • The PowerPivot Unattended Account (predefined for the system by the administrator). 

Data queried by: 

  • Retains the credentials in place when the model was uploaded to SharePoint, as follows:
  • If Windows Authentication was used to initially populate the PowerPivot model:  It will continue to use Windows Authentication, in which case the PowerPivot Unattended Account is considered the “current user.”
  • If a specific User ID and Password (SQL Server authentication, for example) was used to initially populate the PowerPivot model:  It will continue to use that User ID and Password.


  • Simple for workbook owner to complete. 
  • No “special” User ID and Password needs to be shared with the workbook owner.
  • Should be few concerns with password changes, terminations & transfers, which simplifies some maintainability.


  • Not suitable when row level security is implemented on the source system being queried.  Since there can be only one PowerPivot Unattended Account per service application, to satisfy all possible data refresh scenarios for all PowerPivot workbooks, the PowerPivot Unattended Account may have broad or unrestricted permissions to the source data.  This may be far more liberal security than the original workbook author had & inadvertently result in the data refresh populating the workbook with too much data.


  • The PowerPivot Unattended Account needs read rights to all possible data sources used in all PowerPivot data refresh operations.
  • This user is also required to have read/write (Contribute) permissions to the PowerPivot workbook itself (preferably via permissions at the library level).

Disabling this option:

  • Option #1 can be disabled by the system administrator, in which case it would be greyed out for the workbook owner when defining the data refresh schedule. The option to disable it looks like this in Central Administration (PowerPivot Service Application Settings):


Option 2: Connect using the following Windows user credentials


Process executed by: 

  • The Windows user specified in the User Name field. 

Data queried by: 

  • The Windows user specified in the User Name field. This will override the credentials that were used when the model was uploaded to SharePoint.


  • If the workbook owner puts in their own credentials here, the likelihood of complications with source systems is minimal - i.e., the workbook owner already has read permissions to the source system.
  • Row level security on the source data can be retained (limited to the Windows ID specified – the assumption is made that the workbook is only being shared with users who have the same permissions to see the data).
  • The system administrator could provide workbook authors with a set of credentials to use that do not expire & does not have password changes.


  • It can be challenging to manage terminations, transfers, and password changes when individual user accounts are used.
  • Each time the User Password is required to be changed (for example, every 90 days), the workbook owner must go into the Data Refresh Schedule for each PowerPivot model they own and re-key the new password.


  • The Windows user specified needs read rights to the data source.
  • The Windows user specified is required to have read/write (Contribute) permissions to the PowerPivot workbook itself.

Disabling this option: 

  • Option #2 can be disabled by the system administrator, in which case it would be greyed out for the workbook owner when defining the data refresh schedule.  The option to disable it looks like this in Central Administration (PowerPivot Service Application Settings):


Option 3: Connect using the credentials saved in Secure Store Service


Process executed by: 

  • The user defined by the system administrator that corresponds to the ID provided in the field.  This is similar to Option 1 above, except that Option 3 is not limited to just one account per PowerPivot service application.

Data queried by: 

  • The user defined by the system administrator that corresponds to the ID provided in the field.  This will override the credentials that were used when the model was uploaded to SharePoint.


  • Should be very few or no concerns with password changes, terminations & transfers, which simplifies some maintainability.
  • All that is shared with the workbook owner is a Secure Store Service ID (such as 4672) rather than an actual User Name and Password.
  • Row level security on the source data can be retained (limited to the ID specified – the assumption is made that the workbook is only being shared with users who have the same permissions to see the data).


  • Which ID that’s shared with which set of users needs to be managed very carefully (i.e., if different IDs have varying levels of source system permissions).
  • The permissions assigned to the Secure Store Service ID may be far more liberal than the original workbook author had & inadvertently result in the data refresh populating the workbook with too much data.  This can be mitigated by creating and sharing IDs appropriately.


  • This user needs read rights to the data source.
  • This user is also required to have read/write (Contribute) permissions to the PowerPivot workbook itself.
  • The ID used cannot relate to the PowerPivot Unattended Account (i.e., the account used in Option 1).

Managing Data Refresh Failures

The lower section of the PowerPivot Management Dashboard (available only to administrators in SharePoint) displays recent data refresh failures.  This data is also available to be queried in the Management Data Sandbox.  Possible reasons for failure might be:

  • Read permissions to the data source specified do not exist for the user specified in the Data Refresh options
  • Contribute permissions to the PowerPivot workbook are not defined for the user specified (because the data refresh operation updates the PowerPivot file)
  • Source system is offline at the time the data refresh attempted to execute
  • Data source must exist on a server which can be accessed by the data refresh process (i.e., not on a user machine)
  • An individual account was used and the employee’s password expired or was changed
  • An individual account was used and the individual terminated employment upon which time their source system permissions were revoked
  • An individual account was used and the employee transferred to another department upon which time their source system permissions were revoked
  • If data refresh would force the workbook to exceed 2GB in size
  • If a custom driver or data provider is required to query a particular source system which hasn’t been installed on the PowerPivot server
  • If a 32-bit data provider was used when the workbook was set up, but the server has the 64-bit version
  • The workbook is checked out or is being actively edited
  • The workbook uploaded may be from an older version of PowerPivot than what is running on the server
  • Workbook author chose “After business hours” to refresh their workbook, but the system has no valid business hours defined (technically you can set the same start & end time to achieve no business hours, but that causes failures if users select “After business hours” in their refresh schedule – so setting a very small window is better)
  • An individual account is from another domain that isn’t trusted

When Data Refreshes are Executed

Although the Data Refresh options are set up by the workbook owner, the time the data refresh is executed is not entirely within their control.  The time a data refresh runs depends upon these things:

  • Earliest Start Time defined for the individual PowerPivot model. This is set up by the workbook owner after the PowerPivot model is uploaded to SharePoint.
  • Business Hours defined for the PowerPivot application.  This is set up by the administrator within the PowerPivot options in Central Administration.  These hours represent when priority is given to querying versus refreshing data. If you don’t really want to define business hours (perhaps you have an international business), then set this window to be extremely small.
  • How often the PowerPivot Refresh Timer Job is scheduled to run. This is managed by the administrator within the Timer Job Definitions in Central Administration.
  • How many refreshes are permitted to run concurrently.  This is managed by the administrator within the Analysis Services (PowerPivot) Service Settings in Central Administration.
  • Sufficient system resources are available to execute the job (for example, if the system is being taxed with a lot of queries, the data refresh process may need to wait).

Termination of a Data Refresh Schedule

By default, SharePoint will stop trying to execute Data Refreshes when one of the two happens:

  • Consecutive Failures.  Ten data refreshes in a row have failed, or
  • Inactive Workbooks.  No one has queried the PowerPivot model for ten data refresh cycles

The administrator may change these settings in the PowerPivot options in Central Administration.  You may want to consider changing the Inactive Workbooks setting – there are some workbooks that are only used minimally, but it’s still important for them to have current data when they are used.


Finding More Information

TechNet – Different Ways to Update Data in PowerPivot

TechNet – PowerPivot Data Refresh

MSDN – Everything You Always Wanted to Know About PowerPivot Data Refresh but Were Afraid to Ask

Analysis Services & PowerPivot Blog – PowerPivot Data Refresh

Kasper de Jonge’s Blog – PowerPivot Data Refresh, what settings to use and why

TechNet – Troubleshooting PowerPivot Data Refresh