Recent Content
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 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: 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.
Question: Is it possible to filter the list of members returned in a parameter/drop-down box based on a user’s cube data cell (“slice”) security access?
Answer One approach would be to use a bound list parameter together with a dashboard dataset business rule. Source: Office Hours 2021-04-29 - Partner Enablement
Matrix Consolidation: Eliminating Beyond Legal Entity
Purpose of the document To goal of this document is to share our experience in designing for a matrix consolidation requirement, as well as to drive discussion on the topic. What we mean by “matrix consolidation” Matrix consolidation is a term commonly used when finance teams want to prepare their management & statutory financials concurrently. This prevents the need to maintain separate scenarios and processes in the system. It will usually involve running eliminations on something more than just legal entity. In OneStream this can mean using a user defined dimension as part of the elimination process. The Requirement A common use case is to run eliminations between Profit Centres or Segments. Whilst inter-profit-centre eliminations will be used as the example in this document, it is not the only potential use case. The requirement we will attempt to solve for is that the customer wants an elimination to only occur at the first common parent in both the Legal Entity and Profit Centre hierarchies. First let’s look at the two broad ways in which this can be achieved. ENTITY DIMENSION OR USER DEFINED (UD) DIMENSION So, there is a requirement to do eliminations on a level of detail below the legal entity/company code level. The example we will use in the section is where the customer wants to generate eliminations between Profit Centres (PC). You have two main options on how to tackle this, outlined in the following two sub-sections. OPTION 1: ENTITY DIMENSION Include this Profit Centre detail in your entity dimension as base members. These will be children of the legal entity members. OPTION 1: ENTITY DIMENSION Include this Profit Centre detail in your entity dimension as base members. Pros Cons Business Logic · No additional Logic required to get eliminations running by Profit Centre. · Impacts consolidation performance more than option 2 in a typical setup, due to multiplication of members in the entity dimension (i.e. more data units) that are to be consolidated. Exact impact need to be analysed in each project. · If you move a PC in the entity hierarchy, you will need to reconsolidate all history. Dimensions · Uses fewer UD dimensions than option 2. · Generally, only appropriate when Profit Centres are unique to entities, since otherwise they will need to be duplicated for each entity. · Can end up with a very large entity dimension · To achieve some reporting, alternative entity hierarchies (and therefore additional consolidations) may be required. · Often leads to creation of additional “dummy” or “journal” Profit Centre entities to contain data that doesn’t need to be captured by Profit Centre (e.g. Balance Sheet Data). This creates even more entities to be consolidated! · When Profit Centres are not unique to entities, shared Profit Centres will create lots of duplicate entities and should be avoided. · Less flexible since profit centres need to be created & moved within the entity hierarchy. Workflows · If the responsibility structure (and therefore workflow design) is by Profit centre, then can make workflow design/build better. · If the responsibility structure is not based around Profit Centre, more entities will need to be assigned and considered in workflow design. · Makes Profit Centres the basis for everything where data is stored, processed and locked. Reporting & Matching · May align with the way it was done in legacy systems, so users are familiar with the approach. · Standard IC matching reports will work for Profit Centre matching (although this requirement is less common from our experience). · Out-of-the-box matching will now only be at PC level. Legal Entity matching will require custom reporting. · Alternative entity hierarchies (and therefore consolidations) may be required to achieve some reporting. · Execution of consolidation required to see legal entity level data (as they will be parent entities) Security · Native using the entity dimension. · Requires maintenance on a Profit Centre level even if not required on that level. OPTION 2: USER DEFINED DIMENSION Create a Profit Centre dimension in a UD. OPTION 2: USER DEFINED (UD) DIMENSION Include the Profit Centre detail in a User Defined Dimension Pros Cons Business Logic · Logic can be customised to specific requirements. · Does not add additional members to the entity dimension (i.e. data units), which is beneficial for consolidation performance in a typical setup. · Running a consolidation, will run a statutory and management consolidation in parallel. · Requires additional development time for business logic if not part of a starter kit. Dimensions · A cleaner entity dimension to support legal entity and group reporting. · Matrix view of consolidation can be created (e.g. with entities in rows and Profit Centres in columns) · Can be combined with extensibility if Profit Centres aren’t applicable to all entities/divisions. · Requires the use of two UD dimensions (one for Profit Centre and another for PC counterparty). This is discussed later. Workflows · Often more closely aligns with the responsibility structure for Actuals (by legal entity). Reporting & Matching · Standard IC matching reports will support legal entity matching. · Consolidation not required to view total legal entity values (pre-elimination data) · Custom IC matching reports may be required for Profit Centre matching. This is less common as a requirement. Security · Native using the entity dimension if security is driven by Legal Entity. · Requires slice security (via Cube Data Access security) if required at Profit Centre level (can impact reporting performance if security is complex). Other Design Considerations Data quality of matrix counterpart – Remember, all intercompany data from the source system needs to be sourced for all matrix dimensions. It will negatively impact user experience if this data is not readily and accurately available in the source system (lots of manual input will be required). Stability of the matrix dimension – This is to say, in your situation, will the Profit Centre hierarchy change regularly with relationships changing? This requires significant consideration in the design phase. Some discussion points are included in a later section. New or existing application – The choice of solution may depend on whether this is a new implementation or an addition to an existing one. It will likely be easier to add a new UD to an existing application rather than re-develop the entity dimension! Performance – Common design considerations of performance, data unit sizes, number of data units etc. apply. Elimination vs. Matching – Remember that just because a customer wants their eliminations to happen on PC doesn’t mean that they need to do their month end intercompany matching at this level. It’s important to clarify this as two separate requirements during gathering. Workflow – Ensure you consider the responsibility structure of the organisation as this will have a big impact on the decision. If the true process (loading, locking, calculating & certifying the data) is by Profit Centre, then this could be a good justification for using the Entity dimension (option 1). However, it’s much more typical that these are based on legal entity for Actuals, making a UD solution (option 2) more appropriate. Option Overview The best approach will vary depending on the specific requirements, but the above gives some common indications of the benefits & drawbacks of each approach. Adding members to the entity dimension creates additional overhead during consolidation since the system must run the data unit calculation sequence (DUCS), consolidate and check the status on each entity member. Therefore, including profit centre in the entity dimension will often be slower than using a UD (in the presence of typical data volumes, exceptions always apply!). Regardless of the approach, remember that with the default eliminations always occur at the first common parent in the Entity dimension. If the client wants something different, then you would be looking at a “non-matrix” solution (i.e. separate cubes for statutory and management). But that is a different topic… Since option 1 mostly uses system-default logic for processing and eliminating the data, the setup is mostly straight forward. Therefore, the rest of the document focuses on how to design for Option 2, using a user defined dimension to contain this detail and run eliminations. Out of the Box - view of Eliminations It is worth stating that just because a client says “we need the eliminations to run by profit centre” doesn’t mean that they need to implement a full matrix consolidation solution. If they don’t need the profit centre elimination to happen at the first common parent in the PC hierarchy, then the out of the box eliminations will suffice as you can report the eliminated data simply by selecting the correct combination of members (Origin, PC, etc.). For those less familiar with the topic, let’s just take a moment to set up a simple example that shows the default behaviour of eliminations in OneStream. We have a Profit Centre dimension in UD1, and an entity dimension for the legal entity members as follows (all entities are using USD only & are 100% owned, for the sake of a simple example): There is an intercompany transaction between the legal entities Manchester & Houston: Within Manchester it is captured within the Finance PC, and within the Houston Sales PC. When out-of-the-box eliminations are run, we will see the following results (eliminations in red, consolidated results in the blue box): The eliminations occur at the first common parent in the entity dimension (in this case the first common parent is the Main Group). In the UD1 the eliminations happen on the same member as the original data, so at the group level we still see the plug amounts by Profit Centre. If we zoom into the Profit Centre dimension (UD1) at the top Main Group reporting entity member, we see the following, where 100 is the aggregated difference on the plug account of the two base level Profit Centres Finance1 and Sales1: Matrix Consolidation - View of Eliminations Now let’s imagine we have the same setup but want to apply matrix consolidation. We have the same data, but now we are capturing the Counterpart Profit Centre for each transaction: Notice how in the below screenshot our eliminations will be happening on a new “elimination member” within UD1, rather than the member the data sits on (highlighted in the green boxes below; the required elimination members are discussed further in the next section on the setup). The member where the elimination occurs represents the first common parent of the PC & Counterparty PC in the hierarchy (in our example this is the “Top_PC” member in UD1). Again, if we look at this result in more detail at the Main Group entity level, you can now see that within the UD1 hierarchy, the elimination doesn’t occur until the first common parent in the UD dimension. So, at “Top_PC” the data is eliminated, but at descendant UD1 members, it is not (e.g. Admin_PC, Finance_PC, Sales_PC). Note that we have the same result at the Top Profit centre and Group entity, but the way we get there is different. Now that we understand the situation we are trying to tackle, let’s look at the setup used in this example. Setup The following items are configured in our matrix consolidation example. Entity No changes are required to the entity dimension for matrix consolidation. Account No changes are required to the account dimension for matrix consolidation. We will use the same plug accounts. UD1 – Profit centre Some additional elimination members are required in our UD1 as follows. Whilst UD1 is used in our example, the usual design decisions apply to which UD you use. These new elimination members will be required at every point an elimination may happen, so you can see that this can potentially add a large number of members to your existing hierarchy. A common naming convention is often used to allow the system to derive where to post the elimination. In this case you can see it is the parent member name with a prefix of “Elim_”. Alternatively, you could use text fields to store this information. Either way, the logic will rely on this being updated accurately and consistently. Tip: Ensure your consolidation logic provides helpful error messages if it finds that an elimination member does not exist or is misconfigured. UD7 - NEW Counterparty PROFIT CENTRE A new dimension is needed to capture the counterparty Profit Centre information. Like the Intercompany dimension in OneStream, this can be (and almost always is) a simple flat list of the base counterparties. All relevant intercompany data now needs to be analysed by this dimension so input forms & transformation rules will need updating. In data models where (almost) all UDs are already in use, this element can be challenging and requires consideration. Whilst UD7 is used in our example, the usual design decisions apply to which UD you use. Remember that this dimension is simply used to capture the counterparty so if your design is already using lots of dimensions then you may be able to combine this with other supplementary data, or maybe even use UD8 (although this will need additional consideration in your dynamic reporting design). Tip: Consider how this dimension will be maintained going forwards as it will be important for the logic that all members exist with the same naming in this counterparty dimension. Consider if the counterparty dimension could/should be automated to align with the main PC dimension. Business Logic Since, unlike the entity dimension, all parents in a UD are calculated on-the-fly this approach will require additional eliminations to be calculated. You will need to store your new matrix consolidation logic somewhere; in our case it is a business rule attached to the cube, but it could also be attached to a member formula: Tip: You DO NOT need to switch on custom consolidation algorithm on the cube to achieve a matrix consolidation result. Always consider the wider requirements & design. Reporting Custom reports will need to be developed to allow users to do IC matching and report on the resulting eliminations meaningfully. If the customer already does their elimination like this, they should have existing specifications that can be designed for. But if not, end users will need to understand Matrix consolidation when they build their own reports/Quick Views or just run LE-based reports with “Top” for Profit Centres. Tip: You can use Data Set business rules to help you efficiently gather your data for interactive dashboard & reports. Business Logic Consolidation Algorithm When a matrix consolidation requirement exists, it has been commonly observed that consultants will switch on the Custom Consolidation algorithm on the relevant cube(s). However simply because this stores the Share data, this has a negative impact on consolidation performance, and database size. Before you reach for the Custom algorithm though, I would recommend considering calculating the matrix elimination adjustments during the calculation pass of C#Elimination, within a UD member (potentially within your data audit dimension). This will allow you to remain on the Standard or Org-by-Period algorithm and within this member you can update the standard eliminations with the PC detail. Of course, you may have other requirements that lead to you using the Custom algorithm, in which case the approach for matrix eliminations can be determined in the context of the overall design. Tip: Consider whether matrix eliminations are required for all processes & scenarios and ensure it is only running on those where it is truly required. Useful Snippets The general approach for writing a matrix consolidation rule is to check that the elimination only occurs at the first common parent – other than that it will follow standard OneStream rule writing techniques such as using data buffers. The following functions can be useful (comments correct as of version 8.5): Function Comment api.Members.GetFirstCommonParent() You will want to use this function to check both the entity and profit centre parents to see if they’re common to the IC or counterparty member. api.Members.IsDescendant() Note that this doesn’t check whether a descendant has a consolidation percentage greater than zero. So, if doing org-by-period this may need additional consideration. api.Entity.PercentConsolidation() Useful for checking whether entity is being consolidated. Ensure you only pass valid parent/entity combinations into the function. Example rule Attached to this paper you will find a sample rule that can be used as a starting point to implement matrix consolidation. Disclaimer: The provided rule is an example only, and its purpose it to demonstrate an approach that can be taken. If used as a starting point, then all care should be taken to adapt and thoroughly test it before implementation. Updates may be made, without notice, to the example in future. Whilst there are arguably different ways to approach this, the example takes the following approach: Retrieves a data buffer of the system generated eliminations. Reallocates the elimination (both IC & Plug account entry) to the correct PC. Reverses lower-level eliminations from current eliminations (without this step the process will repeat at each parent after the first common parent). Clears the system Elimination as “No Data”. Save Results. This should be assigned to the cube when using the standard or org-by-period consolidation algorithm. It reallocates the out-of-the-box eliminations to the relevant UD member. With some quick reconfiguration of the dimensions & names referenced in the rule, it should work with the setup described in the previous section. org-by period in the UD With regards to matrix consolidation, I have previously been asked the following question: “What happens to the eliminations if we change our Profit Centre structure?” Well in our Entity dimension, we have built in tools to handle org-by-period, so that entities can have relationship properties vary by time period. Data is also stored on parent entities which aids in this org-by-period. Within our UD, no such functionality exists, so if I move a member, that member is moved for all history. If I duplicate a member, the values are duplicated (depending on the aggregation weight of course, but keep in mind that this cannot be varied by time!) So how can we approach this is when a Profit Centre needs to change parents one month: Change the main hierarchy – The old view will no longer be visible. The added complexity is that the eliminations for prior periods will occur in the “wrong” place in the UD hierarchy from a historical point of view unless a consolidation is rerun on all those periods. Of course, if you rerun the consolidation on prior periods, then all your results will change (although not at the top level provided nothing else has changed). This implies that the elimination will correctly display the elimination after the change; Historical data will not be kept for the re-consolidated periods. Create alternate hierarchies (e.g. Top_2023, Top_2024 etc.) – New hierarchies can be created, with unique parents that will allow the old hierarchy to be preserved. As with the first option, re-consolidation of prior periods will be required to view historic data in the same format. However, if the data is only required in the new format going forwards then re-consolidation of prior periods can be avoided. Tip: For every alternate hierarchy in which you run your matrix eliminations, the eliminations will be “duplicated”. Therefore, your business logic should allow you to configure, by time period & scenario, which hierarchies are eliminated to ensure only necessary calculations are run. This could be done, for instance, through tags on text fields of the members. Whilst not such a common scenario, it is a consideration worth making during the requirements gathering & design.Question: Is there any security options to limit a user's ability to submit data using excel?
Answer Submitting data via excel will respect the security model set up in OneStream. So consider typical security setup. Also consider using constraints to manage "valid" data intersections. Inputting data via set cell will get tracked in the audit logs, however, clearing this data can be problematic if you wish to clear all out all submitted cells. Rather than using set cell, another option is to set up an excel import which would go through stage and allow for clearing of the data set. There is a pending enhancement request to have an option available to restrict some users from submitting via excel. Source: Office Hours 2020-7-2 - Partner EnablementDoes OneStream have the capability to refresh Cube View connections within Excel workbooks via XFDoc or some other type of functionality? Goal is to schedule the refresh & distribution of an Excel workbook via the Book or other functionality, so that both CVs and XFGetCells can be used. Per our prior testing, when using an XFDoc.Xlsx file type, the XFDoc will refresh XFGetCells when retrieved. However, it does not refresh cube view connections embedded in the Excel XFDoc.
Answer There are numerous Enhancement Requests related to this: PF-385, PF-1309 Source: Office Hours 2020-10-08 Partner EnablementQuestion: In a Cube View heading, how can I include the last day of the month as the time description in a page caption (e.g., display "as at 30 September 2020" instead of "September 2020“)?
Answer Assuming that the date being shown is always the last day of the month and the client is using calendar months, it is possible to use XFBR string rule to call a function to get the number of days in the month. Source: Office Hours 2020-01-17 Partner EnablementIs it possible to load multiple thing planning registers to meet different requirements and, if so, how?
Answer It is possible to create different "versions" of thing planning which could be then used for different requirements (e.g., travel planning and sales planning in the same application). Using the marketplace solution "Marketplace Solution Tools", you can create multiple instances of a marketplace solution which you would then load into the application as normal. Source: Office Hours 2020-12-03 Partner EnablementQuestion: According to the Design and Reference Guide, it is possible to post adjustments using a form. How is this set up?
Answer It is possible to use a data entry form to update the AdjInput member of the Origin dimension. This is achieved by setting the adjustment type on an account-by-account basis. Each account member has an Adjustment Type property which gives control over whether the AdjInput Origin member will be populated using Journals or Data Entry Forms. This property can vary by scenario and time. Source: Office Hours 2021-01-21 - Partner Enablement
Extensibility Series: Dive into Horizontal Extensibility
What is Horizontal Extensibility? Horizontal extensibility is the game-changing technology in OneStream that allows for sharing and inheriting metadata across Scenario Types. With this ability, the business processes can dictate application design instead of vice versa. Within a OneStream cube, we can assign differing base members to each Scenario Type while sharing all relevant parent members. This means, not only can actuals, budgets, forecasts, long-range plans, detailed data sets, and more be contained within the same cube, but their proper level of inputs can be incorporated into the model as well. In legacy corporate finance software landscapes, it is common to see some of the following: Dummy input members to key/load data at different levels in a hierarchy Separate cubes or disparate systems with slight variations of the same hierarchies Multiple versions of the same common reports Duplicate data residing in multiple tools to facilitate the separate needs of each process Inconsistent data tie out points across systems causing skepticism and confusion In OneStream, we can create a single common set of master metadata and through horizontal extensibility, the input levels can follow each distinct process needs. The most common example of this is a budget or forecast that is completed at a more summary level than the actual financial data. In the CPM Blueprint application, you can see this type of extension in the account dimension. In this application and shown in the image below, Net Revenue is the base input member for budgeting and forecasting while actuals are captured at a lower granularity. No longer do we need to maintain separate charts of accounts, create dummy input members, or store input data in separate systems to accomplish this. We don’t even have to maintain separate reports. All these needs from input to reporting can be maintained in a single OneStream cube with commonalities shared. The example above is within the account dimension, but to understand and unlock the full potential of horizontal extensibility, we will look at how this concept can be applied across multiple OneStream dimensions. In the CPM Blueprint application, we can see this use of horizontal extensibility when we look at the LongTerm Scenario Type. The long-range planning process in this application takes place at a more summary level. Like budgeting and forecasting, the account input is at the Net Revenue level. However, in the LongTerm Scenario Type, input into the Geography and Product dimensions (shown below) vary from actual, budget, and forecast. The ability to utilize extensibility in these ways unlocks so many possibilities when designing scenarios, cubes, and global applications. Using horizontal extensibility across Scenario Types allows us to make the user experience more targeted by adding/removing members and entire dimensions from Scenario Types where they are not valid. The examples above focused on processes occurring at different levels in the same hierarchies, but what if an entire dimension is not valid? An example of this in the CPM Blueprint application is the Cash Flow dimension. Below, you can see this dimension assigned to the Actual Scenario Type meaning all Cash Flow members are included and valid in scenarios created with that Scenario Type. However, in the active planning Scenario Types (Budget, Forecast, LongTerm), the “Root” dimension is assigned on the cube properties meaning only the None member is valid. Organizing the cube to be more targeted in this way will reduce confusion among end users and limit potential data unit explosion caused by rogue rules, imported zeros, or other configuration issues. Business rules without proper filtering and removal of zeros can potentially assess and/or write to significantly more intersections than intended. Additionally, configuring the cube properties correctly by assigning the Root dimension for those that are inactive on a Scenario Type will allow you to update and add new dimensions in the future. An example of proper cube dimension assignment is shown in the next section. How Should Horizontal Extensibility be Applied? Applying horizontal extensibility follows three main steps: Planning & Preparation Configuration Cube Assignment Planning & Preparation Every project should begin with a design. Measure twice, cut once. Start with a list of processes and data sets that are planned to be incorporated into OneStream. Next, list out the various reporting dimensions that are used in each. Finally, create a grid with the processes in the columns, the dimensions in the rows, and fill in where they are valid: This chart can start to help us see that actuals and long-range planning should utilize separate Scenario Types (to vary the inclusion/exclusion of entire dimensions), but it does not give us clarity into the differences between the shared dimensions. In this example, accounts are needed for all four of the processes, but we don’t yet know which accounts are needed in each. At this point, I like to compile a complete list of all possible members and hierarchies and go through the exercise of determining where they should be valid like the chart below: After this exercise has been completed for accounts, it should be repeated for each reporting dimension identified to get a full understanding of each data set. Based on the above account-level breakout between the four different processes, we have some decisions to make. The existing process is set up as shown above, but should it be that way in OneStream? Should we create separate account extensions for both budget and forecast? Or do we potentially see the inclusion of Balance Sheet information in the forecast? Are there any initiatives to budget and/or forecast at a different level than currently? Is the business happy with the detail in which the long-range plan is generated? This is the time to avoid lift-and-shift mentality and set the foundation for future growth in the platform. Really discuss the possible extension points in each dimension and come to a decision as a business. This is a key design decision. Configuration After discussing internally with all stakeholders and making decisions around horizontal extensibility, it is time to configure the dimensions and identify any issues with the extension points. After creating a summary dimension at the highest identified level in the member structure, the extended dimensions will utilize the Inherited Dimension property on creation. In the newly created dimension, we will notice the inherited members in gray and we can begin to add in the extended members. In most cases, we recommend extending from base members only. Vertical extensibility will be covered in another article, but when we look at consolidating data between linked cubes, extending from parent members causes the data to not consolidate from sub cube to parent cube. It should be noted that the Entity dimension does not follow this same restriction. In the image below, you can see an account extension that does not follow our recommendation to extend from base members on the left (Example A) and an account extension that does follow our recommendation on the right (Example B). In Example A, two issues have been identified: 1) Five base accounts have been extended from the parent OtherOpExp. You can see this difference via the black & gray text in the account dimension library. accounts 541000, 541100, and 541950 are in the gray text signifying they have been inherited from the parent dimension while the five base accounts from 541200 - 541600 are in the black text signifying they have been created in the currently selected dimension. This is an example of extending from a parent member and it will not consolidate correctly across linked cubes. To resolve this configuration issue, it is common to see one of three solutions: All the base members under OtherOpExp are included in the parent dimension and then inherited into the extended dimension. Input in the parent dimension would be to 541950 - Other or one of the other base members. Only the member OtherOpExp is included in the parent dimension and all child accounts in the extended dimension. Input in the parent dimension would be aggregated to an OtherOpEx level without the breakout of 541000, 541100, & 541950. A new parent member would be created in the summary dimension to extend from. An example of this is shown below. This facilitates the desired split of base members between dimensions but may cause confusion among end users when viewing the hierarchy. The risks of this can be mitigated with proper end user training and documentation. Each of these solutions comes with pros & cons that should be weighed and discussed by the business. 2) The parent accounts TravelEntExp and HRExps are extended from the parent account OpEx. This is an example showing that even though the base members do not have siblings in the inherited dimension, the parents cannot have siblings in the inherited dimension either. All members in the extended dimension, both parent and base, should be extended from a base member. Example B shows the common solution for this configuration with the Travel and HR expense parents included in the parent dimension and all base members in the extended dimension. However, a similar approach with the _EXT parent member could be applied here as well. While discussing these extension points and deliberating whether to move members up/down a dimension or add _EXT parent members to apply extensibility properly, the future state goals should be considered. If the summary dimension you are creating is to facilitate the forecast process today, but that process does not include details around travel and HR expenses, might it in the future? Should you include these members and grow into their use? Is there a roadmap to expand your planning capabilities in these areas? Moving parent members between dimensions later can be accommodated, but moving base members is more difficult. The recommended practice is to extend from a base member, but there are some outlying use cases where extending from parent members is acceptable: If the intent is to not consolidate the data up the linked cube structure. If there is no linkage between cubes and the intent is to limit the members visible to end users. If it is in the entity dimension. As a reminder, the example shown was of the Account Dimensions, but this also applies to Flow Dimensions and User Defined Dimensions. To aide in the process of creating and validating extensible dimensions, the utility “Extensibility Relationship Analysis” on Solution Exchange can help identify potential issues with parent-child relationships. Cube Assignment After we have designed and created our account dimensions in an extensible fashion, we need to assign them to the cube. Properly applying dimensions on the cube settings is critical to unlocking the flexibility that horizontal extensibility provides. Non-data unit dimensions (Account, Flow, UD1 - UD8) should be applied on the specific Scenario Types that are in use. Any unused dimensions on those active Scenario Types should be assigned to the “Root” dimension (Ex. RootUD4Dim in the image below). Assigning the Root dimension instead of (Use Default) allows for that dimensional assignment to be changed a single time later and activated for data input on a go-forward basis. More information on proper cube dimension assignments can be found in the linked article. Recommendations & Considerations Horizontal extensibility is more about providing flexibility and data model integrity and less about managing data unit sizes. Yes, it can shrink the potential data unit size and mitigate the impact of a rogue calculation, but the main focus is on creating a single source of truth by allowing for a single set of master metadata. It is such a powerful driver of adoption to be able to meet the various parts of the business where they operate. When planning for extensibility, one should be forward thinking. Ask questions during design and be mindful of future expansion. Talk to other parts of the business to understand how they operate. What levels do they report or plan at? What is on the roadmap? Is there any defined need for extensibility that can be captured now to facilitate future adoption? Horizontal extensibility should be applied with a purpose. Define the need for extending and get alignment throughout the business. Parent members can be moved between dimensions since there is no data stored in the database at that level, but base members cannot change dimensions. If there is a plan to include certain members or hierarchies in a data set in the near future, you may want to incorporate them now. With proper configuration, horizontal extensibility should then be utilized in Cube Views, Parameters, Business Rules, and more to drive standardization. Below are a few examples of how this can be applied. Member Filter Expansions Applying extensibility and utilizing the provided member filter expansions can allow for the same row/column set to be used across varying Scenario Types in OneStream. If the business has a standard Income Statement where the only difference between processes is the level of detail, it can be created as a single report and shared across Workflows. Two member expansion functions to point out here are .Where() and .Options(). .Where(MemberDim = Value) Example: A#60000.Base.Where(MemberDim = |WFAccountDim|) .Options(Cube = CubeName, ScenarioType = Type, MergeMembersFromReferencedCubes = Boolean) Example 1: Targeting a specific extension point A#19999.Base.Options(Cube = [Total GolfStream], ScenarioType = Actual, MergeMembersFromReferencedCubes = False) Example 2: In combination with XFMemberProperty() to create a more dynamic member formula A#60000.TreeDescendantsInclusive.Options(Cube = |WFCube|, ScenarioType = XFMemberProperty(DimType = Scenario, Member = |WFScenario|, Property = ScenarioType), MergeMembersFromReferencedCubes = False) Calculations The same member expansion functions shown above should be considered when writing calculations across the platform. They can be a tool to make calculations more dynamic and necessary at times to make them more targeted. Another consideration is the function api.Data.ConvertDataBufferExtendedMembers when copying data across extended dimensions. A common need is the ability to copy actual data into a forecast and this function is a performant way to do so while also accounting for extensibility. The ConvertDataBufferExtendedMembers function aggregates the data from extended members in the source data unit to the base level of the target data unit. After aggregating the data in memory, it can then be manipulated and/or stored using the target dimensionality. Additional information on utilizing ConvertDataBufferExtendedMembers can be found in the OneStream Finance Rules and Calculations Handbook and the Tech Talks series on OneStream Navigator. Finally, when applying horizontal extensibility, it is important to keep in mind that it is not just applied to a single hierarchy. The business must be mindful of all alternate hierarchies and incorporate extensibility there as well. It should also be thought through how certain extension points in one dimension could impact its use in another dimension. For example, excluding balance sheet accounts from a forecast could impact the ability to make use of a Cash Flow dimension and corresponding calculations. Conclusion If applied properly, horizontal extensibility can provide amazing benefits. Reduce technical debt by incorporating many fragmented processes Encourage adoption by meeting users where they operate Facilitate reporting and reduce data movement/maintenance Provide a single source of truth The topics outlined in this article should be discussed and utilized during a design to properly apply horizontal extensibility. For additional examples, the CPM Blueprint application can be referenced. Examples in this application include Accounts, Geography, Product, Cost Center, Customer, and Vendor.
