How to resolve "The requested service, 'http://:port#//ReportingWebService.svc' could not be activated" error when creating Power View report

After we installed and configured PowerView for SharePoint 2013, we have constantly run into stability issue when creating Power View report. The symptom is users will randomly get the following error message when click “Create Power View Report” as indicated in the screenshot.

I’ve tried the following two different ways people mentioned but not be able to resolve the issues.

1. Check Rssrvpolicy.config file mentioned by Mike Yin
2. Change the Enable 32-Bit Applications property of the IIS application pool for the SharePoint web application to False published in PrologikaForums (Note this will stop app pool and make it unstable!)

If you check the ULS log, it will show the following error:

“System.ServiceModel.ServiceActivationException: The requested service, 'http://spsbx15:32843/586f5b2b0ac34da6b57e9dd817fa06d3/ReportingWebService.svc' could not be activated. See the server's diagnostic trace logs for more information.”

This seems to point to the reporting web service issue from this log. After review the SQL Server Reporting Services 2012 SharePoint integrated mode Endpoints architecture, I found the solution is to restart the app pool that is running report service. Here isthe details procedure to found the app pool that is running report service.

1. First run the PowerShell command to identify reporting service reporting service process ID.

PS C:\> Get-SPRSServiceApplication



Name                   Id                                   UEAccountName
----                   --                                   -------------
Reporting Web Service 586f5b2b-0ac3-4da6-b57e-9dd817fa06d3  

You could verify the report web service status using the ID but remove all the "-" inside
to construct the following URL. You will see nothing will be displayed. The port # can be found in steps below.

http://spsbx15:32843/586f5b2b0ac34da6b57e9dd817fa06d3/ReportingWebService.svc

  2.  Browse IIS manager "SharePoint Web Services" with this ID and click "Advanced Settings..." to
identify application pool ID that runs reporting service. In this case, the ID is "a106ffe3b2e74f5b9b77bd1f4312129f". You can idntify the port # for the service. In this case, the port # is "32843".

3. Browse IIS manager "Application Pool", select the pool with the ID ("a106ffe3b2e74f5b9b77bd1f4312129f")  from step #2. 

You could verify this is the application pool runs report service by right click "View Applications". You should see the applications it runs.

4. Restart the application pool and view the report service URL. You will see response displayed.

 

Now you have not only the way to fix "ReportingWebService.svc could not be activated" issue, but also have a way to verify the report service availability though report service URL. We might need to configure a monitor to constantly check this web servcie and automatically restart the application pool whence it was not reponse. 

We are continue working with Microsoft to resolve the stability of the report service stability issue.

Delete Powerview reports, original file, and site collection does not remove Microsoft Analysis Server database entries

After upload Powerview file into SharePoint 2013 PowerPivit gallery, you could craete mutiple reports based on the file uploaded. We created two reports named "Report1" and "Report2" as in the following screenshot.

There will be multiple databases created to support Powerview reports on Microsoft Analysis Server as in the following screenshot.

The issue is if you delete the reports, or even the Powerview file, then claenup the recycle bin, the database entries inside Microsoft Analysis Server are still there.

We have also tried to delete the whole site collection completely as the following steps.

1. Delete the site collection from central admin
2. Run the command to remove the deleted site from queue
    get-spdeletedsite -webapplication http://spsbx15 | Remove-SPDeletedSite
3. Run “Gradual Site Deletion” immediately to delete the sites that were marked for deletion
4. Delete the content DB from central admin
5. Delete the content DB from SQL server
6. IISRESET on SharePoint server

We are surprised that the multiple databases created on Microsoft Analysis Server are still there as in previous screenshot. The orphan database on SSRS database server is one of the Powerview issues that need to be addressed.

Added note on 8/21/2014.

After working with Microsoft support, we have identified similar issues published by Microsoft KB.  After we upgrade our SSAS databases to 11.0.3412 which is 2012 SP1 CU9, this issue has been resolved. However, the trick is the orphan databases will be removed after we create another new Powerview report. It seems to me the new Powerview report creation will trigger to refresh the SSAS database.

An error occurred while loading the model for the item or data source 'EntityDataSource' on Powerview reports when site restored to different SharePoint 2013 farm

We are in the process to upgrade SharePoint 2013 to SP1 and June 2014 CU. We would like to refresh non-production farm with production content. We have identified some issues and one of the issues is we are getting error “An error occurred while loading the model for the item or data source 'EntityDataSource'” for all Powerview reports when site restored to different SharePoint 2013 farm.

Here is the procedure to reproduce the issue and the possible reason. We might need to work with Microsoft to identify a solution.

1. Create a site collection named http://spsbx15/sites/Harry/ 
2. Enable Powerview site feature and add an “PowerPivot Gallery” app
3. Upload one Powerview file named “HelloWorldPicnicPowerViewRTM-2010.xslx” from Microsoft site
4. Create a power report and same as “Report1” as in the following screenshot
5. Backup the content DB as WSS_Content_Harry.bak
6. Restore the content DB to different farm and change the permission for the system account
7. Restore the site collection on destination farm using command “Mount-SPContentdatabase -name WSS_Content_Harry -Webapplication http://spsbx08”
8. The site will be restored to http://spsbx08/sites/Harry/
9. Grant the access permission to the new content DB to the system account
• $w = Get-SPWebApplication –Identity http://spsbx08
• $w.GrantAccessToProcessIdentity("na\spsvc8")
10. Click the “Report1” from the restored site and get the following error

Here is the site from original SharePoint farm.

We are able to view the Report1 from original site.

Since Powerview report will generate database entries in SSRS database as in the following screenshot,we believe the Powerview reports need to connect to SSRS database tables to display the report. As site backed up and restored to new SharePoint farm, there is no process provided by Microsoft to backup and restore the Powerview related SSRS tables along with site collection content. As a result, the restored Powerview reports will not work! I've a workaround to fix this and will publish in future blog. We are working with Microsoft to verify whether we could have a automate way to fix all Powerview reports for the whole farm.

The good news for the restored site is you can create new Powerview reports without issues.

SharePoint 2013 site deletion issue w/ Access Apps

If we installed any Access App to SharePoint site and then either delete the Access App or delete the site collection, the issue is Access App databases and logins will remain on SQL database server. In the long term, we will end up many unused access database and login users.

We try different way to identify the orphan Access Apps but we have not find a good way. Here is the detailed descriptions to reproduce the issue.

 1. Create a site and add one Access App named "HarryAccess1" and another "Project Management" app from SharePoint store. Both of them are access apps.

2. Delete the "HarryAccess1" first and then delete the whole site collection. Look at the database and you will still have the two access App databases and all the logins associated with the databases.

The tricks to complete remove the access app databases and the logins is you need to run the following two steps after remove the site collection.

1. Remove site collection from graduate deletion queue

• get-spdeletedsite -webapplication http://spsbx15 | Remove-SPDeletedSite 
• Run “Gradual Site Deletion” immediately to delete the sites that were marked for deletion 

2. Might need to recycle the app pool running the app management service or iisreset

If you following these steps, the access app databases and logins should be removed and SharePoint databases will be clean.