Building Consumption and Planning Models with Business Builder

Explanation

Once business entities exist in Business Builder (typically built on top of views or tables from the Data Builder), the next step in a real project is turning them into consumption-ready analytical objects: fact models and consumption models. This lesson focuses on that intermediate step, which is where most functional consultants spend their daily work. A fact model in Business Builder combines one or more business entities that represent measures (facts) with associations to dimension-carrying entities. Unlike a raw analytic model built directly on database views, a fact model is meant to be authored by business users using guided wizards: you pick measures, define aggregation behavior (sum, count, min, max, or non-cumulative where supported), and attach associations to master data entities such as Customer, Product, or Cost Center. The tool auto-suggests join paths when associations already exist on the underlying business entities, but you should always verify cardinality and join type, because an incorrect many-to-many association silently inflates measure values in downstream stories. A consumption model sits one layer above the fact model. It is the object you actually expose to SAP Analytics Cloud, to other Datasphere spaces (via sharing), or to external BI tools through the data layer's exposed views. In the consumption model editor you decide which dimensions and measures are visible, apply renaming for business-friendly labels, set default hierarchies, and optionally add calculated measures using restricted or calculated column logic (for example, ratios, delta versus prior period, or currency-converted amounts using a currency conversion function tied to a defined exchange rate table). This is also where you can mark a model as planning-enabled, which changes its behavior: planning-enabled consumption models allow SAC Planning to write back data, and Datasphere manages versioning and data locking so that plan and actual data coexist safely in the same semantic layer. Runtime flow matters for troubleshooting: when a story in SAC queries a consumption model, the request is translated into a query against the fact model, which in turn queries the underlying business entities, which resolve to the actual database views or replicated/federated tables in the data layer. If a story shows wrong numbers, the diagnostic order should be: check the consumption model's exposed measures and calculated logic first, then check the fact model's join associations and aggregation settings, then check the business entity's underlying view logic, and finally check the raw source data or replication status. Skipping this order wastes time, because most reported 'wrong number' defects are actually double-counting from an incorrect association cardinality in the fact model, not a data quality issue in the source. For planning scenarios specifically, you need to understand that Business Builder's planning model concept differs from classic BW-IP or SAC's own model-based planning: in Datasphere, planning data is stored as a fact model with designated planning dimensions (version, time, and organizational dimensions), and the consumption model exposed to SAC must be explicitly configured to allow input-ready cells. If this configuration is missing, users will see the data but cannot submit changes, which is a very common initial deployment issue. Security also flows through this chain: if a business entity has data access controls (row-level security) applied, those restrictions propagate through the fact model into the consumption model and finally into the SAC story, so testing must be done with representative user roles, not just the modeler's own administrative access, to catch scoping errors before go-live.

Code example

-- Illustrative calculated measure logic as authored inside a Business Builder consumption model-- (expressed conceptually; actual UI is graphical, not raw SQL) -- Calculated measure: Gross Margin %Gross_Margin_Percent =  ( SUM(Net_Revenue) - SUM(Cost_Of_Goods_Sold) ) / NULLIF(SUM(Net_Revenue), 0) * 100 -- Restricted measure: Current Year Actuals onlyCurrent_Year_Actuals =  SUM(Revenue) WHERE Version = 'Actual' AND Fiscal_Year = CURRENT_FISCAL_YEAR() -- Planning-enabled measure exposed to SAC with input-ready flag-- Version dimension must include at least 'Actual' and 'Plan' members-- Input-ready cells only enabled where Version = 'Plan' in the consumption model settings

Real project scenario

A retail finance team needed a self-service margin analysis story in SAP Analytics Cloud, sourced from Datasphere without engaging the central data engineering team for every new metric. The Datasphere modeling lead built base business entities for Sales Orders and Product Master using the Data Builder, then handed off to a finance power user trained on Business Builder. That user created a fact model joining Sales Orders to Product Master, added a Gross Margin % calculated measure, and exposed a consumption model to SAC. During UAT, margin figures were roughly double the expected value; tracing the issue backward from the SAC story to the consumption model to the fact model revealed that the association between Sales Orders and a Product Attributes entity was one-to-many instead of many-to-one, causing revenue rows to be duplicated per attribute row. Correcting the association cardinality in the fact model resolved the discrepancy without any changes to the underlying source view.

Common mistakes

• Creating associations in the fact model without verifying cardinality, leading to fan-out and duplicated measure values • Forgetting to mark a consumption model as planning-enabled, so SAC displays data but blocks user input • Exposing too many technical or intermediate calculated columns in the consumption model, confusing business users in SAC • Not testing row-level security with a representative restricted user before go-live, only validating with an admin account • Applying currency conversion logic at the wrong layer (business entity instead of consumption model), making it hard to reuse the same fact model for multiple reporting currencies • Skipping version and time dimension planning during model design, forcing a rebuild once planning requirements are added later

Best practices

• Always validate join cardinality explicitly when creating associations in a fact model, don't rely solely on auto-suggestions • Keep calculated and restricted measure logic in the consumption model layer so fact models remain reusable across multiple consumption scenarios • Design version and time dimensions up front if planning is even a possible future requirement • Test consumption models with at least one restricted-role test user before releasing to broader audiences • Use clear, business-friendly naming and descriptions in the consumption model since this is what end users see directly in SAC • Document the full entity-to-consumption chain for each exposed model so support staff can trace defects efficiently

Interview angle

Interviewers assess whether a candidate understands the layered architecture: business entity to fact model to consumption model, and can explain where to troubleshoot specific defect types such as duplicated values, missing input-readiness, or security leakage. Strong answers reference the propagation of row-level security through the layers and the difference between exposing a model for reporting only versus planning-enabled consumption, and can describe a realistic debugging sequence rather than guessing at the fix.