Rule Rules - ONLY write rules when necessary.
Rules TIP - ONLY write / use Rules when necessary.
OneStream projects should not be a contest to see who is better at writing complex rules.
If you must write rules please remember:
- Unless specified, the calculation will run on Base Entities AND each Parent Entity. Use these lines almost always in your rules.
- For Base entities only use: If Not api.entity.HasChildren Then
- For Parent entities only use: If api.entity.HasChildren Then
- For ALL entities: api.Data.Calculate(“A#Account1 = A#Account2”)
- There are up to 6 calculation operations in the Consolidation process per Entity.
Conditional statements can be added to formulas to limit which Consolidation calculation processes will do something for this formula.
For Local currency only: If Api.Cons.IsLocalCurrencyforEntity Then
For Translated currency only: If api.Cons.IsForeignCurrencyForEntity Then
For Parent-Child Relationship levels: Api.Cons.IsRelationshipLevel returns True for Consolidation level being OwnerPreAdj, Share, Elimination or OwnerPostAdj.
For best performance, use Remove Functions – Zero and No Data
- Can be used in a Member Formula or a Business Rule.
- RemoveZeros Function removes data cells with cell amount of 0 from data buffer AND removes data cells with cell status of NoData from data buffer.
- RemoveNoData Function ONLY removes data cells with cell status of NoData from data buffer (data cells in a Data Unit).
- When declaring variables in a member formula or business rule, ensure the variables are within the calculation conditions to avoid unnecessary code execution.
TIP 2 - Don’t Stack api.Data.Calculate Functions.
Let's say we need to calculate data for the dimension members below. What shall we do?
- Use a filter; don’t be afraid to create alternate groupings.
- Use member filters to perform the same calculation logic for multiple members.
- Use the hierarchy to drive calculations.
- Reduces the number of times the Data Unit is called into memory and the number of times data is written back to the Cube.
- Avoid referencing a user's workflow POV in stored formulas: Finance rules can and often do run outside the workflow.
Instead of the above - do the list below.
For more information:
OneStream Finance Rules and Calculations Handbook.
Level 2: Financial Model Rules course.
thank you sir, most important thing to remember
