cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
urpalani
Helper II
Helper II

analyze in excel

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 ?

 

 

Capture.PNG

 

 

42 REPLIES 42
17843
Regular Visitor

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.

IlanH
Microsoft
Microsoft

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.

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.

Mike Carlo ( https://powerbi.tips )
mike@PowerBI.Tips
IlanH
Microsoft
Microsoft

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

Hi,

For me chanigng to OLAP7 did not resolve the issue.

Can we please have an update as soon as the fix was released ?

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?

shawne
Resolver I
Resolver I

added the .7 and it worked.....but this will not suffice when working across larger groups.  What is the horizon on this fix?

jgutierrez
Helper I
Helper I

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.

@jgutierrez .. Not sure but I do have Version 1708 and experience the problem.  I'm currently using the temporary solution that @Lasselakan recommended as listed above.   

pablobarra
Advocate I
Advocate I

Hi
If you run Excel as "administrator" it will work fine.

tarostok
Power BI Team
Power BI Team

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/x...

 

Thanks,

Tal

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).

So after working with the Analyze in Excel team, it looks like the version of ADAL.DLL being used is the culprit.  They are working on a solution.  Don't know when it'll be released, but they figured it out.  

 

I installed Visual Studio 2017 Enterprise, and it seemed to fix the problem for me because it probably overwrote the ADAL.DLL.

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 MSOLAPodc_msolap.JPG

Before: Provider=MSOLAP.7;

After: Provider=MSOLAP;

17843
Regular Visitor

I solved the issue by doing the reverse, but thanks anyway Smiley Happy

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!

I was also having this issue. When I opened the file it was already formatted with Provider=MSOLAP,   I was able to replicate what @Lasselakan did in the file by adding .7, so now it reads Provider=MSOLAP.7 After this change I am able to load the odc file and use analyze in excel... @IlanH what version of excel were you using?

 

I am using Excel 2016 MSO 16.0.8730.2046 64-bit

and just this morning installed the lastest version of OLEDB

x64_1033_3291099_SQL_AS_OLEDB

 

@tarostok  Is there any reason why this would be causing an issue?  Why would my newer 2016 version of excel require the MSOLAP.7?  It is difficult to work with the analyze in excel function if I have to change every odc file I download from the service. 

Mike Carlo ( https://powerbi.tips )
mike@PowerBI.Tips


@Lasselakan wrote:

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!


it worked for me too - great suggestion!

Yep, replacing MSOLAP with MSOLAP.7 also did the trick for me in version 1710 (build 8625.2127).  

With kind regards,

Mark Haring
Process and data consultant @ BisQQ
mcharing@bisqq.nl
www.bisqq.nl
Jacky
Frequent Visitor

Yes, for me as well.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors