analyze in excel

[urpalani]

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 ?

[mrzap000]

same issue with 1711 (Targeted channel), win10 64

manually changing MSOLAP to MSOLAP.7 works for me

[dler]

@Anonymous  This solution worked ... replacing MSOLAP with MSOLAP.7!  THANK YOU SO MUCH!

[dler]

@AnonymousTried everything ... nothing worked ... EXCEPT YOUR SOLUTION!  THANK YOU SO MUCH!

[pablobarra]

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

[EP_Jack]

i applied the latest link from tal.... it fixed it for one day and then when the computer restarted it we get the same error again

very frustrating.... is there any updates

[v-qiuyu-msft]

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]

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]

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

[Twan]

Has anyone been able to fix this issue for all of their users?  They all have the latest version of Analyze in Excel and the latest version of Power BI Publisher installed.  Changing the connection to use MSOLAP.8 instead of MSOLAP.7 fixes the problem for some users but not all.

[tarostok]

Hello, 

For those who still experiance the issue after changing the connection to MSOLAP.8 please try the following:

1. Download MSOLAP 7 from here: http://download.microsoft.com/download/4/8/2/482E5799-9B8E-4724-8A4C-F301BAE788EE/13.0.3523.4/1033/x...
2. Close all office applications
3. Go to: C:\Program Files (x86)\Microsoft Office\root\VFS\ProgramFilesX86\Microsoft Analysis Services\AS OLEDB\140 and rename the adal.dll to adal_orig.dll (if you have excel 64 bit use the version from Program Files)
4. Copy adal.dll from here: C:\Program Files (x86)\Microsoft Analysis Services\AS OLEDB\130 to: C:\Program Files (x86)\Microsoft Office\root\VFS\ProgramFilesX86\Microsoft Analysis Services\AS OLEDB\140  (if you have excel 64 bit use the version from Program Files)
5. Open .odc file and see if it works for you.

[eliorb]

It works, but after you close the file and open it again the connection is back to MSOLAP.7 and  then it does not work again.

[digowaddington]

Hello guys,

I´m still experiencing this error. It started on September and I´ve tried all the solutions listed here.

In the beggining they´ve worked, but only for a short period of time. Then the error started showing again.

I need a real and solid solution for it. Analyze in excel feature is one of the best things on BI, and I need it on my day-a-day routine. 

[Twan]

The method mentioned earlier in the thread about replacing the adal_orig.dll file has worked for my end users.  Here are the steps:

1. Download MSOLAP 7 from here: http://download.microsoft.com/download/4/8/2/482E5799-9B8E-4724-8A4C-F301BAE788EE/13.0.3523.4/1033/x...
2.    Close all office applications
3.    Go to: C:\Program Files (x86)\Microsoft Office\root\VFS\ProgramFilesX86\Microsoft Analysis Services\AS OLEDB\140 and rename the adal.dll to adal_orig.dll (if you have excel 64 bit use the version from Program Files / ProgramFilesX64)
4.    Copy the adal.dll from here: C:\Program Files (x86)\Microsoft Analysis Services\AS OLEDB\130 to :C:\Program Files (x86)\Microsoft Office\root\VFS\ProgramFilesX86\Microsoft Analysis Services\AS OLEDB\140 (if you have excel 64 bit use the version from Program Files / ProgramFilesX64)
5.    Open the odc file and see if it works for you

[tarostok]

Hello,

If someone still experiances issues with Analyze in XL please send me an email and I will be happy to help you resolve the issue via Skype, my email is - tarostok@microsoft.com.

Thanks,

Tal

[alvrz1908]

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. 

[James_W_Myers]

Just had the same issue which remains unresolved - Why does Analyze in Excel not use Power Query, think it would be easier to use and hopefully not have the same issues? 

[v-qiuyu-msft]

Hi @alvrz1908,

Analyze in Excel feature has some requirements, you can review it before use this feature. for your feedback, you can contact the @tarostok who comes from the Analyze in Excel team.

Best Regards,
QiuyunYu

[MSAInitaite]

Hi 

I get the exact same error (well, in danish, but still) and I've just installede and reinstalled the OLEDB driver. 

I have another computer, on which it's not woring Either, but there Excel crashes without an error. 

Any other Ideas as hov to solve this? 

[xiaodong]

If you use Windows 7 or Windows 2008 R2, 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. I think the issue related to Windows 7 AND the latest Analyze In Excle (SQL_AS_OLDB.msi build 14.0.700.314).  

[tarostok]

Hello,

I'm from Analyze in XL team and I'll be happy to help with this issue.

Please contact me via e-mail tarostok@microsoft.com.

Thanks,

Tal

[MSAInitaite]

Hi

I have the exact same problem, and i've update the component. 

I'm having this issue on one computer and on another computer, where I don't get the error, but Excel just crashes.