Excel workbook connected to on-premise SSAS cube - Can Power BI refresh?

[jordandakota]

There's a lot of topics about Excel workbooks, on-premise connections, SSAS tabular/MD, but nothing really concise answering these questions:

• If I create an Excel 2016 workbook and use Data>From Other Sources>Analysis Services>Choose an on premise server>to pivot table, save this workbook locally and then upload to Power BI, assuming I've set up Data Gateway - Enterprise for this data source, can Power BI handle the refresh of the connection?
• If I login to Power BI using my work email from home, not on premise, can I open this workbook and see the most up to date data? Do I need to hit refresh? Can I hit refresh - does Power BI pass the request through Data Gateway?
• Assuming refresh works, how is security handled? Does Azure AD pass my credentials through Data Gateway to on-premise and therefore row level security, etc still works? If I manipulate filters after opening the workbook hosted in Power BI connected to an on-prem cube, do I only see data I have security for?
  • Can I login from home (off-premise) and still see updated data?
• Assuming refresh doesn't work, can I still host a workbook connected to an on-prem cube in Power BI and let users open the templates and manually 'refresh all'/set the workbook to 'refresh all' upon opening so they can use them while on-premise?
• If I save the workbook to my local computer, assumption is connection is lost and no further refresh possible - need to open from Power BI?

I maintain our local SSAS cubes and we currently use SSRS to host templates of those cubes for different perspectives, etc. We're trying to move towards ONE reporting portal and if I can set each paginated rdl report as an 'additional action' on the multi dimensional cubes and host these cubes in Power BI we can 'mostly' move over to Power BI service as the primary report portal.

Thanks folks,

[v-yuezhe-msft]

@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

[JAkerblom]

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