Issue with Excel Data Extraction via Drilldown
Hello everyone, I hope you are doing well! I’m having an issue with an Excel extraction of my data rows through drilldown. I have a French profile and my Excel is set to French, so when I have separators with spaces, it extracts the amounts in a strange format. Have you encountered this problem before? Thank you very much!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 MorganManagement vs Stat reporting - best practice approach?
Hello, We want to review both our Management and Stat reporting on a monthly basis. The 2 approaches we are considering using are: employ an Audit / Nature UD to build the separate views by adjustments layered over the local GAAP import. or use a separate Scenario for each view with the Local GAAP data being fed into each scenario. One of the main differences between the two views would be pre-acquisition TBs for newly acquired companies being included in the management view for comparative purposes. Would be Interested to hear anyone's feedback or suggestions - thanks!
Invalid Cube Name Error During Data Load
My colleagues in OneStream, have you seen this error before? Since this is the first time I’ve come across it in a standard load scenario with everything else validating, I thought I’d share here to compare notes. Any insights on what could be “behind” this error in practice would be really helpful for building a knowledge base around less common OneStream load errors. While running a data load in OneStream, I encountered an "Invalid Cube Name" error. What made this surprising is that all the usual suspects checked out fine: Workflow setup was validated. Data sources were reviewed. Transformation rules were checked. Rules on members who call cubes were also confirmed. Cube mappings looked correct. Despite all of these checks, the error still occurred. This message is unusual because it doesn’t typically appear if the metadata, cube structure, or mappings are aligned. In most cases, it points to something deeper in how OneStream is resolving the cube context for the workflow profile or the entity being processed. Have you seen this error before on OneStream? ( Invalid Cube Name Error During Data Load) Cheers
Dashboard component/business rule
Is it possible to do a live character count using a text box and label component inside of a business rule that counts characters as they are being typed into a text box component that is used in a dashboard? The contents of this text are written to a relational table but as for the character count, I want to do that live BEFORE I write the contents to a relational table. This is on an 8.4 application.Stored Calc Issue
Hi Team, I am trying to build a Stored calc. My account type is "Revenue" & Formula type is "Formula Pass 11". Here, if i give full combination (All Dimensions) data is sending from Source to designation. and one more thing if i give any variables Data is not sending from source to Destination. can you please help us how to make the calc as dynamic since i am giving all combinations.Confirmation Rules - Time Frequency
Hi there, We've developed rules that works properly in actuals with monthly frequency. Is there a way to use the same rules in other frequencies? I tried to link them to a budget scenario with yearly tracking frequency (Scenario property), but they are applied at the end of the year (for instance 2022) instead of each month. This behavior is kept even after changing to Monthly frequency, or "Member Filter" using "T#|WFYear|.Base". Is there a filter on the time from the WF itself? Ideally, we'd like to keep the actuals conf rules as they are. Thank you for the help. Regards
DM Step Issue with Exporting Data with View Filters
Hi team, I'm trying to export data using a Data Management step with view filters. When I specify the view as V#Periodic,V#YTD, only V#Periodic data records is being extracted. If I reverse the order to V#YTD,V#Periodic, then only V#YTD data records get extracted. Has anyone faced this issue or knows how to correctly extract data with View dimension filters? Thanks in advance! 🙂
Meta Data UD2 DIM
We have some group of base customers under different parents with text property, and we are planning to block input to those customers in all the input forms if we use dimension property Allow input 'false' then it will block input but after that update during close FPA team should do adjustments for those Customers. Can we do it with irrespective of that dimension property false update. Is there any option that without updating input forms (CUBE VIEWS) with customer (UD2) parameter or with the help of business rule can we override that dimension property Allow input 'True' by running DM Step for certain customers when we give them in DM JOB and revert again to false after inputs through DM step. Please suggest your solutions.
