TM1 / Planning Analytics · model documentation
Northwind Group's internal FP&A and operations model. Every connection was traced from TurboIntegrator source/target assignments (cCubSrc → cCubTgt) and cross-cube DB() rule references in the live source repository, then grouped into layers you can open one at a time.
Data moves top to bottom, and one path comes back up: results computed inside TM1 are written back to Helios CRM. Click any layer to open just that layer's wiring.
CRM.Dim.FIN BSEG 2.Write Account Clustering Back to HEL.pro makes Helios CRM both the first source and the last target, so a change made in TM1 can reappear as "source data" on the next inbound load. Rule-level dependencies — period locks, attribute lookups, cell security — sit across all five layers and have their own view.Five external systems, five independent landing paths. Nothing crosses here — each source writes its own staging cubes, and only later layers join them.
The one chain that gives the server its name: a Meridian PSA time entry becomes a timesheet row, becomes a GL posting. One branch writes a total back onto a dimension attribute.
TIM Timesheets immediately after the Meridian PSA rows — one logical load split across two processes. And the ledger is populated by TI, not by rule: a stale GL figure is a chore problem, never a feeder problem.FIN General Ledger is the busiest object in the model. If you only learn one cube on this server, learn this one.
FIN.General Ledger Rountrip, REC Loads and the RGL.Data Load.* chores all converge on one cube.The three planning cubes are not wired by convention — TM1 holds an explicit step registry naming each source and target cube.
Source Cube / Target Cube attributes onto elements of }APQ C3 FIN Roundtrip Configuration, and the FIN.General Ledger Rountrip chore walks that registry — so these three arrows are documented by the model itself, not inferred. Every cell in all three cubes is additionally gated by the period-lock rule (layer 6).The only path in this model that writes out of TM1 and into a source system. Worth knowing before you assume Helios CRM is read-only from here.
Connections that move no data but decide what a user sees: rule-time lookups, hierarchy clones and security. These cut across all five layers above.
IF( DB('}APQ C3 Time Year-Month Lookup', !T Year, !T Month, 'Cal Year-Month Index') <= DB('}APQ C3 FIN Planning Param Control', !FIN Version, 'Expenses', 'PERBASE', 'Param Numeric'), 0, STET ). It is not security and not a feeder.| Other rule-level references | What the rule does |
|---|---|
MER Time Entry → }ElementAttributes_MER Workspace | resolves the workspace's currency, then converts stored cents with \ 100 (backslash division, avoids #N/A) |
HEL Contract / Opportunity Line Item → FIN Company | AttrS('FIN Company', …, 'Currency') derives owner and vendor home currency per line item |
HEL Load Tracker → }APQ Dimension Definition | reads the last dimension-update timestamp and reformats it for display |
FIN Reconciliation → FIN Reconciliation Mapping | reads a Function element (e.g. A-B) that decides how the two reconciliation sides are compared |
}CellSecurity_HEL Contract / Opportunity Line Item | grants READ or NONE per }Groups element based on the line's Product Family — product-based row security, not org hierarchy |
}APQ Dimension Hierarchies, }APQ Process Response Message | deliberately omit SKIPCHECK/FEEDERS and say so in an in-file @DOC block — do not flag these in a health check |
The diagrams simplify; this table doesn't. Every row names the mechanism and the file it was read from. Switching views above filters this list to that layer.
| Layer | From | To | How | Evidence (source file) |
|---|---|---|---|---|
| Ingest | Helios CRM | CRM Opportunity | TI | CRM.Cub.Opportunity.Load From HEL.SQL.pro — cCubTgt = CRM Opportunity |
| Ingest | Helios CRM | HEL Contract / Sales Contract / Opportunity Line Item | TI | HEL.Cube.Contracts.Load from SQL.pro, HEL.Dim.Opportunity Line Item.Load from SQL.pro, HEL.Dim.Sales Contract Line Item.Load from SQL.pro |
| Ingest | Helios CRM | HEL Intercompany Definitions | TI | HEL.Cube.Intercompany Definitions.Load From SQL.pro |
| Ingest | Helios CRM (SQL staging) | HEL Load Tracker timestamps | TI | HEL.SQL.Metadata Refresh.Opportunities.pro, HEL.SQL.Metadata Refresh.HEL Dim 4.pro |
| Ingest | Meridian PSA | MER Time Entry, MER Task | TI | MER.SQL.Execute Python to Load Table.pro, MER.SQL.Metadata Refresh.Task.pro |
| Ingest | Ledgerline regional (EU1/EU2/EU3) | FIN General Ledger TB, RGL Reporting Balances | TI | RGL.Cub.FIN General Ledger TB.Load From RGL.pro (+ .Caller), RGL.Cub.RGL Reporting Balances.Load From RGL.pro, RGL.Cub.Execute Python to Load Attributes Cube.pro |
| Ingest | Solara Payroll | payroll — target cube not confirmed | ? | HEL.SLP.Load Payroll.pro → Scripts/pyscripts/solara/solara.py (daily_run) |
| Timesheet | MER Time Entry | TIM Timesheets | TI | TIM.Cub.Timesheets.Load from MER Time Entry.pro |
| Timesheet | SQL public holidays (MER_holidays) | TIM Timesheets | TI | TIM.Cub.Timesheets.Load from SQL Public Holidays.pro — called from the MER load's epilog |
| Timesheet | Legacy CSV export | TIM Timesheets | TI | TIM.Cub.Timesheets.Load from Export.pro — "flat csv file exported from old Timesheets cube" |
| Timesheet | Legacy CSV export | TIM Consultant History | TI | TIM.Cub.Consultant History.Load From Export.pro |
| Timesheet | TIM Employee attributes | TIM Consultant History | TI | TIM.Cub.Consultant History.Load From Attribute.pro — TIM Employee × FIN BSEG 3 × T Year-Month |
| Timesheet | TIM Consultant History (prior month) | TIM Consultant History (current) | TI | TIM.Cub.Consultant History.Copy Month to Month.pro |
| GL | TIM Timesheets | FIN General Ledger | TI | TIM.Cub.FIN General Ledger.Load From Timesheets.pro |
| GL | TIM Consultant History | FIN General Ledger | TI | TIM.Cub.FIN General Ledger.Load From Consultant History.pro |
| GL | TIM Timesheets | FIN BSEG 1 attribute "Total Hours" | TI | HEL.Dim.BSEG 1.Load Total Hours from TIM Timehseets.pro — MDX over TIM Timesheets → }ElementAttributes_FIN BSEG 1 |
| GL | Helios CRM | REC Transaction Load ("Last Load From HEL") | TI | Cub.REC Transaction Load - Last Load From HEL.pro, Cub.REC Transaction Load - Last Load.pro |
| GL | MER Time Entry | FIN Prepaid Analysis | TI | @DOC "Move Prepaid revenue from MER Time entry to FIN Prepaid analysis"; FIN Prepaid Analysis.RUX references MER Time Entry |
| Planning | CRM Opportunity | FIN Revenue Planning + Drivers | TI | FIN.Cub.Revenue Planning.Load From CRM Opportunity.pro — cCubTgt / cCubTgt2 declared explicitly |
| Planning | MER Allocations | FIN Revenue Planning | TI | FIN.Cub.Revenue Planning.Load From MER Allocations.pro |
| Planning | FIN General Ledger | FIN Revenue Planning | ACT-11 | }APQ.C3.FIN.Dim.RoundtripStep.Update.pro — Source Cube / Target Cube attributes |
| Planning | FIN Expense Planning | FIN Balance Sheet Planning | PLAN-1 | }APQ.C3.FIN.Dim.RoundtripStep.Update.pro |
| Planning | FIN Revenue Planning | FIN Balance Sheet Planning | PLAN-2 | }APQ.C3.FIN.Dim.RoundtripStep.Update.pro |
| Planning | }APQ C3 Time Lookup + Planning Param Control | FIN Revenue / Expense / Balance Sheet Planning | rule | period-lock IF(DB(…) <= DB(…), 0, STET) in FIN * Planning*.RUX |
| Planning | }APQ C3 Workflow Status | FIN Planning Workflow | rule | FIN Planning Workflow.RUX — WPLAN-scoped comment / submitter measures |
| Feedback | FIN General Ledger + CRM Opportunity | CRM Account Clustering | TI | CRM.Cub.CRM Account Clustering From FIN General Ledger.pro — cCubSrc / cCubSrc2 / cCubTgt |
| Feedback | CRM Account Clustering | Helios CRM | write-back | CRM.Dim.FIN BSEG 2.Write Account Clustering Back to HEL.pro |
| Feedback | CRM Account Clustering | Class & potential measures (12 MAT YTG pipeline) | TI | CRM.Cub.Account Clustering.Populate Class and Potential.pro |
| Rules | HEL Employee | TIM Consultant History | rule | TIM Consultant History.RUX — DIMIX('HEL Employee', !TIM Employee) then end-date attribute |
| Rules | }ElementAttributes_MER Workspace | MER Time Entry | rule | MER Time Entry.RUX — workspace currency lookup, cents → units via \ 100 |
| Rules | FIN Company | HEL Contract / Opportunity Line Item | rule | HEL Contract Line Item.RUX, HEL Opportunity Line Item.RUX — owner / vendor home currency |
| Rules | FIN Reconciliation Mapping | FIN Reconciliation | rule | FIN Reconciliation.RUX — mapping-driven A-B function |
| Rules | HEL Contract / Opportunity Line Item "Product Family" | }CellSecurity_* (}Groups) | rule | }CellSecurity_HEL Contract Line Item.RUX, }CellSecurity_HEL Opportunity Line Item.RUX |
| Metadata | MER Task | TIM Task hierarchy ("Total MER Tasks") | TI | MER.Dim.Task.Recreate Hierarchy.pro |
| Metadata | HEL Region | TIM Consultant Home / Assigned Region | TI | TIM.Dim.Consultant Region.Clone.From.HEL Region.pro |
| Metadata | T Year-Month | CRM Version / CRM Create Date / CRM Close Date | TI | Dim.CRM Version|Create Date|Close Date.Create from T Year Month.pro |
| Metadata | HEL Employee | }Clients (TM1 user accounts) | TI | zDim.}Clients.Create Users from HEL Employee.pro — gated on the Active attribute |
| Metadata | EVT Survey Details | EVT_Event Details | TI | Cub.EVT_Event Details.Update from Survey Details.pro |
Attribute lists are populated; the element array comes back empty for every dimension on this service — for TIM Employee, 26 attributes and 0 elements.
| Dimension | Confirmed attributes |
|---|---|
| TIM Employee | Active, Code and Description, Created Date, Department(+Name), Description, Employee Number, End Date, External ID, FTE, Family, IsDeleted, Local Office, Mobile, Name and Code, ODBC Source, Position, Region(+Code/Name), Reporting Code, Role Region, Start Date, Tenure, Utilisation Percent, email Address |
| TIM Task | Customer(+Name), PTO Type, Prepaid Type, Project–Group Company/Department, Region, WBS G1–G5, WBS Generation/ID, billable, id, parent_id, priority, story_type, title, workspace_id — an external PM-tool schema |
| TIM Category | Meridian PTO Type, PTO Type |
| FIN BSEG 1 | Account (Industry/Name/Number/Owner/Sector), Company, Currency, Department(+Name), Invoice Basis, Meridian Account/Company/Currency/Department, PO, Region(+Code), Revenue Share, Total Hours, Value Chain |
| T Date | Cal Month/Year, Fin Month/Year, Period Name, Prior/Next Day-Month-Year, Week Day(+Name/Num), Year-Week, ibcs-class |
| FIN Version | CalcMethod, Currency Version, Planning parameter BAL / EXP / REV, Format, ibcs-class, ibcs-desc, isDelete |
| Chore | Processes behind it |
|---|---|
| TIM.Timesheets Load Fullv2 | TIM.Cub.Timesheets.Load from MER Time Entry → …Load from SQL Public Holidays |
| MER.Load Timesheets v2 · MER.Load Allocations · MER Prepaid Analysis | MER.SQL.Execute Python to Load Table, FIN.Cub.Revenue Planning.Load From MER Allocations |
| HEL.Contracts and Opportunities · HEL.SQL.Load Transactions · HEL.SQL Refresh Metadata | HEL.Cube.Contracts.Load from SQL, CRM.Cub.Opportunity.Load From HEL.SQL, HEL.SQL.Metadata Refresh.* |
| CRM.Opportunity Load To Revenue Planning | FIN.Cub.Revenue Planning.Load From CRM Opportunity |
| FIN.General Ledger Rountrip (+ PRE) | walks the }APQ C3 FIN Roundtrip Configuration step registry — ACT-11 / PLAN-1 / PLAN-2 |
| HEL.SLP.Load Payroll | HEL.SLP.Load Payroll → solara.py |
| RGL.Data Load.EU1 / .EU2 / .EU3 | RGL.Cub.FIN General Ledger TB.Load From RGL (+ .Caller), RGL.Cub.RGL Reporting Balances.Load From RGL |
| REC Loads · REC Loads part 2 | Cub.REC Transaction Load - Last Load / - Last Load From HEL; failures raise an "FPM Reconciliation Alert" e-mail |
| CORP Month End Refresh | CORP.Cub.Month End.Load from HEL |
TIM Timesheets. The cube has a rule file and fragments of it are quoted above, but the complete listing was never retrieved — treat its rule logic as partially documented.