Calculate if No Data
Have you ever wondered how the Calculate if No Data setting works? (I did, and Keith Berry helped me out) “Calculate if No Data = False” prevents the execution of rules on “OK,NA” (no data) entity. However, the Calc Status gets changed from “OK,NA” to “OK”, and it impacts the parent (“CN”). The “False” option is to avoid populating unused entities with rules. Calculate on “OK,NA” Force Calculate on “OK,NA” Calculate if No Data = True No rule execution No change in Calc Status Rule is run Calc Status changes to “OK” Parent becomes “CN” Calculate if No Data = False No rule execution No change in Calc Status No rule execution Calc Status changes to “OK” Parent becomes “CN”Translating or Converting Units or Volumes
Often global companies may report a metric like units sold or volumes in a stat account. But that unit of measure may vary by region or country. Below is one option for handling the conversion of stat accounts like units sold or volumes. For example: Metric Ton to US Ton Kilograms to Pounds Liters to Gallons Etc Here are some high level ways to tackle this in an application. Considerations: Entities are tagged with currency already Normal Revenue/Expense account types will naturally get “AverageRate” table PVA method (all else equal) Normal Assets/Liability account types will naturally get “ClosingRate” table VAL method (all else equal) You need to identify which accounts (aka UNITSSOLD) need a translation rate table that differs You need to identify which entities are sending which volumes to drive translation to use different rate table Your conversion rates do not vary over time so you really just need to store these rates once in the application for all time periods: KilogramRate (store conversion rate to go from kilograms to pounds) MetricRate (store conversion rate to go from metric tons to US tons) LiterRate (store conversion rate to go from liters to gallons There are a few different options for storing these conversion rates: LOOKUP table under transformation rules section Preferred method Stores once in app that will come out with app extract file Can be referenced in TransformText api to grab rates during conversion Custom table in the app Stored once in app but must be recreated with each app copy or new app FX rate tables pertaining to these elements Leverages standard place for rate but since these rates do not vary over time, you would have to load these conversion rates to all time periods into eternity Account that is balance recurring that stores these conversions in E#None:C#None to be used across all entities Leverages standard place but again, having to store these rates by time period into eternity Does not come out with app extract since it is data Set up accounts as follows: Tag various volume or units accounts as type “Revenue” so that they will get translation Use Text# on account to drive the rate table being used so that it does not use “AverageRate” but instead goes to one of the 3 new rate tables that you set up as: LOOKUP table (preferred method), Custom table, FX Rate table or Account Set up entity as follows: Use Text# on entity to differentiate what unit of measure is being sent This may not be needed if you can direct into different accounts upon load Set up cube as follows: Translation Algorithm Type = Standard Using Business Rule for FX Rates In this Finance BR just isolate list of accounts that need other than standard translation, e.g. focus on handful of volume accounts that should use one of the new unit conversion FX rate tables All else can be standard translation (all else equal)
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? Thanks
Cube Properties Best Practices!
Given a situation, determine the correct properties for the Cube. Certification Exam. Workflow The most common setting Is Top Level Cube for Workflow (Main Cube) = True Project requirements dictate Suffixes for varying Workflow by Scenario Type Assigning Suffix by Scenario Type allows the same Entity to be assigned to different Workflows in different Workflow Cube Root by Scenario Type The best practice is to configure a suffix early on for common Scenario Types regardless of whether they get used. NOTE: These settings cannot be updated once data is loaded to a specific Scenario Type. Leaving blank is defining the suffix as well. Calculation Consolidation and Translation Algorithm Type Most common setting = Standard Settings based on design decisions on how consolidation and translation to be customized. Recommendation – Only change from Standard to Custom on sporadic cases. Elimination and Translation logic should be done either on the Member using O#Elimination or C#Elimination or using Translate as part of the Finance Business Rule and assigning it to Cube. Use Standard Using Business Rules for FX Rates for Translation Algorithm Type in the above case. Calculation Properties: Business Rules Cube Properties: FX Rates Cube Properties: Set Default Currency FX Rates for the Cube. The most common settings for the Cube are shown in the screenshot. Default Currency is dependent on the currency of the application. NOTE: Triangulation is dependent on default currency Best Practice – Control the FX Rates by Scenario. Deep dive of FX Rates discussed during setting Scenario settings. For more - OneStream Certified Professional (OCP) – Lead Architect Certification Prep Webinar in Navigator.Performance Decisions - Dynamic Vs. Stored Calculations
💎 OneStream consultants and administrators must make the correct decisions during the design phase of YOUR project. Top on the list: Deciding which one (Dynamic or stored Calculations) to use is a fundamental consideration of a project. Dynamic Calculations are an in-memory calculation that runs on demand (when necessary). Stored Calculations are calculations that run as part of the Data Unit Calculation Sequence (DUCS). All alternatives should be considered before storing calculated values in the application. Dynamic Calculations do not store a value in the cell. Instead, a numerical value calculates and displays when the Member is referenced. Stored Calculations result in data records and data cells being stored in a database table. For further details: OneStream Foundation Handbook and Implementing OneStream course. Exam QUESTION: What are the three ways to run Calculations? Cube View cells Workflow Process task Data Management Jobs
