Need suggestions on Architecture

[gauthamboppana]

Hey all,

Our company has the below architecture set up in terms of datawarehouse and  ETL.
- Has 20 subsidaries

- Each subsidaries has its own on-prem data warehouse

- In the PBI Premium capacity, we got one workspace dedicated for each subsidary 
- Ingetsing respective DWH data into dedicated workspace using Data Flows

We would like to aggregate all the data into one data flow or datamart. However, wary of the potential permance issues or latency in the data. Has anyone been through similar scenario? Appreciate if you have any suggetsions !

P.S. Our company doesn't want to move to cloud datware house or upgrade to Fabric. 

Thank you

[Anonymous · Accepted solution]

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.