Architecting a Model
It's possible to open Gateway and start building a Model directly - create a Dimension, populate it, build a Cube, and have a working Workview in fifteen minutes. For a very small Model, that's fine. For anything more substantial, building straight from the interface tends to produce predictable problems: Dimensions get the wrong grain because nobody thought about reporting requirements before the Dimension existed; Cubes end up with redundant or missing Dimensions because the inter-Cube data flows weren't considered; a calculation that works for Actual quietly breaks in Forecast because the directional logic flip wasn't planned for; and an integration fails the first time the source system produces a record that doesn't fit the Dimensions as designed.
A short architecture phase - even informally, on a whiteboard or in a shared document - catches these problems while they're still cheap to fix. Five tables cover it:
- Dimension Registry - every Dimension the Model will have, its type, its key structure, and its safety nets.
- Cube Architecture Map - every Cube, its Dimensions, its purpose, and any notes worth leaving for a future reader.
- Cube Logic - the calculations that matter in each Cube, and how they vary by scenario.
- Inter-Cube Connectivity & Mapping - how data flows between Cubes.
- Data Source Register - the external feeds that populate the Model.
The Dimension Registry
| Column | Purpose |
|---|---|
| Dimension Name | The name as it will appear in the Model. |
| Type | One of Standard, Scenario, Time, Measures. |
| Key Members / Hierarchies | The levels of the primary Hierarchy (e.g. State > Entity > Branch) or the specific top-level members. |
| Mandatory Safety Net Elements | The Unspecified and No Elements needed to handle imperfect data and global values - see Designing a Dimension. |
Type matters because it activates platform behaviour: Time triggers the System.Dimension.Time build process, Scenario is recognised by natural-language and assistant features, and Measures enforces that every Cube has exactly one Measures Dimension, always last. Use Standard for everything else. Key Members / Hierarchies doesn't need to enumerate every Element - just enough structure that a reader understands what the Dimension is for and roughly how it rolls up. Safety net Elements are easy to forget at design time and painful to add after data has already loaded, so pin them down here rather than after the fact.
The Cube Architecture Map
| Column | Purpose |
|---|---|
| Cube Name | The name as it will appear in the Model. |
| Dimensions | The Dimensions the Cube uses, in Dimension order. |
| Purpose | What the Cube is for, and what role it plays in the broader Model. |
| Architectural Notes | Decisions worth recording for a future reader. |
Dimension order matters less than it might seem, but it's still worth listing deliberately - typically Scenario and Time at the front, the Measures Dimension last, and everything else in between. Purpose should distinguish the Cube from every other Cube in the Model: "holds revenue data" isn't enough, but "detailed driver-based forecasting of volume and price by product, used as the source for revenue figures in the consolidated Income Statement" is. Architectural Notes is where a non-obvious decision gets written down - why there's no Region Dimension because pricing is global, why No Region was used instead of a separate Pricing Cube - so it doesn't have to be re-discovered later. Reviewing this table is also where redundant, missing, or overloaded Cubes tend to surface.
The Cube Logic table
| Column | Purpose |
|---|---|
| Cube | Which Cube the calculation lives in. |
| Calculation Name | A short name for the calculation. |
| Scenario | Whether the logic applies to Actual, planning scenarios, or all scenarios. |
| Forecast/Budget Logic (Predictive) | The formula or business rule, in plain or pseudo-code. |
| Exceptions | Special cases where the logic doesn't apply, or is overridden. |
Not every formula needs a row - trivial calculations don't. Anything involving directional logic, cross-Cube references, scenario-specific behaviour, or a non-obvious business rule does. The Scenario column is what forces a directional logic flip into the open before it gets built rather than after. A useful test: if a calculation has no Scenario variation and no Exceptions, it's probably too simple to need documenting here.
The Inter-Cube Connectivity & Mapping table
| Column | Purpose |
|---|---|
| Source Cube | The Cube the data comes from. |
| Destination Cube | The Cube the data flows to. |
| Data Flow Direction | "Up" (detail to summary), "down" (summary to detail), or lateral. |
| Mapping Logic (The "Bridge") | How dimensional differences between source and destination are reconciled. |
Direction isn't just notation - it says which Cube is the source of truth. Revenue flowing up from a detail Cube into a P&L means the P&L is showing what the detail Cube calculated; if it flowed down, the detail Cube would instead be showing an allocation of a P&L target, which is a different Model design. Mapping Logic is where the bridge between dimensional differences gets documented - if the source Cube has a Product Dimension the destination doesn't, how do products aggregate up into accounts? A Hierarchy? A mapping table? A hard-coded formula? If this column can't be filled in clearly, the inter-Cube flow probably won't work the way it's imagined, and that's a sign to revisit the Cube Architecture Map.
The Data Source Register
| Column | Purpose |
|---|---|
| Data Source Name | A short name for the source. |
| Source Type | CSV file, JDBC database, REST API, etc. |
| Source System / File | The specific source - which database, which API, which filename. |
| Refresh Frequency | How often the source feeds the Model. |
| Destination Cube | Which Cube the data lands in. |
| Measures Populated | Which measures get values from this source. |
| Dimensional Mapping | How source columns map to Model Dimensions. |
| Safety / Default Elements | Which Unspecified or No Elements catch data that doesn't categorise cleanly. |
| Notes | Anything else worth recording. |
Dimensional Mapping is worth being specific in: "source dept_code maps to Department Dimension element" is fine, but "source dept_code maps to Department Dimension element by stripping the prefix and matching the principal element name" surfaces the actual transformation the load process needs to perform. Safety / Default Elements matters because real source data is messy - a nightly load that fails the first time it meets a record without a department code is an avoidable problem, and it's much cheaper to design the safety net in now than to add it after the first failure.
A worked example
A budgeting Model needs to support payroll planning by employee, revenue planning by product, operating spend planning directly in the P&L, a consolidated Profit and Loss, multiple currencies and departments, and Actuals from an ERP (Income Statement) and a CRM (product-level sales). Three Cubes have already been scoped: Payroll (Scenario, Time, Currency, Department, Employee, Payroll Measures), Revenue (Scenario, Time, Currency, Department, Product, Revenue Measures), and Profit and Loss (Scenario, Time, Currency, Department, Account, Profit and Loss Measures), with operating spend entered directly against the relevant accounts in the P&L Cube itself.
Dimension Registry
| Dimension Name | Type | Key Members / Hierarchies | Mandatory Safety Net Elements |
|---|---|---|---|
| Scenario | Scenario | Default: Actual, Budget, Forecast Planning Scenarios: Budget, Forecast Archive Scenarios: Final Budget, Final Forecast | None |
| Time | Time | Default: Year > Quarter > Month Month YTD, Month List, Quarters List, Financial Year List, Annual Assumptions | No Time |
| Currency | Standard | Default: All Currencies > AUD, NZD, USD, EUR | No Currency, Unspecified Currency |
| Department | Standard | Default: All Departments > Region > Department Cost Centres: Direct, Indirect | Unspecified Department |
| Employee | Standard | Default: All Employees > Department > Employee | Unspecified Employee, No Employee (for departmental aggregates) |
| Product | Standard | Default: All Products > Brand > Product | Unspecified Product, No Product (for non-product revenue) |
| Account | Standard | Default: Net Profit > P&L tree Management View: alternative roll-up | Unspecified Account |
| Payroll Measures | Measures | Salary, Bonus, Superannuation, Total Compensation, Headcount | None |
| Revenue Measures | Measures | Units, Average Price, Revenue, Cost of Goods Sold, Gross Margin, Margin % | None |
| Profit and Loss Measures | Measures | Amount, Comment | None |
The Account Dimension carries both a Default P&L roll-up and a Management View, since the management team may want a different grouping than the statutory P&L. Payroll Measures, Revenue Measures and Profit and Loss Measures are three separate Measures-type Dimensions, one per Cube, rather than one shared Measures Dimension.
Cube Architecture Map
| Cube Name | Dimensions | Purpose | Architectural Notes |
|---|---|---|---|
| Payroll | Scenario, Time, Currency, Department, Employee, Payroll Measures | Detailed payroll planning by employee - salary, bonus, super and total comp per employee per period. | Aggregates up to feed total compensation into the Profit and Loss Cube via account mapping. |
| Revenue | Scenario, Time, Currency, Department, Product, Revenue Measures | Revenue planning by product, including units, pricing and cost of goods sold. | Aggregates up to feed product-level revenue and COGS into the Profit and Loss Cube via account mapping. |
| Profit and Loss | Scenario, Time, Currency, Department, Account, Profit and Loss Measures | Consolidated P&L, including operating spend planning entered directly against operating accounts. | Receives revenue from the Revenue Cube and compensation from the Payroll Cube; operating spend is entered directly here. |
Neither the Profit and Loss Cube has a Product or Employee Dimension - revenue and compensation figures arrive already aggregated by account, and the detail stays in the source Cubes. All three Cubes deliberately share Scenario, Time, Currency and Department, which is what makes the inter-Cube flows below straightforward.
Cube Logic
| Cube | Calculation Name | Scenario | Forecast/Budget Logic (Predictive) | Exceptions |
|---|---|---|---|---|
| Revenue | Average Price | Actual | Average Price = Revenue / Units | None |
| Revenue | Revenue | Planning | Revenue = Units * Average Price | Product: Professional Services - entered manually; formula respects HASSTATICVALUE(). |
| Revenue | Cost of Goods Sold | Planning | COGS = Units * Average Cost Price | None |
| Revenue | Gross Margin | All | Gross Margin = Revenue - COGS | None |
| Revenue | Margin % | All | Margin % = Gross Margin / Revenue | Average or Rate Calculation, so it doesn't sum across the hierarchy. |
| Payroll | Total Compensation | All | Total Comp = Salary + Bonus + Superannuation | None |
| Payroll | Annualised Salary | Planning | Salary (per month) = Annual Salary / 12, restricted to months from employee start to end | Mid-month starts handled with proportional logic. |
| Profit and Loss | Revenue (account level) | All | LINK("Revenue", [...]) aggregating product revenue up to revenue accounts via the Account mapping | None |
| Profit and Loss | Compensation (account level) | All | LINK("Payroll", [...]) aggregating compensation up to salary, bonus, super accounts | None |
| Profit and Loss | Operating Spend | Planning | Direct user input against operating accounts - no formula. | Some accounts use phasing rules (even, prior year trend, manual). |
| Profit and Loss | Net Profit | All | Hierarchy-driven roll-up via the Account Dimension - no formula needed. | None |
The directional logic flip in Revenue is explicit: Average Price is calculated in Actual, Revenue is calculated in the planning scenarios. The Professional Services exception is captured with the specific mechanism used to respect a manual override (HASSTATICVALUE()), and Operating Spend has no formula at all because it's direct user input.
Inter-Cube Connectivity & Mapping
| Source Cube | Destination Cube | Data Flow Direction | Mapping Logic (The Bridge) |
|---|---|---|---|
| Revenue | Profit and Loss | Up (planned and actual revenue) | Product elements aggregate via the Product Dimension's Default Hierarchy. Revenue measure -> Revenue accounts via a mapping in the LINK formula. COGS measure -> COGS accounts. |
| Payroll | Profit and Loss | Up (planned and actual compensation) | Employee elements aggregate via the Employee Dimension's Default Hierarchy (employees roll up by department). Salary -> Salary account, Bonus -> Bonus account, Superannuation -> Super account. |
| Profit and Loss | (none) | Terminal | This is the terminal consolidation Cube - no further flow. |
Both flows run "up" from detail to summary, confirming the P&L is the consolidation point rather than a source of allocated targets. Filling this table in for Revenue surfaced a genuinely open question - there wasn't yet a clear rule for how individual products map to specific revenue accounts (a single account for all products? one per brand? an explicit mapping table?) - which is exactly the kind of gap this table exists to catch before any building starts, not after.
Data Source Register
| Data Source Name | Source Type | Source System / File | Refresh Frequency | Destination Cube | Measures Populated | Dimensional Mapping | Safety / Default Elements | Notes |
|---|---|---|---|---|---|---|---|---|
| Payroll Export | CSV file | payroll_YYYY-MM.csv, uploaded to the MODLR Filesystem | Weekly during planning cycles, manually triggered | Payroll | Salary, Bonus, Superannuation | employee_id -> Employee (zero-padded codes); dept_code -> Department; currency -> Currency; period -> Time | Unspecified Employee for missing IDs; Unspecified Department for missing dept codes | Manually exported by HR; load process uses defensive column matching since source column names occasionally shift. |
| ERP Income Statement Actuals | REST API | Internal ERP, /api/gl/transactions | Nightly at 02:00 | Profit and Loss | Amount | account_code -> Account; dept_code -> Department; currency -> Currency; transaction date -> Time month | Unspecified Account for accounts not yet in the Dimension; Unspecified Department for unattributed transactions | Account Dimension is rebuilt before the load to pick up any new accounts. |
| CRM Product Sales | JDBC database | CRM database, sales_transactions table | Nightly at 02:30 | Revenue | Units, Revenue, Cost of Goods Sold | product_sku -> Product; dept_code -> Department; currency -> Currency; sale date -> Time month | Unspecified Product for retired SKUs; Unspecified Department for missing attribution | Runs after the ERP load so dimension updates are already in place; more granular than the ERP P&L feed, so Revenue holds product detail the P&L doesn't. |
The two automated sources are scheduled sequentially - ERP at 02:00, CRM at 02:30 - specifically so the Account Dimension rebuild from the ERP load is in place before the CRM load runs. That kind of ordering dependency is easy to forget once it's not written down anywhere.
What the tables catch
None of this works because the tables are special - it works because filling them out forces specific design conversations that are easy to skip when building straight in Gateway. In the worked example above, the Cube Logic table is what caught the Revenue directional logic flip, the Inter-Cube Connectivity table is what surfaced the unresolved product-to-account mapping, and the Data Source Register is what made the ERP-then-CRM load ordering explicit rather than tribal knowledge.
A few mistakes show up regularly even with the tables in place:
- Cubes designed around what the source data happens to contain, rather than around what reports and calculations the business actually needs. Design the Cubes first, then design the loads to populate them - not the other way round.
- Dimensions designed for the first dataset - an Account Dimension built to match one business unit's chart of accounts struggles the moment a second business unit arrives with a different structure. Design for the full set of business units up front, then map each one's data into the Dimension.
- Measures Dimensions that grow without limit as every new report request adds another element, until nobody remembers what half of them are for. Curate it, and review calculated measures periodically.
- Cubes that combine planning and reporting - a Cube that holds both detailed transactional data and supports user planning tends to become slow and hard to maintain. Split them when the use cases diverge.
- Undocumented inter-Cube flows - a five-Cube Model can easily have a dozen inter-Cube relationships, each with its own direction and mapping logic. Without the Inter-Cube Connectivity table, that becomes tribal knowledge held by whoever built the Model.
- Data sources without safety nets - a nightly load that breaks the first time the source produces an unexpected record is a fragile design avoided entirely by using the Safety / Default Elements column from the start.