How to fix AppInsights 45K rows limitation, while trying to get pageViews or custEvents table data into PowerBI?

Question

Hello Team, I have been trying to get AppInsights (pageViews and custEvents ) table to be stored and incrementally refreshed from PowerBi Service. However, I am unable to load all data into powerBi desktop/service as there is a limitation on AppInsights, i.e) only ~45K rows are fetched per query. Currently I am using invoked custom function query looping method from PowerBI to send out individual query per day/per month basis and appended all the query output into single table. Is there a way to avoid this limitation from AppInsights end?

Thanks.

Answer 1

Hi, @sharmsky I have been trying to get AppInsights (pageViews and custEvents ) table to be stored and incrementally refreshed from PowerBi Service. However, I am unable to load all data into powerBi desktop/service as there is a limitation on AppInsights, i.e) only ~45K rows are fetched per query. Currently I am using invoked custom function query looping method from PowerBI to send out individual query per day/per month basis and appended all the query output into single table. Is there a way to avoid this limitation from AppInsights end? Optimize Query Performance: Before exploring workarounds or alternative approaches, it's essential to ensure that your queries are optimized for performance. This includes: Narrowing down your query criteria: Instead of fetching all data at once, consider filtering the data based on relevant time ranges, specific attributes, or events of interest. Utilizing WHERE clauses effectively: Narrow down your queries by specifying conditions in the WHERE clause to retrieve only the necessary data. Incremental Data Loading: Instead of fetching all data in one go, consider implementing incremental data loading strategies. This involves fetching data in smaller batches or chunks and appending them incrementally to your Power BI dataset. You can achieve this by: Utilizing date/time filters: Fetch data for specific time periods (e.g., daily or monthly) and append them to your dataset. This can be automated using Power BI's scheduled refresh feature. Implementing pagination: Fetch data in batches using pagination techniques, where each query retrieves a subset of rows, and subsequent queries fetch the next set of rows until all data is retrieved. Azure Data Explorer (ADX) Integration: Consider leveraging Azure Data Explorer (ADX) as an intermediate layer between Azure Application Insights and Power BI. ADX is optimized for querying and analyzing large volumes of data in real-time. You can: Ingest data from Azure Application Insights into ADX, where you can perform complex queries and aggregations efficiently. Use Power BI to connect to ADX and visualize the summarized or aggregated data, reducing the amount of data transferred directly from Application Insights to Power BI. Azure Data Factory: Azure Data Factory can be used to orchestrate data movement and transformation workflows. You can: Use Azure Data Factory to extract data from Azure Application Insights, transform it as needed, and load it into a data store that Power BI can connect to. Implement incremental data loading and schedule data refreshes using Azure Data Factory pipelines. Optimize Power BI Data Model: Once the data is loaded into Power BI, optimize your data model to improve query performance and reduce memory usage: Utilize data compression techniques and apply appropriate data types to minimize memory consumption. Implement relationships and hierarchies to organize and structure your data model efficiently.

                          By implementing these strategies, you can overcome the limitation with Azure Application Insights and efficiently load and analyze pageViews and custEvents table data in Power BI, ensuring a smoother and more effective data analysis process.
                          -->I hope this will solve your problem :)