Azure Data Explorer Database to Power BI Embedded with Incremental Refresh

[stanbeamish]

Hi Community,

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.

[stanbeamish]

Thank you very much for your comment.
As of the incremental refresh it turned out that using Direct Query on the Data Explorer Database is the way to go. This allows us to immediately see new data on the report now. Great.
And your word on "PowerBI Aggregations" got us to the idea, that we really need to make use of such to avoid unnecessary data loads and reduce the data queried.

[ibarrau · Accepted solution]

Hi. I'm not sure if incremental refresh works with Azure Data Explorer Database. However the issue here is that you can't make a full refresh. You need at least one full refresh to make incremental work. That's the thing with incremental, it won't fix any limitation about size because the first refresh after publishing will always be full. It will only make it faster once it's working. 

You can try turning on the large datasets option for it. https://docs.microsoft.com/en-us/power-bi/enterprise/service-premium-large-models

If non of that work, you need to consider getting a deep dive to the model, analyze vertipaq and check if you can reduce its size. Otherwise you will need to reduce data from big tables or make PowerBi Aggregations if the source can read direct query.

I hope that helps,