Designing the Modeling Flow: From Connections to Business Consumption in Datasphere

Explanation

Once teams understand what Spaces, Data Builder, and Business Builder are individually, the next challenge is designing how they work together as a coherent pipeline that is maintainable, performant, and secure. This lesson focuses on the implementation-level decisions that intermediate consultants must make when architecting a real Datasphere solution, without duplicating the deep technical detail covered in dedicated connections, security, or SAC-integration lessons elsewhere in this topic. The first decision point is data acquisition strategy: replication versus federation. Replication physically copies source data into a Space's storage on a schedule or via real-time/delta mechanisms where supported, giving predictable query performance at the cost of storage and potential data latency. Federation queries the source system live through a connection, avoiding duplication but depending heavily on source system performance and network stability. Intermediate practitioners must evaluate source system load tolerance, data freshness requirements, and network topology before choosing per-table or per-scenario. Many real projects use a mix: high-volume, frequently queried master data is replicated, while low-volume or rarely used reference tables are federated. After data lands (replicated or federated) as tables in a Space, Data Builder is used to build a layered view architecture. A common pattern is to separate views into at least two conceptual layers: a 'preparation' layer that does minimal cleansing (renaming columns, correcting data types, filtering technical noise) directly on top of source tables, and a 'transformation' layer that applies business rules such as joins across sources, currency conversions, and calculated columns. Keeping these layers separate makes it easier to trace issues back to either a source data problem or a logic error introduced in transformation. Business Builder then consumes the transformation-layer views to build business entities and fact models. A business entity typically wraps a Data Builder view with friendly labels, hierarchies, and associations to other entities (for example, linking a Sales Fact entity to a Customer dimension entity). Consumption models aggregate one or more business entities into a model exposed for reporting or planning, where you define which measures and dimensions are available to end consumers and can add calculated measures using business-friendly formulas. Runtime flow matters for troubleshooting: when a SAC story or planning model queries a Datasphere consumption model, the query is pushed down through the semantic layer, into the underlying Data Builder views, and finally either against replicated tables (fast, local) or through federation back to source systems (slower, dependent on source availability). When performance issues arise, the diagnostic approach is to isolate which layer is slow: check if source system federation is the bottleneck, whether Data Builder views contain expensive joins or non-optimized SQL views, or whether the consumption model has excessive calculated measures evaluated at query time. Security must be designed in parallel, not bolted on afterward. Space-level access control determines who can even open a Space, while object-level or row-level security (where supported) can restrict which data within shared models a given user sees, such as region-based row restrictions for country managers. These controls should be validated with realistic user roles during build, not only at go-live. Production support considerations for intermediate consultants include monitoring replication job success/failure, tracking view usage to identify orphaned objects during cleanup, and maintaining a change process for view modifications since Business Builder objects often depend on Data Builder views underneath them ripple-effect changes can break downstream consumption models silently if not tested.

Code example

-- Example: layered Data Builder SQL view pattern (conceptual, not a specific product API)-- Preparation layer view: light cleansing onlyCREATE VIEW PREP_SALES_ORDERS ASSELECT    ORDER_ID,    CAST(ORDER_DATE AS DATE) AS ORDER_DATE,    CUSTOMER_ID,    NET_AMOUNT,    CURRENCYFROM RAW_SALES_ORDERSWHERE ORDER_STATUS <> 'CANCELLED'; -- Transformation layer view: business joins and currency normalizationCREATE VIEW TRANS_SALES_WITH_CUSTOMER ASSELECT    s.ORDER_ID,    s.ORDER_DATE,    c.CUSTOMER_NAME,    c.REGION,    s.NET_AMOUNT,    s.CURRENCYFROM PREP_SALES_ORDERS sINNER JOIN PREP_CUSTOMER_MASTER c    ON s.CUSTOMER_ID = c.CUSTOMER_ID; -- This transformation-layer view would then be wrapped by a Business Builder-- business entity that adds friendly labels, a Region hierarchy, and a-- calculated 'Net Revenue (Reporting Currency)' measure for consumption in SAC.

Real project scenario

A manufacturing company federates a rarely queried equipment master table from an on-premise system but replicates high-volume production order data nightly. During UAT, regional planners report slow dashboards. The team traces the issue to a Business Builder consumption model performing a live federated join against the equipment master on every query, and resolves it by replicating that table instead, since its data changes infrequently and query volume was high.

Common mistakes

• Choosing federation for high-volume, frequently queried tables without testing source system load impact • Mixing cleansing and business transformation logic in a single Data Builder view, making troubleshooting difficult • Building Business Builder entities directly on raw source tables instead of on curated transformation-layer views • Deferring security design until after models are built, leading to rushed and inconsistent access rules • Not monitoring replication job health, allowing stale data to silently reach business consumers • Modifying shared Data Builder views without checking downstream Business Builder dependencies

Best practices

• Decide replication versus federation per source/table based on query frequency, data freshness needs, and source system tolerance • Maintain clearly separated preparation and transformation view layers in Data Builder • Build Business Builder entities only on top of stable, curated transformation views, never directly on raw tables • Define row-level and object-level security requirements before modeling begins, and test with representative user roles • Monitor replication jobs and establish alerting for failures or excessive latency • Establish a change-impact review process before modifying widely reused Data Builder views

Interview angle

Interview questions at this level typically probe whether a candidate can justify replication versus federation trade-offs for a given scenario, describe a layered view architecture, and explain a realistic troubleshooting path for a slow consumption model, showing they understand the full runtime chain from SAC query down to source system.