cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stanbeamish
Frequent Visitor

Azure Data Explorer Database to Power BI Embedded with Incremental Refresh

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.

1 ACCEPTED SOLUTION
ibarrau
Super User
Super User

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,


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

View solution in original post

2 REPLIES 2
stanbeamish
Frequent Visitor

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
Super User
Super User

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,


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors