ODC Connection Session ID for Analyze in Excel

[kjendro]

I have a dataset published to the Service. Using Analyze in Excel to create pivot tables. Many times when I try to refresh the connection, I get a "Session ID cannot be found. Either the session does not exist or cannot be found." Click ok, and it keeps popping up. Looks like once per pivot table. If I wait awhile, then it will refresh with no problems. Is there anyway to hardcode the session id into the connection properties, or some other way to improve this connection?

[GilbertQ]

Hi @kjendro 

This could happen because when you upload the PBIX it actually replaces the model, which would mean it has to re-authenticate.

In Excel there might be a setting to share the connection?

What happens if you connect to your dataset via Excel instead of Analyze in Excel? This might work better

[kjendro]

Tough to do, being that the Power BI Publisher for Excel has been retired. In Excel, I don't see any option to get data from a Power BI dataset.

[GilbertQ]

Hi @kjendro 

If you are running one of the newer versions of Excel you can do it by clickong on Data, then Get Data then Power BI as shown below 

[kjendro]

As an example, I make a change to the desktop file, save it, publish it up. Once that completes, I have a browser tab showing the workspace open. I go to the Excel file, hit refresh all, and then have to hit 'ok' 18 times. Seems weird that a session would expire in the 10 second lapse from publishing to hitting refresh.

[GilbertQ]

Hi @kjendro 

This is by design because when you log into the Power BI Service there is a session token that gets created, which can expire. When using the browser there is an option which says Keep me logged in.

While when you are using Excel this option does not exist. What is happening when you get the error is that the session has expired and the token is no longer valid. Behind the scenes it then refreshes the token, and will then start refreshing.

This also ensures that security is kept up to date.

[kjendro]

But I do have the "Keep me logged in" option enabled in the service (because I go in it 6 times a day usually). Is there a way to hard code a token in the ODC connection properties? Getting tired of clicking "ok" on my Excel report 15 times each time I refresh.