XMLA Endpoint and Excel

It doesn't seem like you have used Excel Power Pivot. Otherwise, you would know that your first option is not possible to perform when using the "From Analysis Services or PowerPivot". If I instead try and do an "Other ODBC" conenction, and have it connect using the Microsoft OLE DB Provider for Analysis Services 14.0, and do as you say (set Username and Password to blank) I get the following error:

Non-Windows user credentials were supplied for a non-http connection to Analysis Services. The Microsoft SQL Server Analysis Services data extension requires Windows Integreated Security unless connecting via http or https.

As for your second and thrid options, those are just restatements of what I had said I was already attempting. These are really not viable options to provide to over 100 users in our company, as most of them can not hand write a query.

Your fourth option also does not work. I tried saving a connection without the SSIP option present. When adding that back to my Power Pivot, and attempting to either write a query or refresh results, I get the same error as above:

Non-Windows user credentials were supplied for a non-http connection to Analysis Services. The Microsoft SQL Server Analysis Services data extension requires Windows Integreated Security unless connecting via http or https.

The issue appears to be with how Excel Power Pivot interacts with XMLA Endpoint using the client libraries. Not sure if it is a bug, or an incompatibility between the two. I would suggest trying to take a simple Power BI dataset, and then try and import the data via Excel Power Pivot. You shoudl quickly see the same errors I am facing.