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.
Preventing YTD true up for Attribute Member?
Hi, I've set up Attribute Members in my application. They are using Text fields in my UD3 dimension and the Attribute Members are in my UD6 dimension. For default Text values, it works great. However, if I want to make a time-dependent change, it does a YTD true-up on any values. For example, let's say I have a member "Member1" with the default value "Smith" and 1,000 per month. If I want the Text value to be "Jones" starting in M4, I make that change in the Text field for that period. When I view the data, the Smith row will have 1,000 for 3 months and then -3,000 in month 4 and the Jones row will have 4,000 in month 4 and 1,000 per month after that. Is there any way to prevent that YTD true up? I just want the amounts to stop for Smith and start for Jones without the true up?
usage of Aggregated member from Consolidation dimension
At the moment the Aggregated member uses the IsConsolidated property to aggregate or not. Do I have any option if I wanted to aggregate a member but not consolidate it? With the introduction of the new member Aggregated, is there going to be a IsAggregated property created or will it always work with the IsConsolidated property? ThanksUsing Variance Explanations and UD8 (Dynamic calc periods) together
Originally posted by Felipe Alcorta Hello: We'd like to incorporate variance explanations into our cube views. If the cube view shows one periodic comparison (e.g. Jun YTD CY vs June YTD PY), we have a corresponding dynamic calc that calculates the variance and we can use the (out of the box) variance explanation from the View dimension in order to have comment/explanation functionality. However, if we have multiple comparison period in the same column template/cube view, we are unable to. If we take the same approach as one periodic comparison, the same comment would appear in both comparison columns since there's nothing to distinguish which periodic comparisons are being explained against. We tried using a placeholder UD8 elements (no formulas) in addition to the variance explanation from View dimension but OneStream says it's an invalid cell. Advice?
Process for closing entities
Hello! What is your process for closing entities? Do you rename them or mark “Is consolidated” as false or do something else to discontinue them? We’re working through creating an official process for closed entities so I was wondering how other folks deal with clearing out RE or other account calcs or otherwise officially close entities. Appreciate any input! Thanks!
Can a Data Mgmt Data Extract step be saved somewhere other than under that user's folder?
When running a Data Mgmt Data Extract step, you can chose where you want the file saved: However, the name you provide always starts under the File Share\Applications\[App Name]\DataManagement\Export\[User Name] folder. So, the above extract would show up as File Share\Applications\MyApp\DataManagement\Export\Phil\20211202\CubeData.csv We need these extracts to be available to users who do not have access to the individual user folders on the File Share (i.e. - they aren't Admins) Is there a way to have the Data Extract saved to a truly user defined location, like Application Database\Documents\Public\DataExtracts? How could this be done? If you run the extract from a business rule are there arguments you can pass into the data management call to override the export path? Or is there a way to programmatically move the file after it's been created? Thanks,Carryforward V#Annotation Inputs
Hello, Wondering if there was a way to carryforward V#Annotation inputs on BalanceRecurring Account Types (or any other Account Types) similar to how a V#Periodic input is carried forward on BalanceRecurring. For example, we have the users selecting a text string in a data cell and would like that data cell to be carried forward when saved. When we click save on this BalanceRecurring Account, the text string doesn't carry forward months because no value is being stored at V#Annotation. V#Annotation: However, we can change the parameter to have the value of a MemberID (Integer) which stores the value at V#Periodic and displays the String. At V#Periodic, this carries forward that string which is our end goal. V#Periodic: Due to the nature of the tables supporting these custom dropdowns, we would prefer to be at V#Annotation and carrying forward text strings that way. Has anyone encountered this or have solution to carryforward V#Annotation inputs?
