AshQs, thank you for the reply. Yes, as mentioned in the service limit, the only way forward is to "reduce results by using query scope, time range, and filters in the query."
Also, note that PowerBI is integrated with log analytics. It’s easy to create your PowerBI dataset directly from logs – please see this article: https://learn.microsoft.com/en-us/azure/azure-monitor/logs/log-powerbi
The PowerBI integration for Logs works with Logs API – therefore the limitation is not the UI limitation of 30K results but the API limitation of 500k results, therefore this should help with your scenario.
If you need more then 500k results per dataset run, there are a host of solutions you can use:
- Using PowerBI dataflows and incremental refresh to break the query and incrementally refresh it - https://learn.microsoft.com/en-us/azure/azure-monitor/logs/log-powerbi#collect-data-with-power-bi-dataflows
- Using Microsoft Power automate (https://powerautomate.microsoft.com/en-us/) or Azure (https://learn.microsoft.com/en-us/azure/logic-apps/logic-apps-overview) to run periodic refresh and write back to Logs
The following link could also help you, which shows steps to query LA workspace data as an ADX cluster - https://learn.microsoft.com/en-us/azure/data-explorer/query-monitor-data
You are right, with the service limits in place, if you want to import all the data to PBI dataset using MQuery, going through the ADX cluster/Azure storage would be a good option. For more details, see Log Analytics - Other Export options. Note that there are 2 steps that will have to be done in this case -
- Export historical data first, in case you need the existing data from LA workspace
- Setup continuous export, using Diagnostic settings, so that future data also gets to the destination.
On a side note, I would suggest using the query feature of LA workspace to process, transform, aggregate/summarize the data in query to only import the required content to PBI Dataset. This might reduce the output record count and no additional infrastructure would be required to set up.
I hope this helps.