Configuring and Modeling with Federated Remote Tables in Datasphere
Walks through the practical steps of setting up connections, creating federated remote tables, and building models on top of them, including runtime and troubleshooting considerations.
Explanation
Once the conceptual distinction between federation and replication is understood, the next step for a consultant is knowing how to actually configure federated access in SAP Datasphere and understand the runtime flow when reports execute against it. The configuration process begins with a connection to the source system. In Datasphere, connections are created in the Connections area of a space, and the connection type determines what federation capabilities are available. For SAP source systems such as S/4HANA, connections typically rely on either a direct database-level connection method or an application-level connection depending on deployment (cloud versus on-premise via Cloud Connector). For non-SAP sources, generic database connections (for example, standard SQL-based connections) may be used. Not every connection type exposes the option to mark a remote table as federated; some only support replication. Before designing a model, the consultant must open the connection's associated data source browser in Datasphere, locate the desired source table or view, and check whether it can be added as a remote table with 'Remote' access type selected, as opposed to being forced into replicated mode only. After a remote table is added in federated (Remote) mode, it appears in the space's repository like any other object, but its icon and properties clearly indicate that it has no local persistence. Modelers can then build graphical views, analytic models, or fact models on top of it exactly as they would with a replicated or locally built table. The important behavioral difference emerges at runtime: when a business user opens a story in SAC or previews data in Datasphere, Datasphere's query processor decomposes the model's logic and determines how much of that logic can be pushed down to the source system versus what must be executed locally within Datasphere's own processing layer after the source returns data. Simple filters, aggregations, and joins on federated tables are often pushed down efficiently, especially when the underlying source is SAP HANA-based, because the federation layer can translate much of the SAP-standard modeling logic into native source queries. However, more complex scenarios, such as certain calculated columns, currency conversions, or joins across a federated table and a locally replicated or persisted table, may not push down completely, forcing Datasphere to pull larger intermediate result sets and process them locally, which increases both network transfer and local compute time. Troubleshooting federated queries requires a different mindset than troubleshooting replicated data issues. With replication, problems usually center on load schedules, delta capture, or synchronization failures, and are visible in Replication Flow monitors. With federation, problems typically appear as slow story rendering, timeouts, or inconsistent results caused by the source system being busy or by network instability between Datasphere and the source. Diagnosing this requires checking the connection's health and latency, reviewing whether the source system itself is under heavy load at the time of the slow query, and examining the model to see whether calculation logic could be pushed down more effectively (for example, by simplifying calculated columns or restructuring joins so that filtering happens as early as possible in the query, ideally before crossing the federation boundary). Security for federated access also has its own nuances: since no data is copied into Datasphere, row-level security and data access controls must be enforced either at the source system (through its own authorization concepts) or through Datasphere's own space-level and model-level permissions, and the two layers do not automatically align. A consultant must verify that the combination of source-system authorizations and Datasphere access controls together produce the intended security outcome, rather than assuming one layer alone is sufficient. Finally, on the cloud versus on-premise distinction: federated connections to cloud-native SAP sources are generally more straightforward because network paths and authentication are designed for cloud-to-cloud communication, while federated connections to on-premise S/4HANA systems typically require additional infrastructure such as a Cloud Connector, introducing another potential point of latency and failure that must be accounted for in performance planning.
Real project scenario
A consulting team is building a Datasphere model that joins a federated remote table from an on-premise S/4HANA system with a locally replicated dimension table for reporting hierarchies. During testing, story load times in SAC are unacceptably slow. Investigation reveals that the join logic cannot be pushed down entirely to the source because of a calculated currency conversion column on the federated side, forcing large row sets to be pulled and processed inside Datasphere. The team resolves this by moving the currency conversion to a persisted, replicated intermediate table instead of computing it live on the federated object, restoring acceptable performance.
Common mistakes
• Assuming all modeling logic pushes down to the source system when building on federated remote tables, without verifying actual query execution behavior. • Placing complex calculated columns or currency conversions directly on federated objects, which often forces local processing and slows performance. • Overlooking that source-system authorizations and Datasphere access controls must both be checked for federated data, since no local copy exists to apply Datasphere-only security. • Ignoring Cloud Connector health and latency when troubleshooting slow federated queries against on-premise systems. • Joining federated and replicated tables without considering that inconsistent access modes can create unpredictable performance across different parts of the same model.
Best practices
• Verify push-down behavior empirically during design, rather than assuming all model logic executes at the source. • Keep calculated columns and currency conversions on persisted or replicated objects when they cannot be efficiently pushed down on federated sources. • Explicitly design security by checking both source-system authorizations and Datasphere-level permissions together for federated objects. • Account for Cloud Connector latency and availability as a distinct risk factor when federating to on-premise systems. • Avoid mixing federated and replicated tables in a single join path unless performance has been validated under realistic data volumes.
Interview angle
Candidates may be asked to describe what happens when a query is issued against a model built on a federated remote table, including which parts of the logic are likely to push down to the source and which are not. A well-prepared answer explains query pushdown conceptually without overstating certainty about exact source-system behavior, and correctly separates cloud versus on-premise connectivity considerations such as Cloud Connector dependency.