Building Fact Models, Measures, and Associations in Business Builder
Learn the practical steps and design decisions for building fact models in Business Builder, including measure definitions, currency/unit handling, dimension associations, and how these models surface for consumption in SAP Analytics Cloud.
Explanation
Once the foundational concepts of dimensions and fact models are clear, the intermediate skill is actually designing a fact model that performs correctly and is safe to expose for consumption. A fact model in Business Builder is created by pointing to one or more source objects—usually deployed Data Builder views—and then defining measures, which are numeric columns intended for aggregation (sum, count, average, min/max), and associations to dimension objects that provide descriptive context and enable slicing. The modeler chooses aggregation behavior per measure; getting this wrong (for example marking a percentage or ratio column as summable) produces analytically nonsensical totals in downstream SAC stories, so understanding the semantic meaning of each column before assigning aggregation is essential. A critical intermediate topic is currency and unit of measure handling. Many fact models contain monetary or quantity measures that require conversion (for example, converting local currency amounts to a reporting currency, or converting between units like kilograms and pounds). Business Builder provides currency conversion and unit conversion configuration for measures where the source data includes the necessary reference currency/unit columns and conversion tables are available (typically maintained in Data Builder or through Datasphere's currency conversion objects). Modelers must ensure the exchange rate or conversion source is correctly maintained and refreshed; a stale conversion table silently produces wrong converted values without throwing an error, which is a common production support issue. Associations between a fact model and dimensions are established through key fields. The fact model's source view must contain a foreign-key-like column (for example Product ID) that matches the dimension's key. Business Builder validates that this mapping is structurally sound, but it cannot validate business correctness—if the fact source and dimension come from different systems with mismatched key formats (e.g., leading zeros, different code systems), rows will fail to join or will produce unexpected blanks in SAC ("#" or null dimension members). Diagnosing this requires checking sample data on both sides, not just relying on deployment success messages. Another intermediate consideration is model reusability: a well-designed dimension (like a shared Customer or Cost Center dimension) should be built once and reused across many fact models, rather than each fact model author recreating a similar but slightly different dimension. This avoids semantic drift, where the same business term (e.g., "Customer") is defined slightly differently in different models, causing inconsistent numbers across SAC stories that supposedly represent the same KPI. Governance practice usually assigns a small set of trusted modelers to own shared dimensions. Once a fact model (or the consumption model built from it) is deployed, it becomes available in SAP Analytics Cloud as a model source, either live (querying Datasphere directly at runtime) or, in some configurations, imported. Runtime behavior matters: live connections push query load to Datasphere per SAC interaction, so poorly performing underlying views (e.g., unoptimized joins, no filters) directly degrade story performance. Performance tuning at this stage usually means going back to the Data Builder view to add filters, reduce columns, or pre-aggregate, since Business Builder itself offers limited performance tuning controls beyond selecting which columns and measures to expose. Finally, authorization scenarios—Data Access Controls—can be attached to a fact model or its dimensions to restrict rows returned based on the requesting user's attributes (e.g., restricting a Sales fact model so a regional manager only sees their region). These are configured declaratively and should be tested with multiple representative user profiles before going to production, since incorrect DAC configuration can either over-expose sensitive data or silently return empty result sets for legitimate users.
Real project scenario
A multinational manufacturer builds a Business Builder fact model called Plant Costs sourced from a Data Builder view that aggregates cost postings by plant, cost center, and period, in multiple local currencies. The modeling team configures currency conversion on the Amount measure to report in USD using a maintained exchange rate table, associates the fact model with shared Cost Center and Plant dimensions used across several other fact models, and attaches a data access control so that plant controllers only see costs for plants in their assigned region. During UAT, a controller in Brazil reports seeing costs in the wrong currency; the root cause is traced to a missing exchange rate for the reporting period in the conversion table, which the team fixes by updating the rate maintenance process rather than the fact model itself.
Common mistakes
• Assigning sum aggregation to ratio, percentage, or already-aggregated measures, producing meaningless totals • Configuring currency conversion without verifying the exchange rate table is complete and current for all relevant periods • Recreating near-duplicate dimensions per fact model instead of reusing a shared governed dimension, causing inconsistent KPIs across stories • Assuming a successful deployment means the join between fact model and dimension is business-correct, without sampling actual joined data • Skipping multi-user testing of Data Access Controls, leading to over-exposure or unexpected empty results in production
Best practices
• Explicitly review and set correct aggregation behavior for every measure rather than accepting defaults • Maintain and monitor currency/unit conversion reference data on a defined refresh cycle • Standardize on shared, IT-governed dimensions for common business entities and discourage duplicate local dimensions • Sample and manually verify joined data between fact model and dimensions after each significant change, not just after deployment • Test Data Access Controls with multiple representative user roles before promoting a fact model to production consumption
Interview angle
Expect questions on how currency/unit conversion is handled, how associations between fact models and dimensions are validated, and how performance issues in a live SAC connection are diagnosed and resolved (usually by tracing back to the underlying Data Builder view). Also be ready to discuss dimension reuse and governance to prevent semantic drift across fact models, and how Data Access Controls are tested before production rollout.