Home / Databases / SQL Server

DatabasesHow-toIntermediate

A Power BI dashboard for SQL Agent job health across all your instances

Build a job health report in Power BI on top of a central Agent history store. Covers the data model, DAX measures for success rate and duration change, the pages worth building, refresh through a gateway and the read-only permissions it needs.

Deepak Bhardwaj3 min read

Applies toSQL Server 2016–2025 sources, Power BI Desktop / Service with on-premises data gateway

Source

Point Power BI at the central history mart (see the incremental data mart post), not at each server's msdb:

  • One connection instead of dozens.
  • History older than msdb's retention.
  • No load on production servers during refresh.

If you have only one or two servers, a view over msdb (dbo.v_AgentHistory from the conversion cheat sheet) works too.

Data model

DimServer 1──* FactJobRun *──1 DimJob
                  │
                  *──1 DimDate (on run_start date)

Use import mode. Filter FactJobRun to step_id = 0 for the job-level table, and keep a second table for step-level detail if you need it.

Measures

Runs = COUNTROWS ( FactJobRun )

Failures = CALCULATE ( [Runs], FactJobRun[run_status] = 0 )

Success Rate = DIVIDE ( [Runs] - [Failures], [Runs] )

Avg Duration (min) = AVERAGE ( FactJobRun[duration_sec] ) / 60

Avg Duration Prev 28d (min) =
CALCULATE ( [Avg Duration (min)],
            DATESINPERIOD ( DimDate[Date], MAX ( DimDate[Date] ) - 28, -28, DAY ) )

Duration Change % = DIVIDE ( [Avg Duration (min)] - [Avg Duration Prev 28d (min)], [Avg Duration Prev 28d (min)] )

Last Run Status =
VAR lastStart = MAX ( FactJobRun[run_start] )
RETURN CALCULATE ( SELECTEDVALUE ( FactJobRun[run_status] ), FactJobRun[run_start] = lastStart )

Pages worth building

Page Visuals
Overview Cards: runs, failures, success rate (last 24 h / 7 d). Table of servers with failures.
Failures Table: server, job, time, step, message (truncated), sorted newest first. Slicer by category.
Duration trends Line chart of average duration per week for selected jobs. Table of jobs with the biggest Duration Change %.
Coverage Jobs without notification, owned by people or disabled (from DimJob attributes).
Timeline Matrix: hour of day × server, coloured by number of running jobs. Shows overloaded windows.

Use conditional formatting on Last Run Status (red for 0) rather than lots of colours.

Refresh

  • Install the on-premises data gateway if the mart is on-premises; add the data source with a read-only SQL or Windows account.
  • Schedule refresh to match the collector (for example, hourly at :15 after an hourly load at :00).
  • For "is it failing right now", alerts by email are still the primary signal. The dashboard is for trends and reviews.

Permissions

The refresh account needs only SELECT on the mart schema:

CREATE USER [CONTOSO\svc-pbi-gateway] FOR LOGIN [CONTOSO\svc-pbi-gateway];
GRANT SELECT ON SCHEMA::agent TO [CONTOSO\svc-pbi-gateway];

Use row-level security in the dataset if application teams should see only their own servers or categories.

Using it

  • Weekly ops review: Failures page plus the top 10 duration changes.
  • Capacity planning: Timeline page before adding new jobs to a busy window.
  • Audit: Coverage page to keep job standards on track.

Tip

Put a "data as of" card on every page (MAX(FactJobRun[run_start])). People trust a dashboard more when they can see it's fresh.

Related fixes