Data Ingestion

Custom Relational Tables

Updated February 17, 2026

Not all data belongs in the Cube. The Cube is a multi-dimensional engine optimized for financial aggregation — Entities, Accounts, Scenarios, Time. But many real-world scenarios involve tabular data that doesn't fit a dimensional model: transaction registers, approval logs, lookup tables, configuration parameters, staging data for custom processing. For these, OneStream lets you create custom relational tables directly in the application's underlying MSSQL database.

When to Use Relational Tables

Use Case	Why Not the Cube?
Transaction registers (e.g., headcount change requests, approval logs)	Each row is a discrete event with a timestamp, user, and description — not a numeric value at a dimension intersection
Lookup / reference data (e.g., account mappings, exchange rate overrides)	Flat key-value or key-description pairs that drive Business Rule logic
Configuration tables (e.g., rule parameters, threshold values)	Administrators need to edit values without modifying Business Rule code
Staging / intermediate data	Data being transformed or validated before it reaches the Cube or an external system
Detail behind a Cube cell (e.g., line-item detail)	The Cube stores the aggregate; the relational table stores the breakdown

Both the Cube tables and your custom tables live in the same MSSQL application database. The difference is structural: Cube data is accessed through the Finance Engine; custom table data is accessed through SQL.

Database Server Connection Security

Before anyone can create or interact with custom tables, the Database Server Connection must be configured with the correct security settings. These are found under System > Security > Database Server Connections on the Internal connection.

Setting	Purpose	Default
`CanCreateAncillaryTables`	Master switch — must be `True` to allow table creation	`False`
`TableCreationGroupForAncillaryTables`	Security group whose members can create tables	Administrators
`MaintenanceGroupForAncillaryTables`	Security group whose members can insert, update, and delete rows	Administrators
`AccessGroupForAncillaryTables`	Security group whose members can read table data	Administrators
`CanEditAncillaryTableData`	Must be `True` for the SQL Table Editor to allow write access	`False`

⚠️Warning

Both CanCreateAncillaryTables and CanEditAncillaryTableData default to False. If you skip this step, table creation calls will silently fail or throw security errors. Configure these settings first.

Creating Tables via Business Rules

Use a Business Rule (typically an Extender or a Finance rule) to execute DDL against the application database. The pattern is:

Get a database connection with BRApi.Database.CreateApplicationDbConnInfo(si)
Build a CREATE TABLE statement as a string
Execute it with BRApi.Database.ExecuteActionQuery

1Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
2  Dim sql As String = "
3      CREATE TABLE XFW_Config_Params (
4          ParamKey    NVARCHAR(100) NOT NULL PRIMARY KEY,
5          ParamValue  NVARCHAR(500) NULL,
6          Description NVARCHAR(1000) NULL,
7          ModifiedBy  NVARCHAR(100) NULL,
8          ModifiedOn  DATETIME NULL DEFAULT GETDATE()
9      )"
10
11  BRApi.Database.ExecuteActionQuery(dbConnApp, sql, False, False)
12End Using

The two database execution methods serve different purposes:

ExecuteSql(dbConnInfo, sql, True) — Executes a SELECT query and returns a DataTable
ExecuteActionQuery(dbConnInfo, sql, useCommandTimeoutLarge, logErrors) — Executes a non-query (CREATE, INSERT, UPDATE, DELETE) and returns nothing. Pass False for useCommandTimeoutLarge unless the statement is long-running, and False for logErrors unless you want errors written to the server log
ExecuteActionQuery(dbConnInfo, sql, dbParamInfos, useCommandTimeoutLarge, logErrors) — Same as above but accepts a List(Of DbParamInfo) for parameterized queries. Pass Nothing/null when no parameters are needed

💡Tip

Wrap table creation in an existence check so the rule is safe to re-run. Query INFORMATION_SCHEMA.TABLES first, or use IF NOT EXISTS in the DDL statement.

Querying Tables

Reading from a custom table returns a standard .NET DataTable that you can iterate over in your Business Rule:

1Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
2  Dim sql As String = "SELECT ParamKey, ParamValue FROM XFW_Config_Params WHERE ParamKey = 'Threshold'"
3  Dim dt As DataTable = BRApi.Database.ExecuteSql(dbConnApp, sql, True)
4
5  If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then
6      Dim threshold As String = dt.Rows(0)("ParamValue").ToString()
7      ' Use the threshold value in downstream logic
8  End If
9End Using

ℹ️Info

ExecuteSql with True always returns a DataTable. If the query returns no rows, the DataTable will have zero rows but will still have the correct column schema. Always check Rows.Count before accessing row data.

Writing Data

INSERT, UPDATE, and DELETE operations use ExecuteActionQuery:

Inserting Rows

1Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
2  Dim sql As String = String.Format(
3      "INSERT INTO XFW_Config_Params (ParamKey, ParamValue, Description, ModifiedBy) VALUES ('{0}', '{1}', '{2}', '{3}')",
4      "RetentionDays", "90", "Number of days to retain history", si.UserName)
5
6  BRApi.Database.ExecuteActionQuery(dbConnApp, sql, False, False)
7End Using

Updating Rows

1Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
2  Dim sql As String = String.Format(
3      "UPDATE XFW_Config_Params SET ParamValue = '{0}', ModifiedBy = '{1}', ModifiedOn = GETDATE() WHERE ParamKey = '{2}'",
4      "120", si.UserName, "RetentionDays")
5
6  BRApi.Database.ExecuteActionQuery(dbConnApp, sql, False, False)
7End Using

Deleting Rows

1Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
2  Dim sql As String = "DELETE FROM XFW_Config_Params WHERE ParamKey = 'RetentionDays'"
3  BRApi.Database.ExecuteActionQuery(dbConnApp, sql, False, False)
4End Using

