Data gateway mode required to refresh an Excel workbook in Power BI with a Power Pivot data model

[abernal]

I have an Excel workbook uploaded to the Power BI service under the workbooks tab that has a Power Pivot data model which connects to an on-premises SQL server database. I have been able to successfully refresh the underlying Power Pivot data model with the data gateway on personal mode. Can I use the enterprise/standard mode of the data gateway to refresh the underlying Power Pivot data model of an Excel workbook? If so, how?

 Thanks,

Alex

[v-yuezhe-msft]

@abernal,

Add SQL Server data source within gateway following the guide in this article. And add the Excel data source within gateway if the excel file is located on your drives. 

Regards,
Lydia

[abernal]

Thanks Lydia, Your proposed solution won't work. We already have the SQL Server database as a data source of the the data gateway, which has been installed in standard/enterprise mode.

The excel files are in the SharePoint Files area of the Power BI workspace and get imported into the Workbooks area of the Power BI service by pressing the "Get Data" icon in the Powe BI service, selecting the "Get" icon in the Files section of (Import or Connect to Data), selecting One Drive for Business, selecting the Excel file I want to import, clicking Connect and, finally, clicking again on the "Connect" icon of the "Connect, manage, and view Excel in Power BI. Once I do all this, I see my Excel report in the Worbooks area of the Power BI Service. This Excel worbook has a Power Pivot data model that connects to an on-premises SQL Server database. From my research, I have concluded that I can only refresh the underlying Power Pivot data model with the data gateway installed in personal mode. See Configuring scheduled refresh.

I need to absolute confirmation that the data gateway in standard/enterprise mode won't work. Can you assist? If not, who should I contact? Thanks,

Alex

[v-yuezhe-msft]

@abernal，

It is possible to use on-premises gateway to refresh your dataset in Power BI Service, please take a look at this official article.

And I test same scenario as yours, refresh works well when I use on-premises gateway.


Regards,
Lydia

[abernal]

Thank you Lydia.

I got it to work with an Excel workbook that contains a Power Pivot model that connects to an on-premises SQL Server database. I did not work, however, if the Power Pivot model connects to an Analysis Services Tabular database. I checked all the configurations and I am pretty sure that they are correct. Could you please perform the same test that you did before, but having instead a Power Pivot model that connects to an Analysis Services Tabular database? By the way, our Analysis Services Tabular database server is 2012. 

Below as some screenshots of my settings screens:

1) on-prem data gateway appears online for power pviot models that have a SQL Server connection... 

 2) on-prem data gateway appears offline for power pviot models that have a Analysis Services tabular connection... 

Thank you in advance for your assistance,

Alex 

[v-yuezhe-msft · Accepted solution]

@abernal,

I can't use on-premises gateway when connecting to SSAS in Excel. Only peronal mode gateway can be used in this case.

Regards,
Lydia

[abernal]

Thank you Lydia.