sqliteviewpowerbirefreshfolding

To speed up data refresh should I create a view or use query folding?

I have an SQLite database of 5GB which gets updated few times a day and is used to refresh a PowerBI dashboard. While below 1GB I could refresh the dashboard in under a minute, but now takes around 20 minutes.

  1. Should I create views so merges, joins, etc. are made in the view instead of loading the table itself and using Power Query to perform data manipulation?

  2. Should I use incremental refresh? Is it possible in SQLite?

Solution

  • I would implement views on the database side in this case. This is in my eyes the benefit of having control of the DB yourself - pushing these data transforms to the DB instead of tinkering in Power Query is a major benefit.

    When the views are set up, work on incremental refresh if the SQLITE3 connector supports it, but if it doesn't use the "regular" SQL Database connector it may not do so, according to the official documentation.