Recent Content
Tech Talks After Hours: Creating Entity Dimensions from a UD via Rules Part 2
Entity Relationships created via UD Text properties driving rules! Ludo de Paz returns as the SME joining Tom Linton as they continue their advanced rule writing examination of the Planning/Forecast use case of creating Entities and Relationships via rules from UD text fields! Here we focus on creating/updating Entity relationships!Tech Talks After Hours: Creating Entity Dimensions from a UD via Rules
On this advanced rule writing edition of Tech Talk After Hours, Ludo de Paz and Tom Linton review a use case where dashboard selections, data management steps, and extender business rules come together to create an Entity dimension from an existing User Defined Dimension via rules!OneStream 9.0 Stage Schema Changes
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. 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 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: Table Name Index Name Comment Member IX_Member1 New nonclustered index on Name, DimTypeId, DimId StageArchivesInformation IX_StageArchivesInformation0 Index is now a unique clustered index. The “UniqueID” column has been added to this index. StageAttributeData IX_StageAttributeData0 This index has been removed. StateBiBlendInformation IX_StageBiBlendInformation0 New unique nonclustered index added using the column “BlendTableName” in ascending order. StageRuleGroupsHistory IX_StageRuleGroupsHistory0 Index has been removed. StageRuleProfileMembersHistory IX_StargeRuleProfileMembersHistory0 Index has been removed. StageRuleProfilesHistory IX_ StageRuleProfilesHistory0 Index has been removed. StageRules IX_StageRules1 New index on the “RuleIdentityId” column in ascending order was created. StageRulesHistory IX_StageRulesHistory0 Column “RuleIdentityId” added to index. Index changed to a unique nonclustered index. StageToFinanceDataUnit IX_StageToFinanceDataUnit0 Index is now nonclustered. “WorkflowProfileKey” column has been removed. “FillFactor” has been removed. StageToFinanceDataUnit IX_StageToFinanceDataUnit1 The “WorkflowProfileKey”, “WorkflowScenarioKey” and “WorkflowTimeKey” have been replaced with the “ScenarioId”, “TimeId”, “CubeId” and “EntityId” columns. StageToFinanceDataUnit IX_StageToFinanceDataUnit2 Index has been removed. StageToFinanceLoadResult IX_StageToFinanceLoadResult0 Index is now a unique clustered index. The “SummaryRowID” column has been added and the “FillFactor” has been removed. StageToFinanceLoadResult IX_StageToFinanceLoadResult1 The “WorkflowProfileKey” column has been removed. StageToFinanceValidationError IX_StageToFinanceValidationError0 Now a unique clustered index with “FillFactor” removed. StageToFinanceValidationError IX_StageToFinanceValidationError1 Index has been removed. TaskflowStepInstances IX_TaskflowStepInstances0 “FillFactor” increased from 80 to 95. TaskflowStepLog IX_TaskflowStepLog0 Now a unique clustered index. “UniqueID” column has been added. “FillFactor” increased from 80 to 95.
