PUG Exchange

1.  Error in Analyze in Excel

Posted 6 days ago
Hi to All,

Hoping for some help here.

Recently I imported an excel file into Power BI services as data set. But as I tried the Analyze in Excel feature, it automatically downloads the ODC file and then it asked me to put my credentials here (see picture below)

error
as soon as I clicked the "Sign in" button. It pops up an error message like this

Note: I update my Analyze in Excel.

Thanks in advance for the answer.


------------------------------
Timothy Bryle Serac
Data Warehouse Admin

------------------------------


2.  RE: Error in Analyze in Excel

Posted 5 days ago
​Not enough information.

The screenshot indicates you are pointing to an Azure resource, not an Excel file. Where is the file stored? Is it the sole source on the query or could the query also be looking for as Azure resource as well as the file?

------------------------------
Simon Hill
Manager
TTX Company
Solihull
------------------------------



3.  RE: Error in Analyze in Excel

Posted 5 days ago
The Analyze in Excel function adds a data source to your computer via the ODC file that can connect to the PowerBI model you have uploaded into the cloud.  That connection works over an internet connection only...you don't need to be on your home network, just like with PowerBI in the first place.  The reference you are seeing to Azure I believe is the authentication into the PowerBI  model for that data source.  Not sure why it's using Azure...but I see the same thing and I can guarantee you that my data sources are NOT in Azure.

Users who need to refresh the data must have the Analyze in Excel data source/function added to their computer prior to trying a refresh.  Also, the ODC file that was used to create any Excel report must be available locally to the user.  I have tried storing it OneDrive and can't get it to work that way.  And storing on a shared drive on your network kind of defeats the purpose of it.  So, for us we simply made sure that the ODC file was on their laptop or desktop, and pointed their copy of any Excel report that connects to the PowerBI model to that local copy of the ODC file.

I must qualify all of the above:  I have learned all of this empirically...I can't provide you any reference to instructions or blogs where an expert discusses how to make this work.  But I can tell you that this is a pretty important function for us, and we spent quite a few hours trying different things before we ended up with the "plan" I outlined above.

Hope this helps :-)



------------------------------
Richard Ward
Vice President - Controller IPD
Storm Industries, Inc.
Torrance CA
310-602-5313
------------------------------



4.  RE: Error in Analyze in Excel

Posted 5 days ago
​Richard,

I bow to your greater experience. When I have done this, I've used a source via a data gateway, so I've not seen the same issue. I'm wondering if a gateway would eliminate this?

Simon

------------------------------
Simon Hill
Manager
TTX Company
Solihull
------------------------------



5.  RE: Error in Analyze in Excel

Posted 5 days ago

Hi,

What I have suggested to people who want to use Excel for Power BI Dataset's is to use the Power BI Publisher for Excel.

The difference here is that it installs as an Excel Add-In, so it is always available.
Then using this Add-In you can then sign into the Power BI Service and select which datasets you want to connect to.
Any dataset in any workspace that you have access to, you will be able to connect through.

https://powerbi.microsoft.com/en-us/excel-dashboard-publisher/

With regards to the Log-In Window that you see, that would be due to the fact that Power BI is running on the underlying Microsoft Azure Platform, which does the authentication back to your Azure Active Directory to log you in correctly. The difference is when you are in the Power BI Service, it just masks it slightly differently.



------------------------------
Gilbert Quevauvilliers
Power BI & Data Analytics Colnsultant
FourMoo
------------------------------



6.  RE: Error in Analyze in Excel

Posted 4 days ago
LOL...no bowing of any kind allowed!  I'm a humble traveler on the PowerBI journay as well!

I don't know what causes that error...I see it as well sometimes.  Keep in mind...it's telling you that the operation completed successfully.  I truly wonder if it's just a bad string of text which should otherwise say something else.  The thing you need to validate is this:  is the data updating or not?

With respect to the use of a gateway...
All of my projects are based upon the use of the Enterprise Gateway.  It's currently configured to provide me access to our Dynamics GP SQL Server...and also a couple of Excel files.  I don't think it matters whether you are manually pushing the data into the PowerBI service, or using a gateway to perform the update for you.

That also brings up a good thing to understand (once again, my empirical observations...not from user manual or anything):

Updating the link from Excel to the PowerBI model DOES NOT refresh the model data in the cloud.  Think of it this way:  The Analyze in Excel link is a one-way pipe to download whatever currently exists in the cloud.  You use either a gateway or a manual update to push refreshed data up to the cloud.

I'm pretty sure this is true...but again would have to defer to Microsoft or someone who knows the true design of the architecture.

------------------------------
Richard Ward
Vice President - Controller IPD
Storm Industries, Inc.
Torrance CA
310-602-5313
------------------------------



7.  RE: Error in Analyze in Excel

Posted 2 days ago

Hi Richard,

Your observation is indeed 100% correct.

When you refresh your Excel file it is simply loading the data that is stored in the Power BI Data model in the cloud.

If you want to refresh your Power BI Data model, as you mentioned (Also correct), you have to do this with the On-Premise (Enterprise Gateway).
And as a side note when a refresh is initiated data is always pushed from your On-Premise data sources to Power BI. And never the other way around.



------------------------------
Gilbert Quevauvilliers
Power BI & Data Analytics Colnsultant
FourMoo
------------------------------