Hi, When i download the data in Excel - Analyze in excel ,I am asked for credentials but after that it popups "The Following Error Occured:(The Operation Completed Successfully) , When i click ok ,again Power BI Dataset Remote Connection screen opens, Anybody faced this issue ?

I had this issue today and first I tried to download all versions of SQL_AS_OLEDB.msi that I could possibly find but nothing worked, so then I did the EXACT OPPOSITE of what you just wrote: I replaced MSOLAP with MSOLAP.7 and saved the file. And then it worked!

Hi v-qiuyu-msft I did get once instance resolved, but updating the OLEDB provider did not actually resolve it. The only way I got it resolved was actually be uninstalling and reinstalling the entire Microsoft office suite and basically starting over. This was very time consuming, so I really hope this bug gets resolved. I also had other colleagues install the OLEDB provider and it still lead to the error message. I then added my file to a separate workspace and one user was able to analyze in excel, the other could not. So honestly, this has been really hard to pinpoint. Any users under the same office 365 service and Power BI with should be able to open .odc file from my Power BI workspace right? This analyze in Excel feature is probably the single most important feature of Power BI, so it has been incredibly frustrating not having other users open the .odc file and successfully access the linked data. Can you please prioritize this? I am happy to work directly with any associates at Microsoft.

The issue realted to the Windows 7 or Windows 2008 R2, plus the latest Analyze In Excle (SQL_AS_OLDB.msi build 14.0.700.314). You can try the build 13.1.4001.0 (SQL 2016 SP1). Download the ENU\x64\SQL_AS_OLEDB.msi from https://www.microsoft.com/en-us/download/details.aspx?id=54279.

We think we found the issue and will fix it soon in PowerBI.com (end of week) Below is a workaround: in the odc file, change MSOLAP.7 to MSOLAP1) Open *.odc file Analyze in Excel created in powerBI.com2) Search MSOLAP3) Change MSOLAP.7 to MSOLAPBefore: Provider=MSOLAP.7;After: Provider=MSOLAP;

HiIf you run Excel as "administrator" it will work fine.

analyze in excel | Microsoft Fabric Community

42 Replies

xiaodong
Frequent Visitor
9 years ago
The issue realted to the Windows 7 or Windows 2008 R2, plus the latest Analyze In Excle (SQL_AS_OLDB.msi build 14.0.700.314). You can try the build 13.1.4001.0 (SQL 2016 SP1). Download the ENU\x64\SQL_AS_OLEDB.msi from https://www.microsoft.com/en-us/download/details.aspx?id=54279.
pablobarra
Advocate I
8 years ago
Hi
If you run Excel as "administrator" it will work fine.
IlanH
Microsoft Employee
8 years ago
No coincidense. Excel click to run latest updates should fix the issue.
Find below build info for relevant click to run releases.
You are on November 2017 release (according to build you provided)

- July 2017 (Builds 16.0.8431.2XXX):
Available in July builds 16.0.8431.2149 and higher.

- October 2017 (Builds 16.0.8730.2XXX):
Available in October builds 16.0.8730.2175 and higher.

- November 2017 (Builds 16.0.8827.2XXX):
Available in November builds 16.0.8827.2107 and higher.

- December 2017 (Builds 16.0.9001.2XXX):
Available in December builds 16.0.9001.2000 and higher.
- Mike_Carlo
  Most Valuable Professional
  8 years ago
  Update it is now 2-15-2018 and I have been able to update all my Excel components in office 2016. It is now working again.
v-qiuyu-msft
Community Support
9 years ago
Hi urpalani,

To use Analyze in Excel, the computer must have a current AS OLE DB provider installed. Please check whether you need to update your Excel libraries for OLE DB provider follow this article: Update Excel libraries for the OLE DB provider.

Also you can check if the Power BI service Sample dataset is working with Analyze in Excel feature. In Power BI service, you can Get Data -> Samples -> Retail Analysis Sample.

