PowerPivot Model on SQL Server Powerpivot Instance

[JelleVanCraen]

Hi,

We're trying to add a datasource to our on premise data gateway. The datasource is a Powerpivot model deployed on our on premise Sharepoint server (thus deployed on our SQL Server 2012 Powerpivot instance). 

The Url is like: http://sharepointXXX/Site/Datamarts/Powerpivot.xlsx (which is basically a connection to a deployed model on our Powerpivot instance). 

 

When we do this we get a "connection succesful" but when we try to add a dataset in PowerBI based on this datasource we get the following:

Technische gegevens:

Activiteits-id: a55befe7-a85b-bbef-f2a6-31cb081f1230

Aanvraag-id: f00977c4-e0ce-9f69-7443-7ee4b78ba10a

Datum: 2016-11-16 16:26:40Z (UTC)

Fouttekst: You do not have permission to perform the requested operation.

Cluster-URI: https://wabi-north-europe-redirect.analysis.windows.net

Any ideas?

Many thanks

Jelle

[Anonymous]

Hi @JelleVanCraen,

Do you get the above error message when connecting to the Excel file via “Get Data>Files>SharePoint -Team Sites” option in Power BI? If that is the case, how about using Power BI Desktop to connect to the Excel file? You can connect to the Excel file using the Get Data->All->Web option or Get Data->All->SharePoint Folder option.

Based on this article, when you want to refresh the dataset created from Excel workbooks which contains Power Pivot model connecting to all on-premises data sources except for Hadoop file (HDFS) and Microsoft Exchange, personal gateway is required.

In your scenario, you would need to install and configure personal gateway instead of on-premises gateway to refresh the dataset.

Thanks,
Lydia Zhang