PowerBI From Odata Dynamics NAV 2017. The Azure SQL server uses 95% power, why?

[Adamtall]

We have a problem when we use PowerBI against Odata from Dynamics NAV 2017.

The problem is when the refreshing starts in PowerBI the Azure SQL Database goes up to 95-100% processor usage.

We tried to connect directly to the Azure SQL Database instead of Odata from NAV, and the result is 15-25% processor usage when loading new data. (same database). We also tried to upgrade the SQL server to a faster server, but same result as above 95-100% processor usage.

But the whole solution is built by Odata connection.

Do you guys have any solution why Odata from NAV does this? And what can we do to improve the OData ?

[v-shex-msft]

Hi @Adamtall,

It seems like you have a huge datasource need to refresh.

In my opinion, I think the simple way to work through this issue is increase your azure system resources or switch to connect directly mode.

BTW, I haven't found any solution to simply improve the odata performance.

Regards,

Xiaoxin Sheng

[Adamtall]

Hi @v-shex-msft,

Its not that big, we tried to increase the system to double power, but we have same problem 95-100% power usage.  Refresh time with Odata is 80 minutes and with SQL connector is 2-3 minutes.

We did also a connection to the same SQL server without Odata connection and took alot more data out, and that works fine 15-30% power usage.

I think SQL connector is better anyway, but Odata shouldn't behave that way? 

Is it only me who got this problem with Odata from NAV ?

[v-shex-msft]

Hi @Adamtall,

It seems odd, but I can't reproduce on my side, perhaps you can open a support ticket and share these detail information to power bi team for further support.

Regards,

XIaoxin Sheng