Jul 03 2017
- last edited on
Jul 12 2019
We, as a company are maintaining a OLAP cube in MS Analytical Services. We use MS SQL Server Management Services. I am trying to connect Excel 365 online to the OLAP cube. I should be able to refresh connection from Excel 365.
Things I have tried:
1. Connecting Excel 2016 desktop to OLAP cube (Successful) and opening the same file in Excel 365(Failed)
2. Using power BI:
a. Created an organization gateway.
b. Connected power BI to organization gateway(Success)
c. Connect Excel 2016 Desktop to power BI dataset (Sucess)
d. Upload same excel file online and refreshing the connection. (Failed)
PS: I am using the same user account everywhere. Also, I am added as a user to all gateways and OLAP cube.
Please let me know if there is a solution for this within the existing infrastructure.
Jul 03 2017 09:32 AM
Jul 03 2017 09:40 AM
Thanks for your prompt reply.
So, in the 1st scenario I connected Excel desktop to OLAP through a connection string (Get and transform method, I guess) in Data tab on the ribbon.
In the second scenario. I used 'Power BI' plugin for Excel desktop in order to import data from Power BI.
In both the scenarios, refreshing the excel file from Excel 365 failed.
Jul 03 2017 09:55 AM
Jul 03 2017 11:23 AM - edited Jul 03 2017 11:23 AM
I'm not sure, but most probably that's not an issue of connection to on-premises data source. You may create simple data model published on Power BI services, connect it to Excel and try to refresh from Excel Online. It doesn't work.
Analyze in Excel | Microsoft Power BI
Since users will need to refresh the dataset, and refresh for external connections is not supported in Excel Online, it’s recommended that users open the workbook in the desktop version of Excel on their computer.
That was publishes (more exctly updated) bit more than a month ago , don't think something changed recently. We don't use Excel Online in organization (people pushed to open in desktop), thus have no concrete experience.
Jul 06 2017 08:43 AM
Thanks for answering.
Our problem is that we can connect excel desktop to OLAP cube successfully. However, when users use multiple tabs and pivot tables, excel crashes or its very slow.
So, would using excel 365 solve this problem? If not then what else can be done?
Your help is much appreciated.
Jul 06 2017 11:30 AM
I have no own experience with such issues, but in general
- power bi services is actually SSAS, when you connect to power bi from excel you don't load the data model into excel, that's just live connection. To refresh you run simular OLAP query as you run using live connection from Excel desktop to your on-premises SSAS.
- perhaps the difference if in both cases if you use live connection is what the cloud infrastructure usually more advanced and more optimized compare to on-premises one.
- with power bi scenario, why don't you open Excel in desktop mode, not online? The only reason could be if some users don't have Office license and have to use online version only.
- and with first scenario did you try to compare live connection and importing to Excel data model? Are they both have performance issues?
Sorry, nothing concrete, just thoughts.
Jul 06 2017 11:39 PM
Jul 07 2017 01:50 AM
Wyn, if use live connection to the cube hardware shall not be critical. If load entire cube into the Excel data model - yes, that depends.
Apr 02 2020 08:29 AM