Analyze in Excel: Initialization of the data source failed

[jdunmall]

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?

[AJames]

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

[jl20]

My colleague is having the same issue. Any update?

[jllelis]

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

[duncfair]

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. 

[jllelis]

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.

[RakeshEternus]

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

[duncfair]

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

[jeremyhager]

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?

[jllelis]

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.

[Guest]

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

[jl20]

That worked - thanks!

[jllelis]

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

[IlanH]

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

[Anonymous]

Hello,

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

[batyushki]

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?

[AndrewSEA]

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?

[jllelis]

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.

[AndrewSEA]

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

[jllelis]

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.

[AndrewSEA]

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