← Case Studies

Rejecting DirectQuery: choosing Import mode for the primary semantic model

Status: Adopted Scope: Primary SAP reporting semantic model, ~50 users Owner: Dylan Reusch

Context

The reporting platform needed to turn SAP transactional data into trusted, standardised Power BI reports for around 50 users across the business. Direct connectivity from Power BI to SAP — DirectQuery or Live Connection against SAP systems — was ruled out for the main reporting datasets on security grounds: the platform could not depend on live, unmediated access into SAP from the reporting layer. That left an open question — how to get data that was both current enough to be useful and safe to expose broadly — which this decision resolves.

Options Considered

Rejected

DirectQuery via CDS view

  • Near real-time data, no scheduled refresh needed
  • Tested against a CDS view as the most promising direct-access path
  • Report interactivity degraded noticeably under real usage — slow visual load, sluggish slicer response
  • Drove high capacity consumption per query, disproportionate to the number of users
Adopted

Import mode via secured extracts

  • Data lands via secured extracts to SharePoint, then loads into the semantic model on a scheduled refresh
  • Keeps SAP itself outside the reporting layer's direct reach
  • Query performance decoupled from source-system load — consistently fast for end users
  • Trade-off: data freshness bound to the refresh schedule, not real-time

Decision

Import mode via secured extracts became the standard for the primary trusted-reporting semantic models. DirectQuery was not adopted for this workload after testing showed the performance and consumption cost outweighed the freshness benefit at this scale. The decision was documented so future model development would start from Import mode by default, rather than re-litigating the same trade-off per report.

Evidence

Report load time
100
 
19
Capacity consumption / query
100
 
24

Indexed values (DirectQuery via CDS view = 100) illustrating the relative gap observed during testing. Figures are representative, not the exact production numbers, to avoid disclosing operational data from the source environment.

Consequences

Gained

  • Predictable, fast report performance independent of source-system load
  • Lower and more predictable capacity consumption
  • A clear security boundary — SAP is never queried live from the reporting layer
  • A default architecture that new report development can follow without re-deciding the trade-off

Accepted trade-offs

  • Data freshness is bound to the refresh schedule, not real-time
  • Refresh reliability becomes an operational responsibility in its own right, monitored as part of platform health