Need suggestions on Architecture

Hi gauthamboppana ,

According to your statement, you want to aggregate data from multiple sources in a dataflow or datamart. This can indeed pose challenges, especially regarding performance and latency. Here I will give you some suggetsions and hope it could help you.

1. When you connect to your datasource, you need to consider the connection mode of your data source. DirectQuery can be used for real-time data access, while Import mode can be used for historical data.

2. You have a large size of data, I suggest you to implement incremental refresh for your dataflows. 

For reference: Using incremental refresh with dataflows - Power Query | Microsoft Learn

3. You can aggregate data in data source before you get data. Utilize Power BI’s aggregation features to pre-aggregate data at different levels. This can significantly improve query performance by reducing the amount of data processed at runtime.

For reference:Optimization guide for Power BI - Power BI | Microsoft Learn

4. You can try Premium feature for dataflow to optmize the performance.

For reference:Configure Power BI Premium dataflow workloads - Power BI | Microsoft Learn

Best Regards,
Rico Zhou

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