How to use TOM libraries reliably (xmla) for Power BI datasets

[dbeavon3]

I'm having a massive amount of trouble trying to reliably use the AnalysisServices TOM libraries (xmla over wire) for managing Power BI datasets.

I want to be able to create new partitions, refresh partitions, and query for the state of the data.   This is what TOM libraries are for. However there are several different types of "transient" errors that are making this difficult.  I get an very high number of these errors - probably more than ~10% of the time that I try to interact with datasets, especially overnight.  The network connecting to PBI datasets - even from anywhere on Microsoft's own Azure backbone - is terribly unreliable.  I get the feeling there are lots of flakey network components in front of the datasets, or maybe the datasets themselves are going offline while the xmla commands are being processed.

The errors seem to come in several variations ... 

 - Random 401 unauthorized messages, for no reason

 - Explict xmla errors like so: "session ID cannot be found. Either the session does not exist or it has already expired"

 - Explicit error : "The operation was throttled by Power BI because of insufficient memory"

 - And socket errors: from the networking stack: System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host

In my case, the TOM client that is connecting is also hosted in Azure.   The client runs on app service in East US and the dataset that I'm interacting with is hosted on the North Central region.

Below is an example of the issue with the "session ID" that cannot be found.  

I realize that Microsoft came up with another Power BI REST API that is less dependent on a long-running connection to the XMLA endpoints (they call it the "enhanced refresh api").  However it is really primitive, and lacks ~90% of the API functionality that is available on the TOM/xmla side of things.  

If anyone has an ideas on how to get this XMLA stuff to be more reliable, the PLEASE let me know.  I am trying to come up with some sort of pattern to understand why this stuff is so unreliable.  Perhaps my resources in the PBI are getting deprovisioned, while my connections are still active.  Perhaps there is a way to "pin" the resources to make sure that the XMLA connections aren't broken?  I'm thinking about sending DAX queries every 5 seconds for the duration of the period while I'm trying to use TOM, or some other silly trick like that.  I'll probably update this post if I ever come up with a solution.

It seems likely to me that Microsoft themselves is internally using TOM/xmla to manage their PBI datasets.  But they probably have tricks to making this stuff work more reliably, and haven't shared those tricks with customers (given that they have offered the primitive "enhanced refresh API", as an alternative).  Hopefully there will be a path to getting this stuff to be a bit more reliable.  Datasets are fairly central to what we need from Power BI, and if they are not reliable, then it detracts substantially from the entire Fabric platform.

[Anonymous · Accepted solution]

Hi @dbeavon3 ,

Here are some suggestions to help you.

1. Make sure your authentication token is valid and not expired according to error code 401. Sometimes, tokens can expire or become invalid, which can lead to unauthorized errors. Verify that the service principal or user account has the necessary permissions to access the dataset.

2. Consider upgrading Power BI capacity to higher SKUs to ensure adequate resources are available. Optimize datasets and queries to reduce memory consumption. Also, ensure that the network connection is stable and there are no intermittent connectivity issues.

While the Enhanced Refresh API may lack certain features, consider using it for operations where it is sufficient. It can provide a more stable alternative for certain tasks.

Here are some more related links that I hope will be helpful.

Handling errors in the TOM API (AMO-TOM) | Microsoft Learn

Troubleshoot XMLA endpoint connectivity in Power BI - Power BI | Microsoft Learn

Enhanced refresh with the Power BI REST API - Power BI | Microsoft Learn

Datasets - Refresh Dataset In Group - REST API (Power BI Power BI REST APIs) | Microsoft Learn

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[dbeavon3]

Now testing trick number 1....

I noticed that the "large semantic model storage format" is not enabled for one of my datasets which is just under ~2GB.  That doesn't seem particularly large to me.

... I've read the documentation about "large semantic models" several times in the past

https://learn.microsoft.com/en-us/power-bi/enterprise/service-premium-large-models

But it was never clear to me what the practical impact of this checkbox would be. 
It only says:
"When enabled, the large semantic model storage format can improve XMLA write operations performance."

This sounds like a euphemism to describe the impact of the bugs I'm experiencing.  I'm still not very sure what they mean by this.  But I'm guessing they mean some of my XMLA errors and bugs will go away, thereby "improving performance".  They should re-word the configuration option to say "Fix the buggy XMLA endpoints".   Or better yet, they should disallow all usage of remote XMLA clients (even SSMS) until the checkbox is actually checked.

I'm hoping the checkbox will decrease the number of errors that are observed in my XMLA client operations.  More to follow.

[dbeavon3]

An update.  Enabling "large semantic models", was the key to avoiding lots of the bugs (at least 95% of the random bugs from TOM operations).

However, I still get error messages like so:

The operation was throttled by Power BI because of insufficient memory

... this happens even on "reserved capacity" with an F64 sku.