Naming Conventions and Restrictions

OneStream reserves a large set of table name prefixes for its internal system tables. Custom tables must not use names that start with any of these prefixes:

Reserved Prefix
`AppProperty*`	`DataAttachment*`	`FileContents*`	`Parser*`
`Attachment*`	`DataCellDetail*`	`FileInfo*`	`Relationship*`
`Audit*`	`DataEntry*`	`Folder*`	`SecRoles*`
`Data*`	`DataMgmt*`	`Form*`	`Stage*`
`CalcStatus*`	`DataRecord*`	`FxRate*`	`System*`
`Certify*`	`DataUnit*`	`ICMatchStatus*`	`Taskflow*`
`Confirm*`	`Dim*`	`Journal*`	`Time*`
`Cube*`	`Dashboard*`	`Member*`	`Workflow*`

Internally, OneStream's IsXFDatabaseTable method checks whether a table name matches any registered system table. System tables are read-only to external writes — any attempt to INSERT, UPDATE, or DELETE against them will throw a security error.

🛑Danger

Never name a custom table starting with any reserved prefix. A table named DataExport or CubeResults will collide with OneStream internals and either fail to create or be treated as a system table (blocking writes).

Recommended Convention

Prefix all custom tables with XFW_ followed by a short solution identifier:

plaintext

1XFW_PLP_Register        — People Planning register
2XFW_Config_Params       — Configuration parameters
3XFW_GL_JournalDetail    — General Ledger journal line items
4XFW_RPT_ExchangeRates   — Reporting exchange rate overrides

The XFW_ prefix is a widely adopted community convention. It clearly signals "this is a custom table" and avoids any collision with current or future OneStream system tables.

Surfacing Data to Users

Custom tables are only useful if users can see and interact with the data. OneStream provides two primary mechanisms.

SQL Table Editor

The SQL Table Editor is a Dashboard component that directly displays and edits a database table. It requires no Business Rule code — just configuration properties:

Property	Description
Database Location	`Application`, `Framework`, or `External` — almost always `Application` for custom tables
Table Name	The exact table name (e.g., `XFW_PLP_Register`)
Where Clause	SQL WHERE filter (e.g., `WFProfileName = 'WFProfile' And WFScenarioName = 'WFScenario'`)
Order By Clause	SQL ORDER BY expression (e.g., `RegisterID`). SQL functions are not allowed here
Default For Columns Are Visible	`True` to show all columns; `False` to hide all and selectively enable
Default For Allow Column Updates	`True` to allow inline editing of existing rows

💡Tip

The SQL Table Editor supports Workflow substitution variables in the Where Clause. Use 'WFProfile', 'WFScenario', and 'WFTime' to automatically filter the table to the user's current Workflow context. This is the most common pattern for register-style tables.

The SQL Table Editor is best for straightforward CRUD on a single table. Users can add rows, edit cells inline, and delete rows — all governed by the ancillary table security settings described earlier.

Table Views (Spreadsheet Business Rules)

For more complex scenarios — pulling from multiple tables, computed columns, custom validation on save — use Table Views. Table Views are powered by a Spreadsheet Business Rule with three function types:

Function Type	Purpose
`GetCustomSubstVarsInUse`	Defines Dashboard Parameter interaction (optional)
`GetTableView`	Defines the source data: queries one or more tables, builds column definitions, returns a `DataTable` to the Spreadsheet
`SaveTableView`	Handles write-back: receives the modified row from the Spreadsheet and executes INSERT/UPDATE/DELETE against the target table

Table Views render inside the OneStream Spreadsheet control or Excel Add-In. They support client-side features like calculated columns and pick-list validation.

ℹ️Info

Table Views are not a replacement for the SQL Table Editor — they serve different needs. Use the SQL Table Editor for simple single-table display and editing. Use Table Views when you need computed columns, multi-source joins, custom save logic, or tighter control over the user experience.

Size Considerations

Neither the SQL Table Editor nor Table Views are designed for massive datasets. The Spreadsheet control does not support paging, so large result sets will degrade performance. Keep these guidelines in mind:

Filter aggressively with Where Clauses — show only what the user needs for the current Workflow context
Cell content length matters as much as row count — long text values dramatically increase the payload
For very large tables (tens of thousands of rows), consider the Grid Viewer Dashboard component, which supports paging

Processing Relational Data into the Cube

A common pattern bridges relational and dimensional data: a Finance Business Rule reads from a custom table and writes the results into the Cube. This is how detail-level relational data becomes aggregated financial data.

1' Read detail rows from the custom table
2Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
3  Dim sql As String = String.Format(
4      "SELECT Account, Amount FROM XFW_GL_JournalDetail WHERE Entity = '{0}' AND Period = '{1}'",
5      api.Entity.Name, api.Time.Name)
6  Dim dt As DataTable = BRApi.Database.ExecuteSql(dbConnApp, sql, True)
7
8  If dt IsNot Nothing Then
9      For Each row As DataRow In dt.Rows
10          Dim account As String = row("Account").ToString()
11          Dim amount As Decimal = CDec(row("Amount"))
12
13          ' Write each row into the Cube
14          api.Data.Calculate("A#" & account & " = " & amount.ToString())
15      Next
16  End If
17End Using

This pattern appears in scenarios like People Planning (reading a salary register into Cube accounts), Capital Planning (reading asset detail into depreciation accounts), and any process where transactional detail rolls up into financial totals.

⚠️Warning

When reading from custom tables inside a Finance Business Rule, always filter by the current Entity and Time period. Without filters, you risk loading the entire table on every calculation — which will be slow and may produce incorrect results if the rule runs for multiple Entities in parallel.