cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jdunmall
Advocate I
Advocate I

Analyze in Excel: Initialization of the data source failed

Hi all,

 

Trying to get Analyze in Excel working. I tried it with a PBIX uploaded from desktop. I've installed the OLEDB package. I get the following error: Initialization of the data source failed. Check the database server or contact your database administrator. Make sure the external database is still available, and then try the operation again. If you see this message again, create a new data source to connect to the database.

 

Here is the data source:

Provider=MSOLAP.7;
Integrated Security=ClaimsToken;
Identity Provider=AAD;
Data Source=https://analysis.windows.net/powerbi/api;;
Initial Catalog=sobe_wowvirtualserver-xxxx;
Location=https://wabi-us-east2-redirect.analysis.windows.net/xmla?vs=sobe_wowvirtualserver&db=xxxx;
MDX Compatibility= 1;
MDX Missing Member Mode= Error;
Safety Options= 2;
Update Isolation Level= 2

 

I'm Azure AD joined.

 

[Edit] I am using Excel 2016. When I try to open the connection in Power Pivot, and  test the connection, I get: the test connection failed because the provider could not be initialized. If you contact Microsoft support about this error, provide the following message: Failed to connect to the server. Reason: Invalid value for key 'integrated security'.

 

Feels like the new OLEDB install didn't work, though it reported as installed succesfully. I see it listed in Programs and Features "Microsoft AS OLE DB Provider for SQL Server 2016 RC1".

 

I'm running 64-bit office.

 

Any ideas?

1 ACCEPTED SOLUTION
kcantor
Community Champion
Community Champion

I had this exact error. I uninstalled the add on and then re installed making sure to choose 64bit. After that, it allowed me to log in and export for analysis. While I had installed the correct version, a coworker installed the incorrect version. A clean install solved the issue for both of us.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

26 REPLIES 26
AJames
New Member

I am having the same issue, any update please? I also get the message:
"The following system error occurred: (The operation completed successfully.)."

My colleague is having the same issue. Any update?

Hi All,

 

The lastest office update release has a correction for this issue, try to update it and see if works. If not, there is a workaround in this link: https://pbiwiki.com/wiki/TSG_-_Analyze_in_Excel look for the topic: MSOLAP 8 uses a bad adal version. Hope it helps!

 

Best,

João Lucas

When I try that I get the following error message:

 

Selected user account does not exist in tenant 'Microsoft' and cannot access the application.

'https://pbiwiki.com/simplesaml/www/module.php/saml/sp/metadata.php/default-sp' in that tenant. The account needs to be added as an external user in the tenant first. Please use a different account. 

Try the following workaround:

 

  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

 

At any point you can go back to the original adal file.

Installation step works for me

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

Thanks !!!

That didn't work. Possibly because the AS OLEDB version is only 110 on my system.

 

C:\Program Files\Microsoft Office 15\root\vfs\ProgramFilesX86\Microsoft Analysis Services\AS OLEDB\110

Hi jllelis,

 

It seems you've linked an interal wiki for Microsoft-tenant-only users. Can you post the workaround in this forum or on a public wiki?

Try the following workaround:

 

  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

 

At any point you can go back to the original adal file.

Dear Jllelis,


You are a genius. Kudos.

You can not understand how much you've helped me. @jllelis


Actually, I am a good user of Excel for some time. I like to use queries and data relationships to calculate profit on foreign exchange mega data. It happened that my Excel Office 2016 was uninstalled by the IT department after a year of activity. Another IT staff came and installed Office 2016 Professional Plus and I was happy to get back to my data and peform my calculations as usual.


The first query I refreshed couldn't. I thought it was a rather complex query. I started simple queries from simple tables but neither could load. I asked Microsoft, by default, they said, this is due to "Old PowerPivot Add-Ins" to put it simple. I went on many forums, tried almost every proposed solution:
-Re-installing

-Removing & Replacing Add-Ins

-Running latest office updates

....


I tried numerous options and none seemed to be proper.

When I tried @jllelis option to install "Microsoft AS OLE DB Provider for SQL Server 2016" available here and it worked flawlessly and all my data refreshe and loaded perfectly.

Thank you @jllelis

That worked - thanks!

jllelis
Advocate I
Advocate I

I did everything in this post and I'm still getting this issue!! 😞 😞 😞

IlanH
Microsoft
Microsoft

We have just release an improvement to "Analyze in Excel" setup/download experience

1) 1 download link - we auto detect the OS (operating system) bit-ness (32 or 64 bit) and accordingly provide 1 download link

2) Download menu - when you choose "Analyze in Excel" from the download menu you get a dialog with 1 download link (we auto detect the right link) and link to "Analyze in Excel" help page

 

Download_Menu1.PNG

 

Download_Menu.png

Hello,

I am having issue where my analyze in excel is greyed out.  Any ideas on why it is greyed out?

In the last week I have just started to get this error. I have downloaded the add-in, uninstalled it and reinstalled to no avail. Is there a way to force 64bit compatibility at the time of install?

I'm getting this when I try to use Analyze in Excel and many of my co-workers are experiencing the same.  Everything was working up until a couple week ago.  With so many people experiencing this, I suspect something on the backend broke?

Try to change the provide in connection string by updating the Provider=MSOLAP.7 to Provider=MSOLAP.8. Go to DATA tab >> Queries & Connections >> right click on connection name >> Properties >> Definition tab >> Connection string. I don´t think this will be a final solution, but maybe it will help for now.

That did work for my colleague and got me past this error message, but now I get this error:

 

"The following system error occurred: (The operation completed successfully.)."

 

Hi Andrew, I´m getting this same issue to. I´ve replied about this because a collegue is not facing this second issue. I have a ticket with support team and I´m waiting for a response, I will share once I got a solution from them. This "Azure issue" I´ve solved before by installing the Power BI Publisher for Excel and logging in my account on Profile bottom (Power BI toolbar), but now it´s not solving that! However, you can try do it and also make sure you have your account logged in Excel since it seems to be an autentication issue.

I have PowerBI Publisher installed with me logged into my profile, and it doesn't work.  

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.