Power BI Desktop connection to SSAS Tabular slow

[RahilBukhari]

Hi

I'm experiencing unusual behaviour when connecting Power BI Desktop to an on prem Tabular model (2014). Our model has 1000 calculations, any time a user connects to teh Tabular Model, the connection takes 30-40 seconds to complete (time it takes to see the meta data). This happens with every new connection. The server which hosts the tabular model shows the CPU spiking up to 30% per connection therefore 3-5 concurrent connections max out the server.

Running profiler trace on the server, i can see that for each connection, a bunch (number of calculations in the model) MDX execute statements are being fired for each calculation in the model which is the reason behind the delay. even though each MDX statement only only milliseconds to run, the number that can execute at any one time is throttled and the sheer number add up, taking on avg 30 seconds to complete.

This behaviour doesn't occur when i connect to the tabular model using Excel, where the connection is almost instant and there are no MDX execute statements in the background.

In the Power BI service this works slightly differently in that the number of connections is controlled by Power BI/enterprise gateway/Azure Service Bus rather than per user, would it be possible to share more information on how the powerbi.com service manages connections against the on prem server?

thanks

Rahil 

[Seth_C_Bauer]

@RahilBukhari I can't speak to the slowness, but for the clarification of others looking at this post... I assume one of the following: you mean DAX instead of MDX. Or do you mean multidimensional cube instead of Tabular model?

[RahilBukhari]

Hi @Seth_C_Bauer

no this is a tabular model and we are seeing "Execute MDX Script" events in the profiler which are the create scripts for each measure that we have.

[RahilBukhari]

Just as an update, turns out this is a problem in SQL Server 2014 as the underlying architecture would force every connection to first validate the model and it's calculations before allowing the user in, which in our case caused a huge delay and massive load on the server.

In SQL Server 2016, this happens only once and on the first connection after a service restart or process completion.