Schedule Data Refresh Issue

[WeeraS]

Hi, I am new to PowerBi and  I have a problem of failing schedule data refresh.If anyone can help me to solve this problem woulld be highly appreciated.

I created few powerBi reports to my company and published to pwerBI service. I have used Ostando databae and ODBC to get the data from ostendo to my reports. For these report I have used one query(sales lines) which has over 2.3 million lines and size of my dataset is 630Mb. I used to have personal gate way and even when I had personal gateway data refresh failed every now and then. Recently i upgraded to on premise gateway and it is online always. So I guess my gateway configuration should be fine.

Somehow large datasets I have in powerBi service now keep failing data refresh. Normally it takes nearly two hours to refreh these dataset and sometime it gives me an error message saying timeout. I just want to know that my datasets are too big to refresh within the time frame given in powerBi service or are there anythingelse wrong here. How can I optimise data refresh rate to refresh dateset under 120 minutes

[v-shex-msft]

Hi @WeeraS,

As majdkaid22 said, too many columns may be a cause the timeout issue.

BTW, I think use direct query mode will be suitable for large volume records.

Currently, direct query mode not available on odbc datasource, perhaps you can submit an idea to ideas forum or change the connector which support live mode to connnect, it may fix the timeout issue.

In addition, you can try to install your gateway to the device which installed the dataset, it may increase the refresh performance and cutdown the timeout rate.

Regards,

Xiaoxin Sheng

[WeeraS]

Thanks Xiaoxin

[majdkaid22]

@WeeraS  2.3 million rows and dataset size of 630MB looks odd. Am assuming that you have way too many columns in a table or tables? (how many columns each table has?)

Be aware that PBI responds better to narrow long tables, rather than wide table with too many columns. 

Also, it's worth checking the latency between your Data source and the machine you are using for PBI Desktop. Timeout usually refers to disconnection caused by a latency. 

[WeeraS]

Thanks majdkaid22 for you quick reply,

 This paticular table contains all sales since 2008 and 39 coulumns are in this table

Would you mind tell me how can I check latency test? I did a speed test for my computer connecting to intenet and it was around 40 Mbs

[majdkaid22 · Accepted solution]

@WeeraS try to trim the columns. 39 columns are alot. maybe split your columns and create a an ID to connect both together. any Date column, make sure to have it Date instead of DateTime.

Go to the Command Prompt in windows and run a 'ping' to the server of the data source, or better check with your IT to run a Ping between your PC and the server where the Data Source is hosted.