PBI architecture

powerbidev123 

On premises data gateway

1. Assuming your on premises SQL Server is proptected with firewall rules, you would need to set On premises data gateway to refresh your seamantic model from power bi service. 

I would highly recomment you to go through this learn path completely to know all the details

https://learn.microsoft.com/en-us/data-integration/gateway/service-gateway-onprem

2. Depending on the volume of the data that you are going to load into the semantic model and depending on the complexity of the transformations that you would want to perform within your semantic model. You need to make sure your gateway instructure is adequate. 

 

Incase if you do not want to handle all this infrastructure on your own then you can set up VNet gateways and then Microsoft will manage the infra. It would consume your Fabric capacity compute power. Please checkout the below learning path.

https://learn.microsoft.com/en-us/data-integration/vnet/overview

 

 

Gateway Connection

3. You need to setup a gateway connection to your SQL server source and grant the necessary permissions on this connection to the developer who is going to build the semantic model. You can refer the below page to know more about connection access management rules

https://learn.microsoft.com/en-us/data-integration/gateway/manage-security-roles

4. While configuring the connection make sure, you enabled "Allow this connection to use along with cloud sources" option so that you will be able to include your other cloud connections like sharepoint and Azure SQL database along with your on premises source in the same semantic model

 

Privacy settings

5. Incase if you are going to merge queries from one data source with other, make sure you configure the privacy levels properly. refer the below article for more details. 

https://learn.microsoft.com/en-us/power-query/privacy-levels

As a rule I would recommend you to choose the privacy levels of all your datasources as 'organizational'.

 

6. As long as all you keep the storage mode of all your queries in your model as 'Import' then its is not a composite model. When tables use different storage modes, the model is a composite semantic model. For DirectQuery table storage mode, the model is composite when the DirectQuery tables use different data sources.

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models

 

 

Challenges

1. Mantaining and monitoring gateway infrastructure is a challenge, incase if your on premises sources are only one or few. Then it is recommended to go with VNet gateways. 

2. Depending on your data volume and kind of data, you can consider using incremental refresh policy as ineeded. So that your refresh operations wont take longer and wont consume significant resources 

https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview

3. Unless your seamantic model is hosted in a fabric caacity you will not be able to refresh your model more than 8 times a day. If Pro workspaces features does not suit your model requirements then you need to purchase a fabric capacity. 

4. Model refresh frequency depends on what is the acceptable latency in your reports. The best strategy is that, in your ETL pipelines add a additional activity in the orchestration to refresh your model as soon as the ETL pipeline execution is completed.

https://learn.microsoft.com/en-us/power-bi/connect-data/asynchronous-refresh

 

 

Hope this answers all your questions

read my articles here: https://www.techietips.co.in/

 

 

 

 

Connect on LinkedIn

 

 

 

 

 

Hi powerbidev123 

 

1. Main challenge & design considerations

The complexity is that on-premises sources must be reached through  on-premises data gateway which becomes single point to understand both cloud service and internal network protocols while cloud sources like Azure SQL DB connect directly. This forces you to design around gateway placement and availability rather than treating all sources. You also need to standardize on query folding, star-schema design since each source ex.file-based SharePoint, relational SQL Server, cloud SQL which has different connector behavior and refresh 

Reference: https://learn.microsoft.com/en-us/power-bi/guidance/gateway-onprem-sizing

 

2. Performance, security, and maintainability implications

Performance: Gateway throughput and network to on-prem SQL Server become the bottleneck, especially under DirectQuery. Microsoft's guidance explicitly recommends setting up less frequent data refresh using incremental refresh to minimize data transfer, ensuring query folding and converting to DirectQuery or composite designs for large or low latency needs to reduce gateway load.

Security: Gateway account only needs connectivity to cloud service not the data sources where actual data source credentials are stored and managed separately per source and SSO mapping is needed if you want per user identity pass-through to on-prem sources.

Maintainability: A hybrid model means two operational domains like gateway team for on-prem, Cloud team for SharePoint & Azure SQL must stay coordinated on credentials, gateway clusters, and refresh windows

 

3.When to use a Composite Model and is it appropriate here?

A composite model combines multiple source groups where each source group can represent imported data or a connection to a DirectQuery source 

Reference - https://learn.microsoft.com/en-us/power-bi/guidance/composite-model-guidance 

 

Microsoft's guidance is clear that composite models are not meant to be default basis for your overall architecture — they should be adopted for specific use cases (https://tabulareditor.com/blog/composite-models-in-power-bi-and-fabric)  like extending an existing enterprise model, mixing storage modes for performance or letting DirectQuery on a large fact table coexist with imported small dimension tables.

 

For your scenario: yes, a composite model is a good fit. For example, you import the smaller or slower-changing SharePoint list and Azure SQL dimension data, while keeping a large on-prem SQL Server fact table on DirectQuery or vice versa this avoids moving huge volumes across gateway while still getting fast interactivity on small tables.

Reference: https://learn.microsoft.com/en-us/power-bi/guidance/composite-model-guidance and https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models

 

4. Recommended refresh strategy for mixed refresh frequencies

Enterprises typically don't refresh the whole model at one go. Instead we can use incremental refresh for large fact tables (Ex. on-prem SQL Server fact table) so only recent partitions will refresh instead of full table. Incremental refresh extends scheduled refresh by automating partition creation and management for tables that frequently load new or updated data 

Reference - https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview

Keep slow changing dimension like data like SharePoint list, reference table on less frequent schedule since they don't need daily or hourly updates. If a table absolutely needs live data, make it a DirectQuery (Import for historical partitions + DirectQuery for most recent partition) within the composite model instead of full model DirectQuery.

 

Watch query folding support per source since if filter logic does not fold to the source, the engine may have to pull all rows locally which can overload Power BI service or on-premises gateway and defeat the purpose of incremental refresh

Reference: https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview