Designing SAC Model Structures: Dimensions, Accounts, Currency, and Version Management
Covers practical design decisions for building robust SAC models, including dimension types, the Account dimension in planning models, currency conversion setup, version handling, and validation before rollout.
Explanation
Once the basic concept of an SAC model is understood, the next step is designing it correctly for real project use. A key intermediate-level decision is how dimensions are structured. General dimensions (like Product or Customer) can be created as standalone reusable dimensions shared across multiple models, or as model-private dimensions used only within one model. Reusable dimensions are preferred in mature landscapes because they enforce consistency: if Product hierarchy changes, updating the shared dimension propagates to every model referencing it, avoiding drift between different reports that should agree on the same master data. In planning models, the Account dimension deserves special attention. Unlike analytic models where each measure is a separate column, planning models typically store all numeric facts as members of a single Account dimension (e.g., Revenue, COGS, Headcount), with a generic Amount or Quantity column holding the value. This design enables flexible planning functions, cross-account calculations, and easier addition of new accounts without restructuring the model. However, it also means query and story design must account for filtering and aggregating by Account member rather than by separate measure columns, which is a mental shift for consultants coming from a purely BI background. Currency conversion is another area requiring careful configuration. A model can have a fixed currency, or support multi-currency with conversion based on exchange rate tables loaded into the model (rate type, from-currency, to-currency, rate, and validity date). When designing this, it is important to decide whether conversion happens at query time (dynamic, flexible, but adds query overhead) or is pre-calculated and stored (faster consumption, but requires reprocessing when rates change). Getting the currency dimension and conversion type wrong is a frequent source of incorrect financial figures discovered late in a project. Version management, particularly in planning models, involves both public versions (Actual, Budget, Forecast, shared across users) and private versions (temporary what-if scenarios for individual planners before publishing). Designing the version dimension well means deciding naming conventions, which versions are locked after period close, and how data is copied or carried forward between versions (for example, copying Actual data into a new Forecast version as a starting baseline). Data locking, applied at intersections of dimensions like Version, Time, and Organization Unit, prevents accidental overwrites once a planning cycle is finalized, and must be planned alongside the model structure rather than as an afterthought. Validation before go-live should include checking that hierarchies roll up correctly with no orphaned members, that calculated measures/accounts produce expected results at different aggregation levels, that currency conversion matches manually verified sample calculations, and that the model performs acceptably with representative data volumes. Many production issues trace back to skipping this validation step and only discovering structural problems after stories and planning forms are already built on top of a flawed model, at which point fixes become far more expensive.
Real project scenario
During a financial planning rollout, a consultant built a planning model with an Account dimension containing over 200 GL-mapped accounts and a Version dimension with Actual, Budget, and multiple Forecast cycles. Midway through testing, business users reported that consolidated Forecast numbers in a foreign subsidiary were incorrect. Investigation revealed the currency conversion was configured as static instead of using the live exchange rate table, so historical rates were being applied to current forecast entries. Reconfiguring the conversion type and reloading rate data resolved the discrepancy before go-live.
Common mistakes
ā¢ Creating model-private dimensions for master data that should be shared and reused across models ā¢ Misunderstanding the Account dimension pattern in planning models and trying to force one measure per column as in analytic models ā¢ Configuring currency conversion as static when business requirements need dynamic, rate-table-driven conversion ā¢ Failing to define data locking rules, allowing planners to accidentally overwrite finalized Actual or approved Forecast data ā¢ Skipping hierarchy and rollup validation, leading to incorrect totals discovered only after user acceptance testing ā¢ Not planning version copy/carry-forward logic, forcing manual data entry for new planning cycles
Best practices
ā¢ Prefer shared, reusable dimensions over model-private ones whenever the same master data will be used elsewhere ā¢ Design the Account dimension hierarchy and structure early, aligning it with the chart of accounts or reporting structure ā¢ Choose currency conversion type deliberately based on reporting frequency and rate volatility, and document the decision ā¢ Define data locking rules alongside version design, not as a late addition after planning has already started ā¢ Always validate hierarchy rollups, calculated accounts, and currency conversion against manually verified figures before go-live ā¢ Establish clear version naming and carry-forward conventions before opening the model to end users
Interview angle
A frequent intermediate-level question is how the Account dimension in a planning model differs from measures in an analytic model, and why this matters for planning function design. Candidates should also be ready to explain currency conversion configuration trade-offs and how data locking protects finalized planning data, since these are recurring real-world pain points that distinguish practical experience from surface-level familiarity.