I'm not exactly a PowerBI expert (not at all). In this respect, I would like to have an assessment whether the architecture makes sense and possibly this architecture is responsible for the problems.
We use a Data Lake Gen2 Blob, then via an Event Grid is triggered that new Blob data is written to a database of the Azure Data Explorer. This is supposed to be read-optimized. Approximately 120,000 data records arrive per blob (day). This does its job!
Now we have tried to set up the Incremental Refresh (according to Microsoft instructions, Youtube instructions) for a PowerBI report (provided as a PowerBI Embedded report on the browser) (30 days retention, 1 day refresh). Data Folding is verifiably present. But the Incremental Refresh just doesn't want to work! The DataSet always does a Full Refresh and fails because of 3GB capacity RAM. ("More details: consumed memory 3346 MB, memory limit 3037 MB, database size before command execution 34 MB"). Increasing capacity is not an option. The architecture hardly pays off as it is.
Does incremental refresh not work with Azure Data Explorer Database?
Would it be better to use Direct Query on the database? Or even would it be better to tackle a Cosmos DB? (which is quite expensive writing/reading that amount of data - the reason we opted for Blob/Azure Data Explorer DB)
Is 120,000 rows/day too much data for PowerBI Embedded (A1 Capacity)?
Any comment very welcome.