Tuning and Troubleshooting Federated Query Performance in Datasphere

Explanation

Federated access (virtual tables and federated views built on top of remote connections) trades storage duplication for runtime dependency on the source system's ability to execute pushed-down SQL efficiently. When federation performs poorly, the root cause is almost always one of: (1) queries that cannot be pushed down and instead pull large row sets into Datasphere for local processing, (2) joins across two different remote systems that force a client-side join, (3) source-system resource contention, or (4) network latency between Datasphere and the source. Start troubleshooting by isolating the layer. Run the federated view or virtual table in isolation with a simple SELECT and minimal filters to confirm baseline latency. If that alone is slow, the problem lies in the connection or source system, not your modeling. Compare this to running the equivalent query directly against the source (for example through a native SQL client for an HANA source, or the source system's own reporting tool for SAP-based connections). If direct access is fast but federation is slow, the issue is in how Datasphere is generating the pushdown SQL or how the connection is configured (parallelism, fetch size, timeout settings). A common performance killer is cross-source joins: joining a virtual table from Source A with a virtual table from Source B. Datasphere cannot push this join into either remote system, so it must fetch both result sets and join them locally, which is expensive if either side returns a large volume of rows. The mitigation is to push filters as far upstream as possible (apply filters directly in the federated view definition rather than downstream in a consuming model), reduce the columns projected from each side, and where feasible, replicate at least one side into Datasphere as a persisted table so the join becomes local-to-remote instead of remote-to-remote. Another frequent issue is aggregation pushdown. If a story or model requests an aggregated result, Datasphere attempts to push the GROUP BY down to the source. Some connection types and some source query complexities prevent this, causing full detail-level extraction before aggregation happens locally. Reviewing the generated query plan (where the connection type exposes one) or testing with progressively simpler aggregations helps identify where pushdown breaks down. Connection-level constraints also matter: many source connections enforce a maximum number of concurrent open connections or session timeouts. In a multi-user story scenario, concurrent federated queries from multiple end users can exhaust this pool, causing queued or failed queries during peak hours. This is a capacity planning problem, not a modeling bug, and requires coordination with the source-system team to size connection pools appropriately or to introduce caching/replication for high-concurrency dashboards. Finally, distinguish between transient and structural performance problems. Transient issues (a batch job on the source system consuming resources at query time) resolve on their own and are diagnosed by correlating query failure timestamps with source-system job schedules. Structural issues (a poorly filtered virtual table used by dozens of stories) require model redesign: adding mandatory filters, restricting exposed columns, or converting frequently-hit federated objects to scheduled replication.

Code example

-- Example: comparing a federation-friendly filtered view versus-- an unfiltered view that forces large data pulls. -- POOR: no filter pushed down; every consumer must filter downstream,-- so Datasphere/source may return the full historical table.CREATE VIEW V_SALES_FEDERATED_RAW ASSELECT SALES_ORDER, CUSTOMER_ID, ORDER_DATE, NET_VALUE, PLANTFROM REMOTE_ECC_TABLE; -- BETTER: filter embedded so pushdown reduces remote result set-- before it ever crosses the network.CREATE VIEW V_SALES_FEDERATED_FILTERED ASSELECT SALES_ORDER, CUSTOMER_ID, ORDER_DATE, NET_VALUE, PLANTFROM REMOTE_ECC_TABLEWHERE ORDER_DATE >= ADD_YEARS(CURRENT_DATE, -2)  AND PLANT IN ('1000','2000'); -- Cross-source join anti-pattern (forces local join in Datasphere):-- V_ORDERS comes from Source A (S/4HANA), V_SHIPMENTS from Source B (3rd-party DB)SELECT o.SALES_ORDER, s.SHIPMENT_STATUSFROM V_ORDERS_SOURCE_A oJOIN V_SHIPMENTS_SOURCE_B s  ON o.SALES_ORDER = s.SALES_ORDER;-- Mitigation: replicate V_SHIPMENTS_SOURCE_B as a persisted table-- inside Datasphere so the join is remote-to-local, not remote-to-remote.

Real project scenario

A retail customer built an executive dashboard in SAC on top of a Datasphere federated view joining live sales data from S/4HANA with live inventory data from a separate legacy Oracle system. During month-end, the dashboard timed out for most users. Investigation showed the cross-source join was pulling the full inventory table (several million rows with no filter) into Datasphere before joining, and month-end batch jobs on the Oracle side were simultaneously consuming most of its available connections. The team added a mandatory plant/date filter to the inventory federated view and replicated a filtered snapshot of inventory into Datasphere on a nightly schedule, converting the cross-source join into a local join against replicated data plus a lightweight federated lookup for same-day sales, restoring dashboard response times to acceptable levels.

Common mistakes

• Joining two federated (virtual) sources directly without checking whether the join can be pushed down, causing full local materialization of both sides • Applying filters only in the downstream consuming model or story instead of embedding them in the federated view, so filters are not pushed to the source early • Assuming federation performance will match replicated/persisted table performance under high user concurrency • Not coordinating with source-system teams on connection pool sizing before rolling out federated dashboards to a large user base • Ignoring source-system batch schedules when diagnosing intermittent slow queries, missing the correlation with peak load windows • Exposing wide, unfiltered virtual tables directly to business users, encouraging ad hoc queries that generate expensive full scans on the source

Best practices

• Always embed restrictive filters directly in federated views rather than relying on downstream filtering • Avoid joining two federated sources from different systems; replicate at least one side when a join is unavoidable • Test federated objects in isolation with simple queries before blaming the overall model for performance issues • Coordinate connection pool sizing and peak-load windows with source-system administrators before scaling federated dashboards to many concurrent users • Use replication for high-concurrency, latency-sensitive dashboards and reserve federation for low-volume, near-real-time lookups • Monitor and periodically review which federated objects are most frequently queried to identify replication candidates proactively

Interview angle

Interviewers assess whether you can reason about federation performance beyond 'it's slow, replicate it.' Be ready to explain pushdown limitations for aggregations and cross-source joins, describe how you isolate whether a bottleneck is in Datasphere, the network, or the source system, and articulate criteria for deciding between federation and replication (data freshness needs, source-system load tolerance, query complexity, and concurrency).