Dataflows, Gateways & Scheduled Refresh in Power BI Service

In a mature enterprise, multiple departments often require the exact same core entities (e.g., standard Dim_Customer, Dim_Date, and Dim_Product tables). If 10 different developers write 10 separate Power Query ETL scripts connecting to the same transactional database, it creates redundant database load and conflicting business logic.

The architectural solution is Power BI Dataflows.

1. What is a Power BI Dataflow? (Self-Service Cloud ETL)

A Dataflow is Power Query running in the Power BI Cloud:

• Dataflows extract, clean, transform, and load data from source systems.
• The transformed tables are stored in Azure Data Lake Storage Gen2 (or Microsoft Dataverse) as standardized Common Data Model (CDM) entities.
• Developers then connect Power BI Desktop to the Dataflow rather than the raw database!

2. On-Premises Data Gateway: Bridging Cloud to Ground

When your source data resides on an internal company server (e.g., on-premises SQL Server, local file share) behind a corporate firewall:

• The Power BI cloud service cannot directly reach into your private network.
• You must install an On-Premises Data Gateway:

Gateway Modes:

1. 1
   Standard Mode (Enterprise): Serves multiple users, multiple datasets, and supports DirectQuery and scheduled refreshes. Installed on a dedicated server.
2. 2
   Personal Mode: Runs only under a single user's Windows login. Shuts down when the user logs off. Not recommended for enterprise production.

3. Configuring Scheduled Refresh

1. 1
   In the Power BI Service, open your dataset's Settings.
2. 2
   Expand Gateway and cloud connections and map the dataset to your active Gateway cluster.
3. 3
   Expand Data source credentials and sign in.
4. 4
   Expand Scheduled refresh:

• Toggle to ON.
• Select frequency: Daily or Weekly.
• Add refresh time slots (Power BI Pro supports up to 8 scheduled refreshes per day; Premium supports up to 48 per day).
• Configure failure notification emails to alert the BI team if a refresh fails.