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)How to Run Workspace Assembly from a Data Management Step In 6 Easy Steps
This was originally a question.It took a while to figure out, so to save someone else the hours of trial and error, here is the process I use in detail, start to finish, to run a Workspace-based Data Management step in a Workspace Assembly business rule. Here's a How-to step by step: List of abbreviations used below: DM: Data Management WS: Workspace MU: Maintenance Unit BR: Business Rule AS: Assembly 1. Workspace:Set the NameSpace Prefix - this is what gets filled in at runtime for __WSNameSpacePrefix 2. Assemblies: Note these properties 3. Create the Assembly Business Rule:Create an "Extender" business rule, this is done by right clicking files and selecting "Extensibility Rules" Add some test code, to verify correct configuration, such as BRApi.ErrorLog.LogMessage(si, "Hello from the new extender!"then code the way you would any other extender 4. Create The Data Management Group: Create a DataManagement Group in the same workspace that the Assembly that has your business rule is in 5. Create a Data Management Step - Create a DM step in the new DM Group, reference the rule using the pattern: Workspace.{WorkspacePrefix}.{AssemblyName}.{BusinessRuleName} In this case it would be: Workspace.Current.DMTestAS.DMTestStepExtender Note: we could also use 'DMTestWS' instead of 'Current'.We use Current because the DM Step is in the same WS as the Extender Rule. 7. Done! Test the DM Step with run button and check the error log(System Tab -> Logging -> Error Log),for your test code's output.
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
Can we make consolidation process faster?
Hi, Currently we are not using translation (currency conversion) and I was wondering if we can skip translation process while consolidating or can consolidate only specific accounts (if it allows) to make the process faster? We have a reporting application with huge set of data and need to refresh the data hourly followed by consolidation which is time consuming process. I am using data management out of the box step for consolidation with C#Aggregated. Can I use any other approach to make it faster?Cube | Data Access | Data Cell Access Security/Slice Security by U1#Geography
Data Management Access Security Please share your expertise to set up a Cube's Data Cell Access Security / Slice Security to limit data read access as outlined below: Dimension: U1# - Geography Total_Geography = Child 1 + Child 2 = Total / Top North_America = Child 1 / NA International = Child 2 / Int'l Slice Security: Access Level - Read Data by Geography - in a CV and a QV Total_Geo_Data = Read Total data | Read NA data and Read Intl data NA_Data = Read NA data | no data access = International and Total Intl_Data = Read Int'l data | no data access = North America and Total TY for your practical advice - SMEs.
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
