Forum Discussion
Data gateway mode required to refresh an Excel workbook in Power BI with a Power Pivot data model
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
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
6 Replies
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
