Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
11 months ago
Solved

Problem with refresh and scheduled refresh in PowerBi service.

Hi whenever i refresh my semantic model, it throws this error. Anyone knows how to fix it? I have set up gateway to access the data source which is stored in SQL server.    Data source error: [Val...
Need Help
  • arvindsingh802's avatar
    arvindsingh802
    11 months ago

    it looks you account does not have correct access

    Microsoft SQL: The SELECT permission was denied on the object 'new_contractproduct', database 'SGH_MSCRM', schema 'dbo'.[/ccon]"}},

     

    Validated you are using same credentials that is working fine in desktop

Anonymous's avatar
Anonymous
Not applicable
10 months ago

Hi,

Thank you arvindsingh802 for your help. The database admin has granted me access and I am able to connect to the server already. However, there is a new problem whereby I have "timeout error" when querying from the database.

 

So, I am building a dashboard which consists of multiple tables (shown in the screenshots). When i "include in report refresh" for the master tables (UCB_Processing, Master_UCL and Master_WJ) and published to PowerBi Service, the refresh failed and gave me this error (in the 2nd screenshot).  So, I tried another method of excluding these 3 master tables from refresh, then the refresh is successful BUT it is not fetching the latest data.
For some context, UCB_Processing has 13,231 rows, Master_UCL has 8,105 rows and Master_WJ has 2,506 rows. Could it be that the dataset is too huge so the query timeout? 
 
All of my datasets are queried from MSSQL Server with SG and INA are on cloud, and the rest of the countries are queried from on-premise MSSQL Server. Data gateway has also been set up to connect to those on-premise databases.
 
To rectify the problem, I had asked my colleague to increase the timeout limit for the gateway to fetch the data and he has set it to 4 hours. For my SQL command, I have changed the CommandTimeOut in the M code to 4 hours maximum too (as shown in the screenshot). 
 
I have tried incremental refresh too, but it said M query not foldable error. 
Could you guide me and point out where the possible error is? As I have been debugging this for weeks.
  • v-aatheeque's avatar
    v-aatheeque
    Icon for Community Support rankCommunity Support
    10 months ago

    Hi Anonymous 

    Thank you for providing the details and screenshots. Based on the error message and your description, the refresh failure appears to be related to a gateway or mashup engine timeout during data retrieval from the SQL Server sources. Even though the timeout duration has been increased both in the gateway configuration and within the M code, the issue is likely linked to query performance or folding limitations for the larger master tables.

    Here are some points and recommendations to help address the issue:

    1.Reduce data volume, simplify joins, and apply filters early.

    2.Update the Execution Timeout value in the Mashup.Config file.

    3.Ensure sufficient CPU/RAM, restart service, and update to the latest version.

    4.Test the SQL connection in SSMS/Excel and check for network delays.

    5.Run refreshes during off-peak hours to avoid heavy load.

    Hope this helps !!
    Thank You.