Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Data refresh problem on a fact table

Hello, I'm having a data refresh issue in Power BI for a fact table in my dataset. I have configured a data refresh for my data set every 2 hours and it is happening without fail. The source is Azure SQL Server and the scheduled task takes 15-25 minutes to update the Power BI dataset.

 

My dataset was developed as a star schema. It includes 14 dimension tables that hardly ever change and 2 fact tables. A fact table containing 361892 rows and which updates correctly but a fact table which contains only 65259 rows and which does not load completely.

 

These 2 fact tables are the origin of views in SQL. When I open the Power Query in Power BI Desktop, I sometimes see the data but when I go back to the dataset I don't see the same data. And this despite the fact that I updated my dataset. After some time, the data appears in my dataset.

 

I tried clearing the cache in my Power BI Desktop and then republishing in the Power BI service but nothing worked. I started reading for Incremental Updates but really is this the solution?

Need Help

3 Replies

  • gauthamboppana's avatar
    gauthamboppana
    Solution Specialist
    4 years ago

    hey Anonymous  - did you check each step in Power Query eidtior? Windering, if there are steps that are filtering the data. Also, assuming tthe table is set to 'Include in refresh' in the power query editor.



    Lastly, did you get any error after importing? You can check the errors in Power Query editor.

    Did I answer your question? Mark my post as a solution! If not, please feel free to ask me.

    Also, I would ❤ Kudos if my solution helped.  It is a token of appreciation!

    Thank you very much !



     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi gauthamboppana ,

    I checked and the 2 parameters 'Enabled load' and 'Include in report refresh' are well selected.

     

    Also, I looked for filters and this is what I see: = Table.SelectRows(SomeTableName, each true). As I understand from the documentation, this shouldn't prevent the data from loading.

     

    Finally, I have no errors when importing the data.

     

    My version is Power BI Pro. Do you have any other suggestions?

     

    Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi gauthamboppana ,
    Maybe another possible solution. I don't know if this could have a direct impact on loading a table.

    At the beginning of my project, I had problems loading data into SQL Server and moreover into my Power BI dataset. So I decided to increase the minutes timeout for the CommandTimeout to 60 minutes in Power Query.

    • [CommandTimeout=#duration(0, 1, 0, 0)]

    In the meantime, I realized that the error was at the level of the SQL query.

    So I modified my query at SQL Server level to improve performance and this is reflected in Power BI. However, I never returned the initial value. I left the 60 minutes. Do you think that this parameter could still have repercussions on the loading of this table.