Forum Discussion
Dataset refresh timing improvement
Hi All,
I am facing issues with dataset refresh failure in Power BI. Here the issue and my approach in a nutshell:
1) We had around 190 column and 195 million records in our dataset, this was a csv earlier and then we moved to a sql server table.
2) There are only 5-6 dimension tables. Other than that, there were 180 plus metric columns for current year, last year and year before last year.
3) We transformed the table into such a way that now columns are pivoted to year type and metric. So we have now around 40 columns but records are increased to 3 times, around 560 million records. So we managed to reduce columns by 1/3rd but record count increased by 3 times. We thought narrow table perform better than wide table.
4) But now neither we are able to query table in SQL server, nor we are able to refresh it completely in Power BI. Even for setting up incremental refresh we need to refresh it one time fully. This is not happening.
Appreciate any suggestions, what can be done. As of now, 8 descriptive columns and 31 measure columns are present.
1 Reply
Even for setting up incremental refresh we need to refresh it one time fully.That's not strictly true. Advanced incremental refresh and real-time data with the XMLA endpoint in Power BI - Power BI | Microsoft Learn
Can you show a sample version of your data model?
In general you want to let the data model do the work (rather than doing transforms in Power Query) because the data model runs in memory.
