Data Ingestion

Transformation Rules

Updated February 17, 2026

After data is parsed into the Stage Engine, Transformation Rules convert source field values into Cube dimension members. Account code 41137 becomes OneStream Account Revenue_Product_A. Entity TX-001 becomes Entity Texas. This guide covers the hierarchy of rule objects, every mapping type, and the nuances that trip up new implementers.

The Rule Hierarchy

Transformation Rules are organized in a three-level hierarchy:

diagramTransformation Rule Hierarchy

Loading diagram...

Level	What It Is	Key Property
Rule Group	A set of mapping rules for a single Cube dimension (e.g., Account rules, Entity rules)	Cube Dimension Name — ties the group to a specific dimension
Transformation Profile	A collection of Rule Groups — one per dimension that needs mapping	Assigned to a Workflow Profile via Transformation Profile Name
Individual Rules	The actual source-to-target mappings within a Rule Group	Mapping Type determines behavior

Security on Rule Groups

Each Rule Group has two security settings:

Access — Members of this group can view the Transformation Rules
Maintenance — Members of this group can edit rules and load/extract from the Transformation Rules page

Mapping Types

OneStream offers several mapping types, each suited to different transformation patterns:

One-to-One

The simplest mapping. One source value maps to exactly one target member.

Source Value	Target Value
`41137`	`Revenue_Product_A`
`05/31/2021`	`2021M5`
`Monthly`	`MTD`

One-to-One rules process in alphanumeric sort order by default. Use the Order field to assign a custom sort order.

ℹ️Info

The Scenario, Time, and View dimensions can only use One-to-One mapping rules. Other mapping types are not available for these dimensions.

Composite

Composite rules add conditional logic by referencing other dimensions. Dimensional tags let you include another dimension in the evaluation.

Syntax: DimensionTag#[pattern]:DimensionTag#[pattern]

For example, map any account starting with 199 in Entity Texas to a specific target:

plaintext

1A#[199?-???*]:E#[Texas]

Composite rules stop processing when a record meets the criteria. If a record could match more than one rule, set the Order field to ensure correct precedence — narrowest patterns first.

Range

Map a continuous range of source values to a single target. Ranges use the ~ separator.

Rule Expression	Target	Effect
`0004~3000`	`General_Expense`	Accounts 0004 through 3000 map to one target

⚠️Warning

Range values must have the same number of characters. A range of 4~3000 will not work — pad it as 0004~3000.

List

Map multiple specific source values to a single target using a semicolon-delimited list.

Rule Expression	Target	Effect
`41137;41139;41145`	`61000`	Three specific accounts map to one target

Mask (Wildcards)

Wildcards let you match patterns rather than exact values:

Wildcard	Behavior	Example	Matches
`*`	Any number of characters	`27*`	`270`, `2709`, `27XX-009`
`?`	Exactly one character	`27??`	`2709`, `2700` — but not `27999` or `2700-101`

Double-sided wildcards work too: *000* matches any value with characters before and after 000.

Pass-through mapping: Use * to * to map every source value to a target with the same name. This is common when source and OneStream metadata are already aligned.

💡Tip

Mask rules with embedded wildcards (e.g., *84*7*) run less efficiently than simpler patterns. Use the most specific pattern possible.

Lookup

Lookup rules reference data from the transformation cache or Cube to determine the target value. They are useful when the mapping depends on previously transformed data or on values already in the Cube.

Derivative Rules

Derivative rules go beyond simple mapping — they apply logic to staged data and generate additional records in the Stage environment. There are two types:

Source Derivatives

Applied to inbound source data before transformation. Common uses:

Check if a trial balance equals zero
Detect when an asset goes negative and needs reclassification as a liability
Create summary accounts by aggregating source records

Example expressions:

Expression	Effect
`A#[11*]=Cash`	Accounts starting with `11` aggregate to a new Account called `Cash`
`A#[12*]=AR`	Accounts starting with `12` aggregate to `AR` (interim — not stored)

Target Derivatives

Applied after transformation, operating on post-transformed records. Target derivatives reference the mapped values rather than the original source values.

Derivative Expression Types

Derivative rules can use additional expression types to perform calculations on the resulting member's data:

Type	Effect
None	Default — no calculation
Business Rule	Runs a Derivative-type Business Rule against the resulting data
Complex Expression	Inline script that runs against the resulting data
Multiply / Divide / Add / Subtract	Basic math against a specified value
Create If > x / Create If < x	Only create the derivative record if the value exceeds or is below a threshold

Reserved Characters

Three characters are reserved for Transformation Rule operations:

Character	Usage
`!`	Negation / exclusion
`?`	Single-character wildcard
`\|`	Logical OR

🛑Danger

Never use !, ?, or | in Source Member or Target Member names. They will cause mapping errors that are difficult to diagnose.

Rule Processing Order

Within a Rule Group, rules process in this order:

Derivative rules (Source) — Generate additional records before mapping
Mapping rules — Process in the order determined by the Rule Name (alphanumeric sort) unless overridden by the Order field
Derivative rules (Target) — Generate additional records after mapping

Composite rules stop processing for a record when a match is found. One-to-One and Mask rules are evaluated in sequence — the first match wins.

Assigning Profiles to Workflow Profiles

After building your Rule Groups and collecting them into a Transformation Profile, assign the profile to the Workflow Profile:

Navigate to the Workflow Profile's Integration Settings
Set Transformation Profile Name to your profile
The import process will now use these rules to transform staged data