Forum Discussion
Excel workbook connected to on-premise SSAS cube - Can Power BI refresh?
jordandakota,
Firstly, Power BI can handle the refresh of your connection. But please note that when you refresh a dataset created from an Excel workbook imported from a local drive, only the data queried from data sources is refreshed. If you change the structure of the data model in Excel or Power Pivot, you'll need to re-upload the workbook. If you expect to make regular changes to the structure of your workbook and you want those to be reflected in the dataset in Power BI without having to re-upload, consider putting your workbook on OneDrive. For more details, please review this article.
Secondly, you want to use "Analyze in Excel" feature by signing in Power BI with work email in your homework, right? If so, you are able to open the workbook from Power BI Service. And as you have set schedule refresh on the dataset in Power BI Service, you will get updated data based on the schedule.
If you want to get the most recent updated data, you can hit "Refresh Now" in Power BI to get the most up-to-date data. In the refresh process, all that matters is that the data gateway has access to your data source. Power BI service uses Azure Service Bus to send queries and credentials to the gateway. Then the gateway does the physical connection to the data source.
For more details about Power BI security and how gateway works, you can review the following article.
Power BI Security
On-premises data gateway in-depth
Thirdly, do you refer to row Level Security in Power BI? And what do you mean "If I manipulate filters after opening the workbook"? If you mean RLS in Power BI, RLS will still work after you use "Analyze in Excel" feature to open the workbook.
At last, for better practice, I would recommend you upload Excel file to OneDrive and connect to the Excel file in Power BI.
Regards,
Lydia
I'm interested in doing the same thing as the topic author. I want to be able to publish a excel report with a live SSAS connection to PowerBI. And when in PowerBI.com I only want to see what I have access to (RLS) and that this goes through the gateway I have already set up for the specific SSAS cube server.
The only way I can get a refreshable excel report in PowerBI is if I create a data model query with PowerPivot on top of a SSAS connection. If I just connect to SSAS the ordinary way and then publish to PowerBI.com it just says that refresh is not supported. If I do the former, however, it at least lets me update the model and report (though it has always failed for some reason, but at least it tries..).
The sad part is that I don't see the same thing as I do when I publish a similar report from PowerBI desktop. Then I can only see what my RLS allows me to, but with the excel workbook it somehow doesn't go through the same gateway as the PowerBI desktop report. The result is that no matter the user, they will see the report in its entirety and the whole idea of using this as an option for our organisation is smashed to pieces.
Notes:
When I set up the model in Excel I state that the connection should use basic windows login authentication, so it doesn't use any admin credentials or so.
When I visit the settings for the workbook on PowerBI.com it says that I don't have a gateway for the data source in the workbook and that I have to install it. This is not the desired behavior since I already have a gateway for the SSAS cube I'm connecting to.
Can anybody confirm or deny what can be done in reality here?
Regards,
Jonathan