Building and Configuring Analytic Models: Dimensions, Measures, Currency and Calculations

Explanation

Once the foundational decision of model type is made, the real configuration work begins: defining dimensions precisely, setting up measures/accounts with correct calculation logic, and configuring currency behavior so numbers are trustworthy across the organization. This lesson focuses on the intermediate-level configuration decisions consultants make daily when building models that will support real business reporting and planning. Dimension configuration starts with deciding whether a dimension is generic (a simple flat list, like Region) or hierarchical (nested levels, like Product Category > Subcategory > Product). Hierarchies in SAC can be defined directly within the model by adding parent-child relationships, or they can be inherited from an external hierarchy source such as a master data table. Getting hierarchy structure right matters because drill-down in stories depends entirely on it; a poorly structured hierarchy (e.g., inconsistent parent assignments, orphaned nodes) causes confusing or incomplete drill paths for end users. Public dimensions—shared across multiple models—are a key design choice: when several models need to share the same Cost Center or Region list, creating that dimension as a reusable public dimension avoids duplication and keeps master data synchronized. Private dimensions, by contrast, exist only within a single model and are appropriate for model-specific attributes. Measure configuration involves choosing aggregation behavior (SUM, MIN, MAX, COUNT, AVERAGE) for base measures, and building calculated measures using SAC's formula editor, which supports arithmetic across measures, referencing account members, and applying conditional logic. Restricted measures narrow a base measure by one or more dimension filters—for example, 'EMEA Revenue' restricts the Revenue measure to Region = EMEA—and are essential for building comparison charts (e.g., showing Actual vs Budget side by side) without duplicating base data. In account-based models, similar logic is achieved by defining account members and using account-level formulas, which behave more like BPC script logic than simple story-level formulas. Currency conversion is a frequent source of complexity. SAC models can be configured with a currency conversion type that references exchange rate tables (either uploaded manually or sourced from a connected system) and applies conversion at query time based on rules (e.g., using period-end rate vs average rate, source currency vs fixed reporting currency). Misconfigured currency conversion—wrong rate type, missing rates for certain periods, or converting already-converted values—produces silently wrong numbers that are hard to detect without careful validation against source system totals. Performance also enters the picture at model design time: overly wide models with hundreds of measures, deeply nested hierarchies, or excessive calculated measures evaluated at query time can slow story rendering, especially on live connections to Datasphere or HANA. Consultants should validate models incrementally—checking a handful of calculated measures against manually computed expected values, testing hierarchy drill-down paths, and confirming currency-converted totals reconcile with source system reports—before opening the model up to broader story development. This validation discipline is what separates a model that looks correct from one that is actually production-ready.

Code example

Example: Defining a calculated measure formula in SAC's formula editor (pseudocode representation, not a scripting language) Calculated Measure: "Gross Margin %"Formula: ([Revenue] - [Cost of Goods Sold]) / [Revenue] * 100 Restricted Measure: "Revenue_EMEA"Base Measure: RevenueRestriction: Region = "EMEA" Restricted Measure: "Revenue_Budget"Base Measure: RevenueRestriction: Version = "Budget" -- Used together in a story table to compare Actual vs Budget vs Margin % side by side without duplicating source data.

Real project scenario

A finance team building a management reporting model needs Actual vs Budget vs Variance% comparisons across five currencies, converted to Group currency (EUR) for consolidated review. The consultant configures a currency conversion type referencing an uploaded monthly exchange rate table, builds restricted measures for Actual and Budget from a shared Revenue account, and adds a calculated Variance% measure. During validation, the consultant discovers March figures for one subsidiary don't reconcile because that month's exchange rate was missing from the rate table, causing SAC to fall back to a default rate—this is caught only through careful cross-checking against the source ERP report before go-live.

Common mistakes

• Building calculated measures with circular or overly complex formulas that are hard to debug when numbers look wrong. • Forgetting to upload exchange rates for all required periods, causing silent fallback behavior or blank values. • Creating private dimensions for master data that should be shared, leading to inconsistent Region/Cost Center lists across multiple models. • Not testing hierarchy drill-down after changes, missing orphaned nodes that break story navigation for end users. • Adding excessive restricted/calculated measures directly in the model when some logic would be better handled at story or widget level, bloating the model unnecessarily.

Best practices

• Always validate calculated and restricted measures against manually computed sample values before releasing a model to story builders. • Maintain exchange rate tables proactively with a defined update cadence, and alert stakeholders if periods are missing. • Use public dimensions for any master data shared across more than one model to guarantee consistency. • Keep model-level calculated measures limited to logic that must be consistent everywhere; push ad hoc or exploratory calculations to story-level calculations instead. • Test hierarchy drill-down paths explicitly after any master data or hierarchy change, not just after initial build.

Interview angle

Expect questions on the difference between calculated and restricted measures, how currency conversion is configured and validated, and how public vs private dimensions affect multi-model landscapes. A strong answer discusses not just how to configure these features but how to validate them against source system truth—demonstrating production-support maturity rather than just tool familiarity.