Anyone got the new Analyze in Excel feature working?

[MattAllington]

I have tried to get this new feature to work.  I click Analyze in Excel, installed the new software (64 bit because i am running 64 bit Windows/Office) and then tried to Authenticate.  It gave me a forbidden erorr and then reprompted for me to authenticate against the Power BI SSAS Server.  No matter what I try, I can't authenticate.  I am using my Power BI email address for the user name and my correct Power BI password.

[IlanH]

While "allow free users" is now fixed.

I would like to mention 2 scenarios where "analyze in Excel' will fail.

1st is currently a limitation. the 2nd is due to choosing wrong account for authentication.

1) Current limitation - "Analyze in Excel" doesn't work for "on premise analysis services" dataset 

2) Choosing wrong account - you must choose your work/school account and not your personal account if you get multi accounts choice. see below

[Anonymous]

Do you know when this feature would be available for SSAS cube? We have few tabular cubes in house and I think  the users would be extremely happy if it will work.

[IlanH]

Would it help if we will provide ODC that you can open and Excel will connect directly to on premise SSAS cube ? assuming you have direct connectivity to your on premise SSAS cube.

[MattAllington]

When this says "Work or School Account", does this mean O365 or something else?

[IlanH]

We fix the issue and now "Analyze in Excel" is available and working also for free users.

Please try and lets us know in case you still have issue

[MattAllington]

---

@IlanH wrote:

We fix the issue and now "Analyze in Excel" is available and working also for free users.

Please try and lets us know in case you still have issue

---

I had extensive support from Tal via Skype last week, so I have a pretty solid understanding of how to test this.  Having said that, i still cannot get it to work.  I have multiple accounts but one of them is a "work and school" account - what ever that means.  It is an O365 account that I have for my business.  I am also the O365 Administrator for the O365 service.  I get the same errors I got previously.

[tommy_g]

I am one of those who have "multiple accounts", the original for my O365 E3 subscription (tied to my domain) and then later when I needed a new official "Microsoft Account" when upgrading to Win 8.

The way I understand "work or school account" is that it refers to an account set up and provided by someone else, as distinct from my "personal" Microsoft Account.

On the upside, having the same account name / email address makes it easy to remember. And I make sure to keep my passwords in sync for both accounts. (bad practice, but I do use a VERY strong password + two-factor auth to mitigate)

On the downside, lots of authentication issues for services that don't understand the need to disambiguate, or for which EITHER is a valid login. Hence, when I go to the Azure portal and they "think" I'm only logged into O365, all my Azure services disappear. Scary at first, but then a simple log-out and re-log gets me back. The same goes for Sway and a few other services.

I suspect that is my issue with Analyze in Excel.

[IlanH]

Tal will work with you to investigate your scenario.

re multi accounts. O365 account should work of course.

My post in respect to accounts refer to a specific scenario where you have 2 accounts with exact same account name. in that case you get authentication form with 2 account options - one is personal and one isn't personal (work/school) you should choose the work/school option in that specific case.

[IlanH]

We have understanding of the reasons you got "Forbidden"

1) Common scenario - free users get forbidden

• We are working to fix that and allow Analyze in Excel for all users
• Currently it is open for pro users

2) Advance case - using multi users for Analyze in Excel

• To resolve this case you should follow Tal workaround steps. Tal steps provide a way to sign in Excel with specific user (and sign out from prev user). This is supported by explicitly setting user ID property in the ODC file.

We will update once the fix to allow free user is available

Thanks all for helping us understand the issue!

[mwmckee]

I am running Pro and have followed Tal's workaround steps by editing the ODC file.  I now do get AAD prompted but still receive the same error after entering my credentials.

Is there anything else I might be missing?

Thanks

[MattAllington]

It is a known problem for some users (free accounts and multiple account users).  I was advised by Microsoft it should take about a week to fix.  They will advice back here once it is fixed.

[NH]

Hi IlanH,

Thanks for letting us know that Free user will not able to use this feature yet.

Any ideal when MS Power BI team is able to get the 2 issues fix? Thanks.

[IlanH]

free user issue should be fixed relatively shortly - will update you once the fix is in. I assume ~1 week

[kazlik]

My problems are solved as well. The menu to download the msi package have both the x64 and x86 version. In my excitement and button highligh I selected x86 and not x64. You might consider better language and equal weight to both types on the menu.

[teroman]

I'm getting this problem, I assume it's because I'm using a free account for testing.

Will I need to get the networking bods to set up AAD too? Not sure that we are all joined up to that yet.

Thanks

col

[tarostok]

Hi all,

A question and a possible solution for those who got "Forbidden" error:

When you opened the ODC file did you get AAD sign-in window (see picture below)?

If the answer is no please try the following solution:

1. Open your ODC file in notepad.

2. Inside <odc:ConnectionString> tag add the following property: ;User ID= your PowerBI user name;

3. Save the ODC file.

4. Open it in Excel.

Now you should get the AAD sign-in window and successfully log-in.

Highly appreciate your feedback.

Thanks,

Tal

[NH]

Hi Taro,

Not familiar with ODC script. Thus need your advise is the below script to include the User ID in ODC file is correct? I'm still not able to the AAD sign-in windows prompt. Note: I'm on Free Power BI version.

</odc:ConnectionString ="User ID = PowerBI user name">

[NH]

sorry I've entered the User ID = BI account name at the wrong place. Corrected as shown but still no AAD prompted.

<odc:ConnectionString>Provider=MSOLAP.7;User ID= BI account name; .....

[MattAllington]

Hi Tal

I have tried what you recommendation but I still get the same forbidden error.  THen I get this popup (same as always happens) however now my username is pre-popluated (it was not like this previously).  The Password box was blank.  I enter my password (shown below) 

Then I get this error shown below.

[jmalone]

I am having the same issue. I first see the message: "The HTTP server returned the following error: Forbidden."

I try to connect using my Power BI account, but end up with the message "An operation that uses the database driver could not be completed."