Introduction

As part of our ongoing commitment to improving functionality and performance, the 9.0 OneStream release includes significant changes to the database schema related to Stage operations and Cube data storage. These changes are designed to enhance data management, streamline processes, and provide increased flexibility for future developments.

Overview of Changes

The following tables have been replaced by views:

• StageSourceData
• StageSummaryTargetData
• StageTargetData

The following tables have been added, to increase normalization of Stage-related data structures:

• StageDataName
• StageSourceDataSlim
• StageSummaryTargetDataSlim
• StageTargetDataSlim

The following views have been added:

• vStageSourceAndAttributeData
• vStageSourceAndAttributeDataSlim
• vStageSourceAndTargetDataR
• vStageSourceAndTargetDataSlim
• vStageSourceAndTargetDataWithAttributesR
• vStageSourceAndTargetDataWithAttributesSlim
• vStageSourceData
• vStageTargetData
• vStageTargetDataR

The StageRules table now has an additional RuleIdentityId column, which can be leveraged in joins with the new “Slim” tables. StageRulesHistory and AuditStageRules also contain the new column.

Indexing and partitioning strategies on BinaryData*, CalcStatus, DataUnitCacheTimestamp , DataRecord*, Stage*, and Taskflow* tables have been streamlined and optimized. See the Appendix for details of modified indexes.

Impact on Existing Applications

The following advice applies only to custom queries that developers, partners, or administrators might implement in their applications. Internal operations (e.g. standard Import/Validate/Load workflows) are unaffected.

The changes to the database schema in OneStream 9.0 might require small modifications to existing applications.

Although we tried to maintain compatibility as much as possible, it is suggested to review and update SQL queries and application logic that interact with tables StageSourceData, StageSummaryTargetData, and StageTargetData. In most cases no change should be necessary, as SQL statements will typically retrieve data from a named object regardless of whether it is a table or a view, and the old table names are now aliases for views.

However, if the query or api is explicitly looking for one of the removed tables (rather than a table or view), it will be necessary to change it so that the query can accept a view as well as a table.

Note that performance of such queries might differ from previous releases.

Impact on New Development

The following advice applies only to custom queries that developers, partners, or administrators might implement in their applications. Internal operations (e.g. standard Import/Validate/Load workflows) are unaffected.

Member names are now stored only once, in the StageDataName table, and referred to from “Slim” tables and views via IDs. In future development, in a few cases it might be more performant to move from operating on the old Stage tables to working with the new “Slim” counterparts, joining on StageDataName only where necessary. Note that these members are not necessarily related to Metadata members; they represent any type of item name involved in stage loads (source members, etc.)

These JOINs should be done sparingly; in most cases, leveraging views will likely be preferable, since they already do that work for you (returning actual member names as strings) and allow for better caching. Unless there is a clear and significant performance difference, queries should be performed on views.

In these new “Slim” tables, the string and uniqueidentifier columns have been replaced with integer types that reference the “NameId” column in the StageDataName table.

When querying “Slim” tables, the integer values in columns are used to join with the “NameId” table of the StageDataName table to retrieve the actual names.

As an example, if we wanted to obtain the text value of the “CnId” column in the StageSourceDataSlim table, we would now need to perform a SQL join to the StageDataName on the NameId column.

SELECT TOP (100) 
     Slim.Wfk, Slim.Wsk, Slim.Wtk, Slim.CnId, Sdn.[Name]
FROM [dbo].[StageSourceDataSlim] AS Slim
LEFT OUTER JOIN [dbo].[StageDataName] AS Sdn
ON Sdn.NameId = Slim.CnId

IMPORTANT! The collation of the “Name” column in the StageDataName table has been set to “Latin1_General_100_BIN2”.  The “Latin1_General_100_BIN2” collation allows us to compare string values properly between Unicode and non-unicode values. If you need to perform a join to the StageDataName table on the “Name” column, you will need to force the source column in the join to use this collation. 

An example of this is below where a string column using the default database collation is joined with the [Name] column of the StageDataName table.  The “COLLATE” clause is added to the join to force a binary comparison of the two column values. 

SELECT Src.Et, Sdn.NameId
FROM [dbo].[StageSourceDataSlim] AS Src
LEFT OUTER JOIN [dbo].[StageDataName] AS Sdn
ON Sdn.Name = Src.Et COLLATE Latin1_General_100_BIN2

Failure to add this collate clause when joining on the [Name] column of the StageDataName table will result in an error like the one below.

Msg 468, Level 16, State 9, Line 4
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_100_BIN2" in the equal to operation.

There is no requirement to use COLLATE when operating on the prebuilt views.

Conclusion

The database schema changes in OneStream 9.0 represent a significant step forward in our data management capabilities. By optimizing table structures and improving indexing, we are laying the groundwork for more robust and scalable applications. We appreciate your cooperation during this transition and are confident that these improvements will provide substantial benefits to our operations.

Appendix

Modified Indexes

The following changes to indexes have been implemented: