How do I tell Power View that the location or name of the PowerPivot model has changed?

Question

The standard way of creating a Power View report in a PowerPivot Gallery in SharePoint is to click on the Create Power View Report button that appears on a PowerPivot data model in the Gallery view.  I have no problem understanding how to do this and it works fine.

But, what happens if the name of the PowerPivot data model file (.xlsx file) changes or it gets moved to another location in SharePoint?  My experience is that this breaks any Power View reports that are in the library and using the moved\renamed PowerPivot model.

This is understandable, but surely there is a way to "re-point" the Power View report(s) to the new name\location of the PowerPivot data model to get the report working again.  If this capability is there, I can't find it.

Does anyone know how to do this?

---

Jeff Cate, Founder and President, SharePoint Solutions

Answer 1

Hi Jeff,

When creating a Power View report using PowerPivot data, we must have a BI semantic model connection (.bism) file or a Reporting Services shared data source (.rsds) file that points to a published PowerPivot workbook in a SharePoint library. In Power View, the .bism or .rsds file provides the data connection used by the report. Once the location of the PowerPivot workbook file is changed, we need to re-create the .bism or .rsds file that maps to the correct workbook file. For the detailed steps to create a shared data source for a data model or create a BI Semantic Model connection to a PowerPivot workbook, please see:

• Create a Shared Data Source for a Data Model (SSRS)
• Create a BI Semantic Model Connection to a PowerPivot Workbook

Hope this helps.

Regards,

---

Mike Yin
TechNet Community Support

Answer 2

Hi Mike,

What you have said is not completely accurate, and it doesn't address the fundamental problem that the data connection setting inside a Power View report does not seem to be able to be changed once the report has been created.

The problem with not being able to change the data connection setting inside Power View is that it makes it impossible to move or rename an existing .bism file (or .rsds file) that a given Power View report is using.  Also, it is possible to create a Power View report directly from a PowerPivot model without having to have a .bism or .rsds file.  I would say that this is the most popular way that end users create Power View reports.  I will explain how this technique works below, but it also suffers from the restriction that if you move or rename the PowerPivot model, you will break any existing Power View report that uses it and apparently there is no way to fix the connection from inside of Power View.

I see this being a big impediment to being able to manage and govern self-service BI.  For example, one approach to managing self-service BI using PowerPivot and Power View is to create a library that users can store their initial reports\models\connection files in and then have a governance team identified to review and "check" their reports\models\connections.  Once the governance team approves the report\model\connection it can be moved to another library that is the library of "approved" reports\models\connections.  Unfortunately, this will never work because when the .bism, .rsds or .xlsx file that the Power View report is based on is moved, the Power View report will break because the URL of the .bism, .rsds or .xlsx file will have changed.

Some people might say that the solution is to put the .bism, .rsds or .xlsx files in their own library(s) and then have a policy that they never get moved and never get renamed.  I would call that a workaround rather than a robust solution.  Simply put, there needs to be a way in Power View to modify the path to the data connection file - whether it is a .bism, .rsds or .xlsx file.

The reason I said that what you have said in your reply is not completely accurate is that you only list two out of the three ways to create a Power View report from a PowerPivot model published in SharePoint.  You list the .bism and .rsds methods, but you left out the method of using the "Create Power View Report" icon that exists on PowerPivot models when you view them using the PowerPivot Gallery View of a PowerPivot library.  If you use that technique (which is the way many, many end users prefer to do it), there is no need for a .bism or .rsds file - the Power View report is linked directly to the PowerPivot model .xlsx file.  Here is a screen shot of what I am talking about:

---

Jeff Cate, Founder and President, SharePoint Solutions

Answer 3

Hi Jeff,

You can manage data sources for reports through SharePoint. Go to a standard SharePoint view of the library that contains the report. (The PowerPivot Gallery view doesn't expose item context menus, so you need to switch the view from the ribbon to "All Items" or any standard SharePoint view.) Open the context menu for the report and click Manage Data Sources (this is the same option for both traditional SSRS reports and Power View reports).

If your report connects to a shared data source (RSDS), you can repoint it to a different RSDS file. If it connects to a BISM or XLSX file, it will likely have an embedded data source, so you can edit the URL of the BISM or XLSX file in the connection string.

You can also manage data sources programmatically, such as via the ReportService2010 SOAP web service.

---

Program Manager, SQL Server Reporting Services

Answer 4

Exactly what I needed to know!  Thank you Riccardo!

I don't know why it did not occur to me to look at the context menu on the Power View report and explore the Manage Data Sources option - but, it didn't. 

Makes perfect sense now.

Thanks

---

Jeff Cate, Founder and President, SharePoint Solutions