Building and Configuring Analytic Models: Dimensions, Calculated Measures, and Data Sources (Part 5)

Building a production-ready Analytic Model goes beyond dragging columns into a canvas; it requires deliberate decisions about granularity, calculation placement, and data source behavior that affect performance and correctness downstream. The build typically starts by choosing the acquisition type. For an Import model, you connect to a source (flat file upload, OData, SAP BW, HANA, or increasingly a Datasphere view) and SAC copies a snapshot of the data into its in-memory column store; you then map source columns to dimensions and measures, and SAC infers data types which you should manually verify, especially for date fields and numeric precision, since auto-detection sometimes misclassifies a numeric ID as a measure or a date string as free text.

Once base dimensions and measures exist, most real models need calculated measures — formulas evaluated within the model using existing measures, e.g., Margin = Revenue - Cost, or Gross Margin % = Margin / Revenue. Calculated measures execute at the model level using SAC's formula language, which supports arithmetic, conditional (IF), and some aggregation-aware functions. A closely related but distinct concept is a restricted measure, which filters an existing measure by specific dimension member conditions, e.g., 'Online Revenue' = Revenue restricted to Channel = Online. Restricted measures are essential for building comparison KPIs (like current year vs prior year revenue) without needing separate physical columns. Both calculated and restricted measures should be built in the model whenever the logic is reusable across multiple stories; story-level calculated columns are appropriate only for one-off, story-specific adjustments that will never need reuse.

Dimension configuration includes setting hierarchies (parent-child or level-based, e.g., Region > Country > City), which enable drill-down in stories, and setting up currency conversion when the Organization dimension role is assigned — this lets the model apply exchange rates from a currency conversion table so users can toggle between local and group currency in the same story without separate models. For planning-enabled Account models, you must also configure the Version dimension with at least a public 'Actual' version and private versions for individual planners, plus data actions (server-side calculation scripts) if the planning process requires allocations, copy-forward, or currency translation logic — these run against the model and are a separate skill from basic model building but depend entirely on the model's dimension structure being correct first.

A key architectural decision is where calculations should execute: in the model (client-independent, reusable, consistent) versus in the source system/live connection (pushed down, potentially faster for huge datasets but limited by what the source's semantic layer already exposes) versus in the story (fast to build, but not reusable and risks inconsistency). As a rule of thumb, anything that represents an official business KPI belongs in the model; anything exploratory or presentation-specific can stay in the story.

After building, validation matters: check row counts against source, spot-check calculated measure results against a known manual calculation, and test hierarchy drill paths. For live models, also verify that source-side security (e.g., BW authorization or HANA analytic privileges) correctly restricts data, since live models typically pass through source security rather than enforcing SAC's own row-level security by default — a frequent point of confusion in security reviews.

A manufacturing client's finance team needed both 'Actual vs Budget Variance %' and 'Regional Revenue Share %' visible across six stories. The team built these as model-level calculated and restricted measures instead of story formulas after discovering that three existing stories had each computed Variance % slightly differently (one used absolute difference, another used a rounded percentage). Consolidating the logic into the model as a single calculated measure eliminated the discrepancy and became the single source of truth referenced during a subsequent finance audit.

• Building the same calculated logic separately in multiple stories instead of once in the model
• Misclassifying a numeric ID column as a measure during import, causing incorrect aggregation in charts
• Forgetting to verify currency conversion setup when the Organization dimension role is enabled, leading to mixed-currency totals
• Assuming live models enforce SAC row-level security by default when they typically inherit source-system security instead
• Not validating restricted measure filter logic against edge cases (e.g., missing or null dimension members) before go-live

• Place all officially defined KPIs as model-level calculated or restricted measures, reserving story-level formulas for one-off exploratory views
• Manually verify data type mapping after import instead of trusting auto-detection, especially for IDs and dates
• Document currency conversion source and rate table used when Organization dimension role is enabled
• Test hierarchy drill-down and restricted measure edge cases (nulls, unmapped members) before releasing to business users
• For live connections, explicitly confirm with source-system admins how security is enforced rather than assuming SAC-side restrictions apply

A common intermediate-level question asks candidates to design a KPI (like Gross Margin %) and explain whether they would implement it as a calculated measure in the model or as a story-level calculation, and why. Strong candidates emphasize reusability, governance, and the risk of KPI drift across stories as the deciding factor, and can distinguish calculated versus restricted measures with a concrete example.