Refresh Schedules and the On-Premises Data Gateway

// Installing and configuring the On-Premises Data Gateway

// Step 1 — Download the gateway installer
// Service → Settings (gear icon) → Manage gateways → Download gateway
// Or: microsoft.com → search "Power BI gateway download"
// Install on a machine that:
//   - Is always powered on (not a laptop that gets closed)
//   - Has network access to the data source (SQL Server, file share, etc.)
//   - Has outbound HTTPS access to Azure Service Bus
//   - Has 4+ GB RAM and a dual-core CPU minimum

// Step 2 — Register the gateway with the Service
// During installation, sign in with your Power BI account
// Give the gateway a name: "Corp-Gateway-01"
// Choose recovery key (store this securely — needed to restore)

// Step 3 — Add data sources to the gateway
// Service → Settings → Manage connections and gateways
// Find your gateway → "+ New" → Data source
// Data source type: SQL Server
// Server: corp-sql-01.internal.company.com
// Database: SalesDB
// Authentication: Windows (Kerberos) or Basic (username/password)
// Test the connection → Save

// Step 4 — Connect the dataset to the gateway
// Service → Dataset → Settings → Gateway connection
// Expand the gateway cluster → map each data source
// Data source in dataset     → gateway data source
// corp-sql-01/SalesDB        → Corp-Gateway-01 / SalesDB
// Click Apply

// Step 5 — Test with a manual refresh
// Dataset → Refresh now
// Monitor progress: Dataset → Refresh history → Latest entry

// Setting up Incremental Refresh — Power BI Desktop

// Prerequisites:
// - Power BI Premium capacity or Premium Per User licence
// - The source table must have a date/datetime column for partitioning
// - Source must support query folding (SQL Server, Dataverse, etc.)

// Step 1 — Create two parameters in Power Query
// Both must be named EXACTLY (case-sensitive):
//   RangeStart  (type: Date/Time)
//   RangeEnd    (type: Date/Time)
// Set current values to any test range: e.g. 2024-01-01 to 2024-02-01

// Step 2 — Filter the date column using these parameters
// In the Orders query, add a filter step:
#"Filtered by date" = Table.SelectRows(
    #"Changed Type",
    each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd
)
// Power BI will dynamically inject the correct date range
// for each partition during refresh

// Step 3 — Configure Incremental Refresh policy
// Right-click the table in the Fields pane → Incremental refresh
// Archive data starting: 3 years before refresh date (keep history)
// Incrementally refresh data starting: 7 days before refresh date
// Detect data changes (optional): use a LastModified column
//   if rows can be updated after insertion

// What happens after the first full refresh:
// Historical data (>7 days old): stored in fixed partitions, never re-queried
// Recent data (last 7 days): re-queried on each refresh
// New day's data: added as a new partition
// Net result: a 10-year table refreshes by querying only the last 7 days

// Viewing refresh history — Service
// Workspace → Dataset → "..." menu → Refresh history
// Shows a list of recent refresh attempts:
//   Status: Completed / Failed / In Progress / Cancelled
//   Start time, End time, Duration
//   Error details (click "See details" on failed refreshes)
//   Which tables were refreshed and how many rows loaded

// Reading a failure error in the refresh history:
// Click "See details" on a failed refresh row
// The error panel shows:
//   Exception type: e.g. "MashupException" (Power Query error)
//                       "DataSourceError"   (connection error)
//                       "GatewayError"      (gateway offline)
//   Error message: the full text of the exception
//   Time: which step failed and at what time
// Copy the error message for searching documentation

// Refresh history retention:
// Power BI keeps 60 days of refresh history
// For longer audit trails, use Power Automate to log
// refresh status to a SharePoint list or SQL table

// Proactive monitoring with Power Automate:
// Create a flow triggered by "Power BI Dataset refresh completed"
//   Condition: if Status = "Failed"
//   Action: send Teams message to the operations channel
//           with the error details and dataset name
// This provides immediate Slack/Teams notification
// rather than waiting for the email that arrives minutes later