Best Regards,
QiuyunYu
- alvrz1908
  New Member
  9 years ago
  Hi QiuyunYu,
  
  Thanks for much for this. I was having the exact same issue. Can you please inform the Power BI team to improve the error message so that it leads to the fix? The text of the error message itself is also very confusing.
  
  This also seems to be an issue triggered by installing Power BI Publisher fo Excel. Once that was installed before the Excel Library update, for whatever reason it removed the prompt for new Power BI users to install the needed Excel Library Update. Without that prompt, there was a lot of confusion around the error message.
  
  Thanks,
  Sergio
  - v-qiuyu-msft
    Community Support
    9 years ago
    Hi alvrz1908,
    
    Did you solve the issue by update the OLEDB provider follow my previous post?
    
    Regarding your concern about the error message, I will report it internally.
    
    You can check Excel Library Update in Power BI service anytime follow IlanH's post.
    
    Best Regards,
    QiuyunYu
tarostok
Power BI Team
9 years ago
Hello,

Please download the following MSOLAP verison and install, it should fix your issue:
http://download.microsoft.com/download/4/8/2/482E5799-9B8E-4724-8A4C-F301BAE788EE/13.0.3523.4/1033/x64/SQL_AS_OLEDB.msi

Thanks,
Tal
- AndrewSEA
  Advocate II
  8 years ago
  That didn't seem to fix the problem. Many of my report users are experiencing the same problem.
  
  I'm on Windows 10, and using Office 365 Pro Plus (64 bit).
  - IlanH
    Microsoft Employee
    8 years ago
    We think we found the issue and will fix it soon in PowerBI.com (end of week)
    
    Below is a workaround: in the odc file, change MSOLAP.7 to MSOLAP
    1) Open *.odc file Analyze in Excel created in powerBI.com
    2) Search MSOLAP
    3) Change MSOLAP.7 to MSOLAP
    Before: Provider=MSOLAP.7;
    After: Provider=MSOLAP;
jgutierrez
Helper I
8 years ago
Hi,

Could it be related to the MS Office version as well?

1705 (Build 8201.2193), does not cause any problems.
But 1708 (Build 8431.2079), does not seem to work with any version of the MS Analysis Services.
- dler
  Regular Visitor
  8 years ago
  jgutierrez .. Not sure but I do have Version 1708 and experience the problem. I'm currently using the temporary solution that Anonymous recommended as listed above.
shawne
Resolver I
8 years ago
added the .7 and it worked.....but this will not suffice when working across larger groups. What is the horizon on this fix?
IlanH
Microsoft Employee
8 years ago
Excel click to run version include adal.dll version with bug in Analyze in Excel scenario.
Every Excel click to run installation will bring this adal.dll version.
To resolve this Excel must update adal.dll with fixed version.

As written in this thread there are 2 possible workarounds
1) Replace Excel click to run adal.dll - this need to be repeated after every Excel click to run update.
2) manually changing MSOLAP to MSOLAP.7 - the reason this work is that msolap.7 is using adal.dll without the bug

Good news: Excel are in the final stages of releasing updates with fixed adal.dll. Should be available by mid Jan.

Sorry for the inconvinience
- Jacky
  Frequent Visitor
  8 years ago
  Hi,
  For me chanigng to OLAP7 did not resolve the issue.
  Can we please have an update as soon as the fix was released ?
  - lndnbrg
    Resolver III
    8 years ago
    With my newest Excel 365 Build 8827.2117 Click-and-run (Monthly Channel) it seems to just work now.
    
    Have reinstalled the OLEDB driver from the Power BI website and now it JUST WORKS.
    
    Can anybody confirm this? Or is it a coincidence?
17843
Regular Visitor
8 years ago
I've previously been able to fix this issue by removing the .7 in the ODC connection string but now (after a rebuild courtesy of 'IT') neither works.

I'm using 32-bit Excel 2013 (15.0.5007.1000). I honestly don't know what version of AS OLEDB I'm using, it's recent but not the latest.

Need to know exactly how to resolve this ASAP because any changes to my laptop involves raising a ticket with IT etc. etc.