Live Connection to SSAS tabular takes long time to open

[Anonymous]

Hi all

I have an SSAS Tabular model with 263 tables and two Perspectives.

When I connect to the model with Power BI for RS with a live connection, it takes around 1 minute to receive the list of tables.

I created an Extended Event Session on the SSAS Instance to trace down what happened. I found two Events, which appears at each connection.

There is no difference when I connect to the database within a few minutes. It always takes the same time to connect.

Duration (ms)	Event Class	Event Text Data
14'304	DISCOVER_END	<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><CATALOG_NAME>DWH_Tabular_Model</CATALOG_NAME><PERSPECTIV...>2.5</VERSION></RestrictionList>
15'515	DISCOVER_END	<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><CATALOG_NAME>DWH_Tabular_Model</CATALOG_NAME><PERSPECTIV...>2.0</VERSION></RestrictionList>

These two events are those who need the most time. All other events are completed within a few milliseconds.

Please note that both Events are almost identical, except for the VERSION — once 2.0 and once 2.5.

I removed the two perspectives, but no change to the load time.

I tried Excel 2019, and it's a little bit different.

Excel causes three different events while opening the connection and needs 29'343 ms to complete.

It's possible to avoid these double events in Power BI Desktop?

I went through the options but didn't find anything which points to something interesting.

Any help is greatly appreciated.

Regards

Salvatore

P.S.: This is not the same issue as described here, as it always takes a long time to connect, regardless when the last connection to the instance created.

[Anonymous]

Hi @Anonymous ,

Thanks for sharing.

In my opinion, the size of the dataset will affect the connect speed. In addition, I noticed that you have Development machine and Prod machine, hardware of the machine, resource usage and network will also have an impact to the connect speed.

Best Regards,

Jay

[Anonymous]

Hi Jay

You're right.

I took a part of the model and created a separated model. Now the connection is made with a few seconds, and the response is swift.

What I don't understand is:

• Why Power BI Desktop executes two separated DISCOVERY operations when connecting.
• Why the amount of data within the tables is related to the time needed to load the structure

It looks a little bit strange to me.

Especially the second point is strange. I understand that it takes some time to get all the Metadata for the structure. But why it takes longer when more data is in the model?

Regards

Salvatore

[Anonymous]

More tests are done:

A colleague created a copy of the model, but without processing it.

Power BI was connected after a few seconds.

On the Development machine, where we have only the actual and the previous year of data, the connections took 26.3 seconds.

On the Prod machine, with several years of data, it took 40.6 seconds.

This doesn't make any sense.

Why it takes longer to get the Metadata Informations, only because there is more data in the model?
This shouldn't be the case,

Regards and have a nice weekend

Salvatore

[Anonymous]

I've done a few more tests:

• Created a small perspective with only two tables - No effect. Still two Discoveries with the same duration
• Deployed the Report to SSRS. Interestingly SSRS executes only one Discovery, which takes around 20 sec.
  Why Power BI Desktop needs two of them?

Regards

Salvatore