OneStream

Data Integration and Transformation

Every FP&A process starts with actuals. Before you can build a budget or update a forecast, the cube needs to contain the company's real financial data — imported from ERPs, mapped to your dimension structure, and validated. This guide covers the data integration pipeline: how data gets from source systems into the cube, and how actual data becomes the foundation for planning.

The Data Journey

Data integration in OneStream follows a consistent pipeline: extract from source, land in staging, transform and map, validate, and load to the cube. Each step is orchestrated by a Data Management sequence — a configurable series of steps that runs on demand or on a schedule.
diagramData Integration Pipeline

Loading diagram...

The developer's job at each step:
  • Connectors and Parsers — Configure or write Business Rules that pull data from external systems (databases, APIs, files). Connector rules define the field mapping; Parser rules handle custom file formats.
  • Staging Tables — No code needed here — data lands in standard staging tables automatically. You can inspect staged data through the OneStream UI.
  • Transformation Rules — Map source account codes, entity codes, and other fields to OneStream dimension members. Handle sign conventions, default values, and validation logic.
  • Cube Load — Built-in functionality moves validated staging data into the cube. You configure which dimensions to load and how to handle duplicates (replace, accumulate, etc.).

Data Sources for FP&A

A typical FP&A implementation pulls data from several sources. Each source plays a different role in the planning process.
SourceConnection MethodFP&A Role
General Ledger (ERP)ODBC, REST API, or flat fileActual financial data — the baseline for budgets and forecasts
HR / PayrollODBC, flat file, or APIHeadcount, salary bands, benefits rates — inputs for workforce planning
Sales / CRMREST API or flat filePipeline data, bookings, sales volumes — inputs for revenue planning
Manual InputJournal entries, Cube Views, dashboard components, Excel add-inManagement assumptions, growth rates, one-time adjustments
External DataFlat file or APIFX rates, commodity prices, inflation indices
ℹ️Info
Not every data source loads directly to the cube. HR and CRM data often load into relational tables (for detailed employee or deal records) rather than the cube. A Business Rule then summarizes the detail and writes aggregated numbers to the cube. This pattern is covered in Forecasts and Custom Planning Solutions.

Staging and Transformation

When data arrives through a Connector, it lands in staging tables — relational tables within the OneStream database that hold raw, untransformed data. Each row represents a source record with its original field values.
Transformation rules then map source values to OneStream dimension members. For example, if your ERP uses account code "4010" for Product Revenue but your OneStream Account dimension uses "ProductRevenue", the transformation rule creates that mapping.
Transformation happens at multiple levels:
  • Direct maps — One-to-one: source value "4010" → OneStream member "ProductRevenue"
  • Conditional maps — Based on combinations: source account "5000" from entity "US" → "SalaryExpense", but "5000" from entity "EMEA" → "WagesExpense"
  • Sign flips — Revenue accounts in some GLs are stored as negative; OneStream expects positive. Transformation rules apply sign conventions.
  • Validation — Rows that fail to map (unmapped accounts, invalid entities) are flagged for review rather than silently dropped.
⚠️Warning
Transformation is usually the most time-consuming part of a OneStream implementation. Getting the account and entity mappings right — especially when integrating multiple ERPs with different charts of accounts — requires close collaboration with the finance team. Budget generous time for mapping exercises.
The transformation step can also include Transformation Business Rules — custom code that runs during the transformation process. These are useful for calculations on staged data (e.g., converting currencies before loading) or complex conditional mappings that cannot be expressed as static mapping tables.

Seeding: From Actuals to Budget

Once actuals are in the cube, the next step in most FP&A processes is seeding — copying actual data to the Budget or Forecast scenario as a starting point. Instead of building the budget from zero, planners start with "what actually happened" and then adjust.
There are two primary approaches to seeding in OneStream:

Data Management Copy Data Step

The simplest approach is a Copy Data step in a Data Management sequence. You configure the source (Scenario = Actual) and target (Scenario = Budget), select which dimensions to copy, and run the step. No code required.
This works well for straightforward one-time seeding at the start of a budget cycle.

Finance Business Rule Seeding

For more control — conditional seeding, partial copies, adjustments during the copy — use a Finance Business Rule. The api.Data.Calculate method can reference across scenarios:
1If api.FunctionType = FinanceFunctionType.Calculate Then
2  ' Seed Budget from prior year Actuals with a 3% uplift
3  api.Data.Calculate("S#Budget = S#Actual * 1.03")
4End If
You can be selective — seed only specific accounts, apply different adjustments by account type, or conditionally skip accounts where budget data has already been entered:
1If api.FunctionType = FinanceFunctionType.Calculate Then
2  ' Only seed revenue accounts — leave expense accounts for manual input
3  api.Data.Calculate("A#Revenue:S#Budget = A#Revenue:S#Actual * 1.05")
4  api.Data.Calculate("A#ServiceRevenue:S#Budget = A#ServiceRevenue:S#Actual * 1.08")
5End If
💡Tip
Seeding logic often lives in a Finance Business Rule under the Calculate function type, scoped to the Budget scenario using api.Pov.Scenario.MemberName. This way, seeding runs only when the Budget scenario is calculated — not when Actual or Forecast scenarios are processed.

Rolling Forecast Seeding

Rolling forecasts add a dynamic twist to seeding. Instead of a one-time copy at the start of the year, Forecast_Working is updated each cycle. Actuals are seeded into Forecast_Working for closed months while open months retain projected values. When the cycle is approved, Forecast_Working is published to the corresponding monthly snapshot (Forecast_M1 through Forecast_M12).
diagramRolling Forecast: Actual vs. Projected Months

Loading diagram...

In this example, the forecast as of March has January through March populated with actual data and April through December with projected data. Next month, April actuals will be seeded into Forecast_Working, and the completed March forecast will have been saved to Forecast_M3.
The key to implementing this is determining the cutoff point — which months are "closed" (use actuals) and which are "open" (keep forecast). A Finance Business Rule handles this dynamically:
1If api.FunctionType = FinanceFunctionType.Calculate Then
2  ' Determine if the current period is closed (actuals available)
3  Dim currentPeriodNum As Integer = api.Time.GetPeriodNumFromId(api.Pov.TimeDimPk.MemberId)
4  Dim lastClosedPeriod As Integer = 3 ' March — typically read from an application setting
5
6  If currentPeriodNum <= lastClosedPeriod Then
7      ' Closed month — seed Forecast_Working with actuals
8      api.Data.Calculate("S#Forecast_Working = S#Actual")
9  Else
10      ' Open month — keep existing forecast (or run forecast calculations)
11  End If
12End If
ℹ️Info
The lastClosedPeriod value is typically stored in an application setting or a dedicated cube cell rather than hard-coded. This lets the finance team advance the cutoff each month without modifying the Business Rule.
💡Tip
As an alternative to the Business Rule approach above, OneStream's Hybrid Scenario setting can automate the seeding step at the scenario configuration level. Set the DataBindingType on Forecast_Working to "Copy Input Data from Source Scenario" with Actual as the source, and the platform copies actuals automatically whenever Forecast_Working is calculated. For finer control — such as copying only closed months — use "Copy Input Data from Business Rule" and point to a Finance Rule with the period-based logic. See the Forecasts guide for details on all three Hybrid Scenario options.
ℹ️Info
The seeding logic above uses FinanceFunctionType.Calculate, which fires on every calculation and consolidation pass. For on-demand control — particularly when seeding should run independently of calculation — isolate the logic in a CustomCalculate function tied to its own Data Management step. See the Forecasts guide for the full pattern with separate Seed, Calculate, and Publish phases.

ABC Example: Integrating Three GL Systems

ABC Manufacturing's biggest integration challenge is consolidating data from three different ERPs — each with its own chart of accounts, entity coding, and sign conventions.
Source System Summary:
EntityERPConnectionKey Challenges
US_CorpSAPODBC connector6-digit account codes, expenses stored as positive, cost centers map to UD1
EMEA_SubOracleREST API4-digit account codes, different account groupings, multi-currency (EUR, GBP)
APAC_SubNetSuiteCSV file exportAccount names (not codes), revenue stored as negative, department field maps to UD1
Each source requires its own Connector (or file parser) and its own set of transformation mappings. The transformation step is where the three different charts of accounts converge into ABC's unified Account dimension.
Here is a simplified transformation rule snippet that handles the sign convention and account mapping for the NetSuite CSV import:
1' Transformation Business Rule — NetSuite sign convention
2' NetSuite stores revenue as negative and expenses as positive
3' OneStream expects all amounts as positive (sign is handled by Account Type)
4If args.OperationName = BREventOperationType.Transformation.EndValidateTransform.Name Then
5  Dim processInfo As ValidationTransformationProcessInfo = DirectCast(args.Inputs(0), ValidationTransformationProcessInfo)
6
7  ' Flip sign for revenue accounts (mapped to Accounts starting with "Rev")
8  For Each record In processInfo.TransformationRecords
9      If record.Account.StartsWith("Rev") Then
10          record.Amount = Math.Abs(record.Amount)
11      End If
12  Next
13End If
Once all three sources are loaded and transformed, ABC has a complete set of actuals in the cube. The next step is seeding those actuals into the Budget scenario as a starting point for the annual planning cycle — using the Finance Business Rule seeding pattern described above.
💡Tip
When integrating multiple GL systems, build and test one source at a time. Get US_Corp (SAP) fully loading, transforming, and validating before adding EMEA_Sub (Oracle). This iterative approach makes debugging transformation mapping issues much easier.

Related Content