QuickTips: Reverse Engineering Entity (Vertical) Extensibility
This QuickTips video explores how to get a Multi-Cube Application to roll up into a Top-Level Cube. Learn how to add relationships to specific Members, remove relationships, clone Members, and create a dynamic structure that pulls in data from underlying units.Extensibility Series: Riding the Wave of Vertical Extensibility
What is Vertical Extensibility? Vertical or entity/cube extensibility is the transformative feature of OneStream’s Extensible Dimensionality that allows for the collapsing and extension of dimensional detail as you move up and down the entity hierarchy. This feature offers the ability to include and/or exclude detail at different levels in the entity structure, and its proper use has three major benefits: It allows for a single, unified, data model to provide the flexibility required in management reporting while maintaining a single source of truth. It allows for the management of data unit sizing to provide a highly performant end user experience. It provides flexibility and future-proofing by setting the foundation for growth in your business and the OneStream platform. Instead of needing separate, distinct, data sets to meet the various reporting needs of your business, vertical extensibility provides the ability to maintain a single linked data model. The cube structure and data model in OneStream is flexible enough to include the details where they are needed and exclude them where they are not (while still providing visibility through drill down). The main difference between horizontal and vertical extensibility is that horizontal is within the same cube across different Scenario Types while vertical extensibility is across cubes in the same Scenario Type. A common example of vertical extensibility showcasing all three of the benefits mentioned above is that of a Summary cube (sometimes referred to as a “super-cube”) containing a total company entity and various Detail cubes (or “sub-cubes”) for each of the various business units in that company. In the CPM Blueprint application, you can see this type of configuration within the FinRptg linked cube structure as diagramed below. In this example, the cubes and data models are linked through a common entity hierarchy. When we look at the entity dimension above, it’s not immediately apparent that anything is going on there. However, when we compare the cube dimension assignments for Actuals, you can start to see differences in the level of detail. In the FinRptg cube, we can see that a summary level of Accounts, UD1, and UD2 have been assigned. This will allow us to collapse the data unit down to that more summary level used in total company reporting. In the account dimension, for example, the lowest base members in the FinRptg cube would be at the NetRev, COGS, and OpEx level. When data consolidates from an entity in a sub-cube to a parent entity in the FinRptg cube, the records will be collapsed and stored in the higher dimensionality members. The lower-level base entities (ex. BU_100) and BU parent entities (ex. TotBU1) that exist in the FinBU1 and FinBU2 cubes will still have the more detailed granularity, but the TotCORP entity will only be stored in the database at that higher level. Without vertical extensibility, every distinct base intersection from both detailed cubes would also be stored for TotCORP as shown below: Applying vertical extensibility to our design allows us to collapse the data unit as it moves into the summary cube. This allows us to better manage the data unit size and keep the details where they are reported on. Applying this concept to the database records we looked at in the previous example, you can see a considerable reduction in the TotCorp entity data unit size and the amount of data we are storing in the database: Base revenue accounts collapsed into NetRev. Base geographies collapsed into a summary level (NA / EMEA). Base products collapsed into a summary product line (Product Line 1 / Product Line 2). The benefit of this is a direct reduction of the TotCORP entity’s data unit size which will lead to more snappy reporting and improved consolidation times. With the linked cube structure, we can directly drill down from TotCORP at the higher level into either TotBU1 or TotBU2 which sit in the detail cubes whenever we need to explore additional detail. Looking back at the CPM Blueprint application’s cube dimension assignment, we can also see a different application of vertical extensibility in UD3. FinBU1 does not collapse detail into FinRptg (as they both have the ProductSummary dimension assigned) but FinBU2 does. This shows the ability to vary base dimensions in each of the detail cubes. Perhaps different business units across the company use distinct cost centers, separate charts of accounts, or in this case, BU 1 may have a more detailed breakout of products than BU 2. This use of vertical extensibility allows the cubes to be more targeted to the data. This will improve the performance and end user experience by reducing sparseness. With this configuration, we can exclude members that aren’t applicable to BU 2. When designing an application, it’s important to consider future flexibility and expansion both within the business and the OneStream platform. Utilizing vertical extensibility to configure a linked cube design from the start leaves the door open by providing numerous possibilities in the future. By extending the entity dimension down from a summary cube to at least one detailed cube, you are then able to: Expand and collapse detail in separate scenario types as they consolidate up. Utilize vertical extensibility on any new dimensions activated in the future. Link additional detail cubes to the summary cube. Once Workflows have been configured and data has been loaded to a cube, you cannot add a summary cube above it. The foundation has been set and your options around flexibility and managing the data unit size have been truncated. How Should Vertical Extensibility be Applied? Vertical extensibility is the natural next step in the extensibility design process. After aligning on the levels of input each dimension should have, you should also discuss how the detail varies across business segments and what levels of detail are required for reporting. In design I find myself asking some of the following questions: Do all entities follow the same chart of accounts? Do all entities follow a similar breakout of detail (geography, products, cost center, etc.)? Are there common characteristics of entity groupings? How does corporate reporting differ from management and base entity reporting? At a consolidated level, do you need to see the lowest level of detail? The most granular GL accounts? Each individual product? Every project? Could a restructuring cause entities to move between BUs or other groupings? Planning & Preparation In terms of order, I like to start with horizontal extensibility first. Horizontal extensibility is more of a business process mapping into OneStream while vertical extensibility is more of a technical lever to drive performance, maintenance, and flexibility. Because of this, I find horizontal extensibility to be easier to understand, and I like to let the client walk before we run. I suggest having the business define input extensibility across scenario types first and then work into the reporting levels and variances across business units or other groupings. I find this exercise to be a back-and-forth refining process. Look at existing reports, analyze what needs to be produced out of OneStream, and make suggestions on the details in each cube. Solicit feedback and refine the suggestions. Bump up calculations that need to run, ad-hoc reporting, etc. to the suggestions and further refine. And finally, map out the combined vertical & horizontal extensibility in a matrix that the business can visualize and agree upon: Throughout this design process, it is helpful to mockup what the data will look like in OneStream and what will/will not be valid with this data model: Remember that alternate entity hierarchies will also need to be extended from summary into the detail cube(s). All entity structures (ex. Total Legal, Total Management, Total Tax, etc.) need to be thoughtfully planned out to incorporate extensibility and alternate entity parents placed in the proper cube. If possible and time permits, create a quick strawman and load some raw data in a OneStream application so the client can play around with it and visualize in QuickViews and drill downs. This will help further their understanding and learning journey. To reiterate, this is the time to set a healthy foundation for the future. Configuration The configuration of vertical extensibility and the linked cube structure has three main pieces: Configure the entity dimensions. Create the cubes and assign the created entity dimensions. Assign the cube references. Configuring the entity dimensions Continuing with the example in the CPM Blueprint application, we will need to configure three entity dimensions and link them by adding cross-dimensional relationships. First, we create the three entity dimensions and the desired members within each: Next, we need to add relationships to link the structure. Select the FinRptg entity dimension, click on the TotCORP entity, and follow the steps in the image below: Select “Add Relationship for Selected Member” TotCORP should be populated as the Parent Member. Select the ellipsis to the right of Child Member. Change the dimension from FinRptg to FinBU1. Select the TotBU1 entity. Select OK on the “Select Member” popup window. Select OK on the “Add Relationship” popup window. Repeat process for adding the TotBU2 entity relationship. After assigning these relationships, you will see the full linked structure in the FinRptg entity dimension. Note: The entities in black text are members of the FinRptg dimension while the entities in gray text are inherited members. Create the cubes and assign the created entity dimensions The next step is to create the cubes and assign the configured entity dimensions. Continuing with the CPM Blueprint application example, we will need to configure three cubes (FinBU1, FinBU2, & FinRptg) More information on proper cube dimension assignment can be found in the linked article. Assign the cube references The final step is configuring the cube dimensions and assigning the proper cube references to link them. Select the FinRptg cube and navigate to the “Cube References” tab. It will auto-populate based on the entity dimensions linked previously but the cube references also need to be assigned to complete the cube linking. After configuring extensibility and assigning the proper Cube References, the Cube Properties and Data Access settings may also need to be updated: Is Top Level Cube For Workflow should be set to “True” on the summary cube. Workflow suffixes should be set on the summary cube. This section will be grayed out on all linked detail cubes. Calculation settings typically align across the linked cube structure. Business Rules that need to be run in all cubes (ex. Custom Translation) should be assigned in both the summary and the linked detail cubes. Data cell security settings may need to be maintained in all cubes depending on the requirements around them (ex. UD1#200 requires data cell access security and UD1#200 exists in all cubes. Therefore, the data cell access “slice” security must be applied on all cubes in the linked structure). Recommendations & Considerations The Data Unit is a key concept to understand in OneStream. It is important to effectively manage its size to allow for optimal performance while accounting for future growth. Vertical extensibility directly affects the data unit size as you consolidate up the entity hierarchy by allowing you to vary the levels of detail assigned across business units and summary levels of the business. The mindset going into an application design should not be whether or not to use vertical extensibility but rather how it should be applied. There are very few cases where vertical extensibility should not be used because of the benefits outlined in this article. Even in cases where the same dimensions are assigned to both the summary and detailed cubes (therefore not collapsing the data unit at all), I would still utilize a linked cube structure. This foundational setup allows you to use a separate Scenario Type in the future if the data unit size or performance does become an issue as the business grows or new use-cases are brought into OneStream. When deciding how many detail cubes to include in an application design, it is important to weigh many different factors including: Are there data unit size concerns with a single detailed cube? Having a consolidated member across various BUs within a detailed cube could lead to an unmanageable data unit depending on the use of UDs and data volume. Is there a business reason to split into separate detail cubes? Maybe they have different charts of accounts or other dimensional differences (ex. Services vs Manufacturing). How often do entities move between business units? Frequent movements would be complicated between cubes so you may want to contain these in a single detail cube. However, if it’s once every three years or if you have to stretch to remember the last time it happened, the benefits may outweigh any effort to (potentially) move an entity between cubes later. What is shared across business units or other management structures vs what is distinct? If most items are shared, does it make sense to split across cubes? Consider any potential business changes as well if they may push these business units to be more aligned in the future. How do internal controls differ? How should we be thinking about cubes in relation to the security model? Applying extensibility is a balancing act. Too many dimensions and cubes can add maintenance overhead and detract from the end user experience. Too few can slow performance and also detract from the end user experience. There are typically compromises across the business during design. For example, it may make more sense to add a few accounts to a common chart to be able to share across the business, or to add some product lines to a business unit’s dimensionality even if they don’t use them. Instead of splitting these entities out into their own cubes, the complexities may outweigh the benefits. In design, it is important to outline the assumptions and reasoning behind a particular decision. List the options that were considered and the reasons they did not prove to be the best fit. With so many design levers at our disposal, it is helpful to have callbacks to the conversations that were had leading up to the final documented design. In a linked cube structure, reports in OneStream can be created using the parent cube. The concept of Merged Hierarchies allows OneStream to understand the extended entity’s data model from within the parent cube. Use “MergeMembersFromReferencedCubes” to control the extensibility level in reports. Example: A#IncomeStatement.TreeDescendantsInclusive.Options(Cube = |WFCube|, ScenarioType = XFMemberProperty(DimType=Scenario, Member=|WFScenario|, Property=ScenarioType), MergeMembersFromReferencedCubes = False) Finally, as detailed in the horizontal extensibility article, it is important to properly configure account-type dimension extensibility or else it will not consolidate up to the summary cube in a linked cube structure. When reconciling data in a detailed cube, this issue won’t present itself but when the data tries to consolidate up to a parent entity in the summary cube, it doesn’t have anywhere to go if you have not extended from a base member. For the most part, this consideration should apply to all configured dimensions but there are select use cases where it is desirable for the data not to consolidate up to the summary cube and those members not be visible to other business segments. Some examples of this include drivers specific to a business unit, dynamic calculations specific to one segment, and alternate hierarchies specific to one cube. Conclusion Vertical extensibility allows OneStream to remain a single source of truth for both corporate and management reporting. By extending dimensions down the entity hierarchy, organizations can avoid fragmented systems and empower users with the detail they need. Proper application of vertical extensibility provides: Performance benefits Flexibility to assign dimensional detail in a more purposeful way Future-proofing by leaving the door open to additional configuration options Lower maintenance overhead with a single unified data model Extensibility should be a discussion topic in every solution design. Its use is the key to unlocking the full potential of the OneStream platform.Using OneStream to Track Inflation and Related Metrics
Hi everyone, I’m interested in learning how other organizations are leveraging OneStream to track inflation and other account-related metrics. Currently, our approach involves using the Account dimension to explicitly separate inflation impacts for specific expense categories. For example: FOOD_BEV – Food & Beverage Expense INFL_FOOD_BEV – Inflation related to Food & Beverage Expense These inflation rates are externally calculated and loaded via XFD uploads. To support different reporting views, we load the data into three separate accounts: _PER – Monthly _QTR – Quarterly _ANN – Annual We also use a view dynamic account without a suffix, which adjusts based on the selected view. Exploring Alternative Approaches I’m considering whether using a User Defined (UD) dimension might offer more flexibility. For instance: Keep the account as FOOD_BEV Use the UD to switch between metrics like: "Dollars" (actuals/estimate) "Inflation" (loaded via XFD) "Per Guest" (dynamically calculated) "Pricing" (potentially another loaded or calculated metric) This method could streamline the structure and allow for more dynamic reporting, especially if inflation rates are only loaded monthly and calculated for QTD/YTD using weighted averages. Open to Ideas I’d love to hear how others are handling similar use cases. Are you using UD dimensions for this type of analysis? Are there better ways to structure inflation tracking in OneStream? Thanks in advance for sharing your insights! — Jeremy MorganExtensibility Series: An Overview of Extensibility in OneStream
What is Extensibility? The concept of Extensibility in OneStream is the capability to incorporate multiple use cases and future growth with a single foundation. I like to relate this to a dinner table that can expand and add additional table leaves while maintaining the same integrity. The OneStream platform, in tandem with Workflow and Extensible Dimensionality expands on this concept by providing users with multiple ways to extend their platform footprint. When designing an application or planning for expansion to the existing footprint, these concepts are crucial to understand and apply correctly. Extensibility in OneStream is a broad topic and can mean something different to each person in the community so I would like to break our language on this topic down further into the following categories: Horizontal Extensibility Vertical Extensibility Workflow Extensibility Platform Extensibility Horizontal or Scenario extensibility relates to the ability to extend and use different levels of a hierarchy for different business purposes. It also provides the ability to target when and where dimensions need to be included in the data model. Have you ever wanted to input data at a parent level? Through horizontal extensibility, that parent can become a base for input in a different scenario by using the scenario type settings and properly applying Cube Dimension Assignments. What if you have highly detailed metadata that only applies to a specific use case? Horizontal extensibility can help limit the potential intersections that aren’t valid for all the other use cases by assigning it only where it makes sense. Vertical or Entity/Cube extensibility relates to the ability to include/exclude detail at different levels up the entity hierarchy. The Data Unit is a key concept to understand in OneStream and it is important to properly manage its size to allow for optimal performance while accounting for future growth. Vertical extensibility also relates to varying dimensionality across business units. When you report consolidated financials, do you need to see the lowest level of department detail? Each individual product? Every project? The most granular GL accounts? If the answer is no to any of these, vertical extensibility can help. Lower-level entities can still report at a detailed level, but the data can be collapsed to a summary level to facilitate the reporting and increase performance. Does your organization have Business Units with very different operations? Perhaps vertical extensibility can provide the flexibility you need to vary the dimensionality at a detailed level but consolidate to a common summary level. Workflow extensibility relates to the ability to vary the input steps & methods within each process flow. Workflow steps and settings can be adjusted on each scenario type or can be combined if multiple processes follow the same responsibility hierarchy. Workflow extensibility can be configured on each parent cube to tailor the software interface to match the process needs. Is your Actual data collection process more import driven and the Planning process more forms, calculations, and dashboard driven? Workflow extensibility can help split these processes and make them easier to manage from an administration standpoint. Are some data collections imported in a centralized fashion while others have their responsibility distributed to more end users? Entities can only be assigned once in a Workflow hierarchy so to vary the entity signoff responsibilities, Workflow extensibility should be utilized to allow for differing entity assignments. Platform extensibility relates to the ability to vary where data is stored and how it is utilized within the platform. It also includes the ability to have multiple applications within one environment that can talk to each other. OneStream has the unique ability to consume, utilize, and report on data regardless of if it is stored in cubes, relational tables, or even externally. The capabilities in this category are expanding rapidly and should be considered during all solution design activities. Do you plan at a named personnel level? By each individual capital project? It’s important to determine what is necessary in the cube for consolidated reporting versus what can live outside the cube to be reported on more at a base entity level. Through platform extensibility, we can combine cube data with relational data to achieve the optimal balance between performance and reporting needs. Is the process you are designing more operationally driven and your data dimensions more transient in nature? Perhaps none of a specific data set needs to live in a cube, or even OneStream at all. Platform extensibility allows us to utilize entirely relational data, web content, and even external data sets. How should one think about Extensibility? Extensibility is foundational to OneStream. It should be thought of as a tool as essential as the level. Without it, you can probably get the job done and, on the surface, it might look okay as well. But over time, you are likely to discover structural integrity issues. It is probable that what you built may no longer be able to do everything you need it to. We use extensibility to right-size data units. We use extensibility to input at the right level. We use extensibility to fit the business process. We use extensibility to set the foundation for the future. I’ve heard people talk about extensibility in that you are “locked in” to the choices you make now. While there is some truth in that, it should not be thought about as a box, but a key to the future. Applying extensibility opens the door to so many more options in the future. Design the process and use extensibility as the tool to bring it all together. As mentioned in the Guiding Principles article, the importance of designing the process cannot be stressed enough. Don’t look for a tool, look for a problem and use the tools provided. Be forward thinking during design and ask questions to all stakeholders to make sure future functionality is accommodated for. Be sure to understand how the business operates and what is on the roadmap so that the proper foundation can be built. Recommendations I will begin with a disclaimer, there is not a single be-all, end-all way to implement extensibility in OneStream. I have seen applications with no extensibility and ones with too much extensibility. While there is a middle ground that should be found, the applications without extensibility are those that much more commonly have issues. A lack of vertical and platform extensibility tends to lead to performance issues. A lack of horizontal and Workflow extensibility tends to lead to flexibility issues. The applications with too much extensibility less commonly run into performance or flexibility issues, but they do have a higher maintenance burden. This is why, as architects, it is our job to balance performance, usability, and maintenance when thinking about these four types of extensibility. It is our recommendation that extensibility be considered in every single design and that it should be implemented nearly every time. To not use extensibility should be an exception, not the norm. During a solution design, I like to fill out a matrix like the one below to visualize what detail needs to be included where. With this, you can start to shape the Scenario Types, cubes, dimensions, and any platform extensibility. When looking for extensibility configuration examples, look no further than our CPM Blueprint application. This application has example configurations using our leading practices. Looking at UD1 as an example, one can see our common configuration of a “MainUD1” dimension parent to summarize the BU and Cost Center details into a common dimension. This is a concept we apply to all user defined dimensions to facilitate both vertical and horizontal extensibility. To facilitate vertical extensibility, dimensional detail that is not needed in a parent cube can be collapsed by assigning MainUD1. The dimensional detail is then extended from “TotUD1” to expand into the necessary levels of detail for each data collection and reporting need. This allows both “None” and “Top” to be active at all levels in the dimensional hierarchy. Another example of extensibility on display in the CPM Blueprint application is in the cube configuration. Focusing on the financial reporting structure in this application, it follows our recommendation for a base-summary cube relationship between Business Unit and total company reporting. I commonly apply this configuration even if there is only a single child cube and a single parent cube because it opens the door to so many more options in the future: More flexibility to expand child cubes horizontally and plug in different dimensionalities Greater ability to collapse the data unit if its size becomes an issue Further future-proofing as it allows for more platform expansion with the same foundation Finally, this application also has Workflow extensibility on display. On the cube settings, you can see the connection between top level and base cubes. You can also see the Workflow suffixing applied in the CPM Blueprint application. In this example, the Actual Scenario Type has a different process flow and responsibility hierarchy from other data collections, so it has been given its own suffix of “ACT.” Budget and Forecast follow the same process flow and responsibility hierarchy so therefore share a Workflow suffix of “BUDFCST.” This allows each process to have its own configuration and entity assignment. Conclusion Extensibility in OneStream cannot be overlooked. During a solution design, each of the four types of extensibility should be weighed and discussed to see which tool is right for the job: Horizontal Extensibility Vertical Extensibility Workflow Extensibility Platform Extensibility If you conclude that extensibility is not right for you, be absolutely sure. If the choice was up to me, the benefits of future flexibility and performance reliability greatly outweigh the potential need for additional administration overhead and end user training that come with extensibility.
Best practices for handling overlays with varying dimensionality in OneStream
We are designing a planning solution where overlays will be used across accounts with very different levels of dimensionality. For example: Some accounts have minimal dimensionality (straightforward base members for input). Others require navigating through multiple UDs before reaching the base intersection. Ideally, I’d like users to be able to choose their drill-down path until they reach the correct base intersection for data entry. Has anyone implemented a design pattern or approach that balances these differences effectively?BI Viewer / Calculated Fields
Hi all, is there some documentation about BIViever's Calculated Fields ? I cannot find info/examples about: aggr filter joinRule I need to compare values against the value of a specific row, but I'm not able to find a solution. Do someone faced the same challenge ? Do someone have more info about the above mentioned functions ? TIA regards FabioGData Management Export configuration
Hi all, I'm working on a Data Management to "Export Data", launching it from a Dashboard. Everything works fine, since filters (Cube, Entity, Account, Flow, etc) can be parametrized through Parameters. Is there a way to parametrized also the Options such as "Include Zeros", "Include Member Descriptions", etc ? I see that at UI level I can only choose between True and False, but I'd like to manage them from my dashboard. Thanks in Advance FabioGFilter Members by Security
So we have the GetBaseMembers method (or the GetChildren, or the GetDescendant or event the GetMember) that would return instances of the Member class. What is not clear (at least to me) is how I could filter returned members based on security. I could set Nobody in all the member Security's fields of a specific member, but it keeps on being returned by (for instance) the GetBaseMembers. I'm wondering how I could get only visible members, applying a filter that won't return them from the call. Also, it would be nice to know, given a member, if it can be seen, read or written. Two ways to get the same result, for different scopes. Thanks FabioG
Extensibility report usage
HI, OS has report to verify extensibility. I wanted to know based on exp that what happen when report has an error from technical perspective. We have some ragged hierarchy to full fill our req, but report shows an error so trying to understand if consolidation won't work if that is the case. By they are in UD.
