Power BI to merge live and history data

[Anonymous]

Hi Team,
This requirement is to have 60 to 90 days of data from "traces" table in "Azure Application Insights".

We need a live data. We tried more approaches, but everthing have limitations.

The other approch I tried, combining direct query with import query with UNION. But, when we are creating a table with UNION, it act as an import mode. We tried other approaches as well.

Now, we have an approach. For past two hours, we will keep a "Power Automate" button in Power BI and configured to refresh the dataset. And we do have 20 tables(for each Interface). When I click the button, it will refresh all the table.

For history data, we are creating a m query dataflow with 20 tables for 60 to 90 days of data with an Incremental refresh. We are pullig it in Power BI, merging two queries for 20 interfaces and creating a report. A report have 3 pages with a drill down for all the Interfaces. 20*3 = 60 Pages.

But, we are facing a chanllenges. The table "Retail Transaction" will have 5 to 10 million records per day. And if we are combining this Retail Transaction with other Interfaces, we will hit heavy performance.

Can you please suggest on this?

Thanks,

Banupriya

[v-jingzhang]

Hi @Anonymous 

If the data is huge, you may consider reducing the data model size by removing unnecessary data from tables. If your report is not going to display the detailed information of single transactions but to show statistics data of some date granularity, you can aggregate data to that granularity in the data source in advance, then import and merge this aggregated data. This will greatly reduce the data size and improve the performance. 

Reference for reducing data model size: 

Data reduction techniques for Import modeling 

Top 5 tips to reduce your Power BI data model size 

How to reduce your Power BI model size by 90%! 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.