Where a Self-Service Report Actually Gets Its Data

A self-service BI report can look current while displaying yesterday's imported data. To explain a number, trace the complete path from the source transaction to the visual.

Draw the Route Before Inspecting the Number

Start with the report, its semantic model, and every source used by that model. Include files, spreadsheets, dataflows, database views, and intermediate tables. The visible chart is only the final step.

Write down where filtering, joining, grouping, and calculation occur. A source query may already exclude cancelled orders before the report applies its own filters. Repeating the same rule later can make diagnosis confusing.

Ask which system owns the business definition and which system merely presents it. Record a contact for each stage. A diagram without ownership still leaves the wrong person receiving the failure notification.

Establish the Database Context

When SQL Server supplies the data, confirm the exact server, database, and account used by the connection. A development connection can return convincing but irrelevant results. Run this check using the approved reporting connection.

SELECT @@SERVERNAME AS server_name,
       DB_NAME() AS database_name,
       ORIGINAL_LOGIN() AS original_login,
       USER_NAME() AS database_user,
       SYSUTCDATETIME() AS checked_at_utc;

This identifies the connection used for the check, not every connection used by the published report. Compare it with the semantic model's configured source. Avoid assuming that a local desktop test uses the service's credentials.

Review views and stored queries before comparing raw table totals. A report may consume a curated object with deliberate exclusions. Reproducing its result requires the same transformation rules.

Separate Source Freshness From Refresh Freshness

Imported models hold a copy of data from a refresh. DirectQuery generally queries the source during report interactions, subject to the product's behavior and caching. Composite models can mix access patterns within one report.

A refresh timestamp does not prove that upstream data arrived on time. The source load may have stalled before the report refreshed successfully. Track the source business cutoff and the model refresh separately.

CREATE TABLE #ReportSource
(
    OrderId int PRIMARY KEY,
    OrderDate date NOT NULL,
    LoadedAtUtc datetime2(0) NOT NULL,
    Amount decimal(12,2) NOT NULL
);
INSERT #ReportSource VALUES
(1, '20260920', '2026-09-21T02:00:00', 100.00),
(2, '20260921', '2026-09-22T02:00:00', 150.00);
SELECT COUNT_BIG(*) AS source_rows,
       MAX(OrderDate) AS latest_business_date,
       MAX(LoadedAtUtc) AS latest_load_time_utc
FROM #ReportSource;

This small temporary example separates business dates from arrival times. Real pipelines need an explicit successful-load record, especially when no rows arrive. A maximum row timestamp alone cannot distinguish an empty successful load from a missing load.

Check Credentials and the Gateway

A published model needs an authorized way to reach its data source. For applicable private-network sources, a configured gateway provides that connection path. The gateway and the source credentials solve different parts of the problem.

Inspect connection mapping, credential validity, gateway availability, and permissions. A report owner changing roles can disrupt an otherwise unchanged refresh arrangement. Plan ownership transfer and credential maintenance before that happens.

Test the least-privileged reporting identity against the required objects. Do not solve an unexplained connection failure by granting broad administration rights. Capture the actual error and resolve the specific missing access or configuration.

Reconcile the Same Slice of Data

Compare identical dates, statuses, time zones, and business units before arguing about totals. Check whether the date filter uses order date or load date. Both may be reasonable, but they answer different questions.

-- Continue in the same session as the temporary table.
DECLARE @StartDate date = '20260921';
DECLARE @EndDate date = '20260922';
SELECT COUNT_BIG(*) AS order_count,
       SUM(Amount) AS order_amount
FROM #ReportSource
WHERE OrderDate >= @StartDate
  AND OrderDate < @EndDate;

The half-open interval makes the requested boundary explicit. In a real report, also inspect relationships, row-level security, and measure definitions. A correct database query can disagree with a differently scoped visual.

SELECT OrderDate, COUNT_BIG(*) AS order_count,
       SUM(Amount) AS order_amount
FROM #ReportSource
GROUP BY OrderDate
ORDER BY OrderDate;

Break the comparison into small groups until the first difference becomes visible. Preserve the query, filter values, and observation time. A repeatable comparison is more useful than two screenshots captured at different moments.

Assign the Call Before the Failure

Name an owner for the source load, connection path, semantic model, and business definition. Configure refresh-failure notifications to reach an accountable person. Include a replacement contact for absences or ownership changes.

Document how to distinguish a stale source from a failed refresh or an incorrect measure. Show users a meaningful data-freshness indicator when possible. The report becomes dependable when its data path is visible and its failures have an owner.

A report is not just a visual, it is a chain of data decisions and responsibilities.

This post was rewritten from scratch in September 2026. The original, published on 2010-10-15, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.