Be aware of this feature when publishing Excel power pivot models to SharePoint

When creating Power Pivot models in Excel and you hide all the columns in your fact table/-s – Excel will show a ∑ next to the table name and show these first in the PivotTable field list in Excel.

This is a good way to help the users of your models to navigate in your model – but if you plan to browse the models via SharePoint – Excel Services has a very bad side effect…

The fact table cannot be accessed via the PivotTable Fields selector…..

If you unhide a field in the fact table

The table will now show as table in the field list in Excel.

And when you browse your model in SharePoint the Facts can now be added to the pivot table in the browser.

So if you plan to publish your models on SharePoint – do not hide all the columns in the fact tables unless you do not want the users to be able to modify the values in the pivot tables.