Extreme instability / randomness in autorefresh of reports / access to directquery reports

[moizsherwani]

I am the PowerBI developer / admin of a company where I have installed an enterprise gateway on a virtual machine (running Windows Server 2012 R2). There are about two dozen datasets and around the same number of reports / dashboard (most of them are from Microsoft Dynamics CRM and some from Microsoft Dyanmics AX). I have the datasets to be auto refreshed two times a day (6am and 6pm). Now the issue is that every single day some of these reports will randomly fail to auto refresh, I have reinstalled the gateway, pulled the firewalls down (on the VM), even tried to optimize the queries but nothing seems to work (it will still randomly fail). I thought the issue might be resolved using DirectQuery so the newer / simpler reports I started doing in Direct Query but it also seems to randomly fail. The errors I get are given below (I am trying to include as much as I can so as to help someone get to the root cause)

1) From the refresh history fail I see the following 

The gateway operation has been cancelled explicitly by the user or from a timeout.

2) From the Server Manager on the VM I get the following at the exact time of the refresh failure

The certificate received from the remote server was issued by an untrusted certificate authority. Because of this, none of the data contained in the certificate can be validated. The SSL connection request has failed. The attached data contains the server certificate.

A fatal alert was generated and sent to the remote endpoint. This may result in termination of the connection. The TLS protocol defined fatal error code is 48. The Windows SChannel error state is 552.

3) Direct Query reports / dashboards

These will randomly fail with the error "Couldn't load the model schema associated with this report. Make sure you have a connection to the server, and try again", even though I can see on the VM that is it up and running

4) Starting On Premises Data Gateway

It will start by saying "Your gate is all set up". When I login it will sometimes end up saying "The gateway ABCD is online and ready to be used", other times it will say something like "your gateway is configured correctly but unreachable due to local network connectivity" and at other times it will just . It is extremely random in nature.

I am desperately looking for help (if I am to keep the little hair that is left on my head)

Thanks,

Moiz

[Anonymous · Accepted solution]

What is the performance of the Gateway like during these periods?  What is the performance of the databases like at the same time?

I often get refresh issues if my Gateway has too many requests, or if my databases are under heavy production load.

Couple of things to consider:

• Are all of your dashboards all set to refresh at the same time? Is that too much for your single gateway's hardware and its bandwidth?
• Are you connecting directly to production systems? If so are you connecting during peak periods?

[moizsherwani]

Ross, thanks so much for your reply. My responses below

What is the performance of the Gateway like during these periods? What is the performance of the databases like at the same time? I am not sure how to measure the performance of the gateway but I do know that it is a dedicated VM so it is not being used by anything other than the PowerBI Gateway. The databases are usually being accessed during work hours 8am to 5pm and so I try to do my refreshes before and after that.

Couple of things to consider:

Are all of your dashboards all set to refresh at the same time? Is that too much for your single gateway's hardware and its bandwidth? They are at the same time and I will try to change the time and see if that helps

Are you connecting directly to production systems? If so are you connecting during peak periods? Yes I am connected to the production system, I deliberately set the refreshes before and after hours (6am and 6pm) to avoid these issues.

[Anonymous]

Ok great, that sounds quite positive.  Hopefully by spreading some of the refreshes out, you will get some new information.  Either the problems will go away, indicating that the problem was the combined load, or the issue will only be present on specific refreshes meaning you can concentratrate on what is wrong with those specifically.

As for monitoring your hardware, i'm not much of a server guy but my server people have monitoring tools which measure things like CPU, Memory and Bandwidth percentages over time.  If any of them are getting to 100%, then you know you will have issues.

[moizsherwani]

@Anonymous 😄 I will call you Super Ross from now since the proposed solution of spreading out the refreshes worked like a charm. Although at the moment I have reduced the refresh from twice to once a day for now, the eventual plan is to group reports which are heavy and light at the same time so we can bring it back upto twice a day.