Rules about Rules: Tips and Calculations Optimization

[cbriscoe]

The OneStream platform is based on the Microsoft .NET Framework, as is the OneStream Business Rules Engine.  VB.NET has become the logical choice for Business Rule syntax, and is also a popular programming language in use today. It is also possible to write Business Rules in C# on OneStream Platform. 

NOTE: Please write C# and or VB.NET Rules only when necessary.

OneStream projects should not be a contest to see who is better at writing complex rules.

OneStream consultants should write rules only when necessary, not because they can.  

When should these types of calculations be used in rule writing?

Dynamic Calculations

“On-the-fly” calculation runs when opening a Form, Report, Cube Views, or Quick Views that refer to each data cell.

Stored Calculations

• Calculated Members via Formulas.
• Data is calculated and then stored in an Entity in the Application database.
• Member formulas can be used to run several calculations at the same time, and when used in this way they will run faster than business rules.

• Business Rules Attached to Cube
• Various types of calculations or other logic run when a Data Unit in a Cube is calculated, translated, or consolidated
• Custom Calculated Data.

Dynamic Calculations vs Stored Formulas

Tip 2:  Consultants should discuss rule types before rule writing.

Application performance depends largely on how they are designed. 

To the table above, add that the results of Dynamic Calculations cannot be used as inputs to another calculation.

TIP 3:  Consultants should consider using Data Buffers and Data Units before embarking on the build phase.

Get these rules wrong and cry (slow application) 

, or get them correct and smile (good performing application).

Ensure you understand the customer's Data Units and Data Buffers? This is where the project Subject Matter Expert helps.  A Data Buffer represents all the stored data cells in a Data Unit.

• Origin
• Accounts
• Intercompany
• Flows
• UD1
• UD2
• UD3
• UD4- UD8

Data Buffer is the term used to refer to a collection of data cells within a Data Unit.

• A subset of data in a Data Unit.
• It can drive filtering in a calculated function.

TIP 4:  Data Units.

A data unit is used to load, clear, calculate, store, and lock data.  OneStream provides the following Data Units.  Understanding the Data Unit and how data is stored in the Cube is crucial to writing Calculations.

Members of the Level 1 Data Units or Cube Data Units are essential to remember.

1. Cube
2. Entity
3. Parent
4. Consolidation
5. Scenario
6. Time

TIP 5: Stored Calculations and Consolidations start with Data Units.

• Data Units drive the Consolidate, Translate, and Calculate processes.
• The Data Units to be processed are determined by the context of the parent and child entities being calculated, translated, and consolidated.
• Each Data Unit includes a single: 
• Cube (e.g. GolfStream)
• Entity (e.g. Houston Heights)
• Parent (e.g. Houston)
• Consolidation member (e.g., Local)
• Scenario (e.g. Actual)
• Time (e.g.: 2011M2)

Data will be stored and generated dynamically from rules within your application. It is important to store only what is needed (e.g., Seeding Data).

TIP 6: We write and run rules to calculate critical statements such as:

Cashflow, seeding data, driver-based calculations, customer KPI calculations, etc.

Consultants should discuss Member Formulas and Finance Business Rules in report design meetings.

Finance Business Rules vs Member Formulas. Why do we choose one method over another?

Finance Business Rules

Member Formulas

On the Business Rules Page Used to write more complex calculations that span multiple Dimension Members They have additional capabilities that make writing seeding, cash flow, and driver-based calculations easier. Do not run automatically and typically need to be assigned to the Cube in the Business Rules section. Some types of Finance Business Rules can be run through a Data Management Job.

In the individual Dimension Members within the Dimension Library Used to write easier-to-digest calculations for a specific Dimension Member Typically, they are more performant and employed for things like retained earnings, CTA, movement calculations, and other items. It can be written as a dynamic calculation that does not store data. Must have a FormulaPass number assigned to them to run in their appropriate order.

The advantage of using Business Rules over Member Formulas is the ability to control behavior through Finance Function Types, which are unavailable in Member Formulas. E.g., Custom Calculate functions cannot be executed through Member Formulas.

Member Formulas provide a maintenance-friendly natural structure to your calculation code. If you want to edit the Calculation Script for the Receivable Account, you can quickly find it in the Dimension Library.

Which is Better – Business Rule or Member Formula? The bottom line is that – in a given implementation – you will have a mix of Business Rules and Member Formulas, and it is primarily up to the Consultants to decide which method is deployed.

TIP 7:  Remember that calculations are run from a Cube View Grid, through a Workflow Process task, and a Data Management Sequence.

The Data Unit Calculation Sequence (DUCS) is a series of steps that occurs each time a Calculation or Consolidation is run.

DUCS Sequence:

1. Clear previously calculated data (based on Storage Types)
2. Run Hybrid Source Data Binding Types (Share Data / Copy Input Data from Source)
3. Run Scenario Formula
4. Perform reverse translations by calculating Flow Members from other Alternate Currency Input Flow Members
5. Business Rules 1 & 2 (as assigned to Cube)
6. Formula Passes 1 through 4 (First Account formulas, then Flow formulas, then UD1 formulas, then UD2 – UD8)
7. Business Rules 3 & 4
8. Formula Passes 5 through 8
9. Business Rules 5 & 6
10. Formula Passes 9 through 12
11. Business Rules 7 & 8
12. Formula Passes 13 through 16

TIP 8:

More Rules about Rules.

Stored Calculations – API.Data.Calculate

• API.Data.Calculate(“C = A + B”)
• Stored calculation is an important concept—any dimension not specified on the left and right sides of the calculate function will have ALL members processed as part of the calculation, negatively impacting application Performance.

Here is an example of not being at the crime scene and going to jail all the same. If a UD1 member is not included in the rule formula, all members of the UD1 dimension will be processed. This is slowing down your application.

What are the Data Unit and Account-type Dimensions rules for rules?

     If Api.POV.Account.Name = …

• Only Data Unit dimensions are available for If conditions. Note that the data unit computes calculations. Hence, only data unit dimensions are known at calculation time.
• API.Data.Calculate runs on all “Account-type” dimensions. This is why being specific in the formula is important.
• Account
• Flow
• Origin
• IC
• UD1…UD8
• Cannot use If conditions on account type dimensions such as

Types of API.Data.Calculate

• Formula As String with Durable Calculated Data
• api.Data.Calculate(formula, IsDurableCalculatedData)
• Formula As String with Eval Filtering Logic
• api.Data.Calculate(formula, onEvalDataBuffer, userState)
• Formula As String with Account Related Dimension Filtering, Eval Filtering, and Durable Calculated Data
• api.Data.Calculate(formula, accountFilter, flowFilter, originFilter, icFilter, ud1Filter, ud2Filter, ud3Filter, ud4Filter, ud5Filter, Ud6Filter, ud7Filter, ud8Filter, onEvalDataBuffer, userState, IsDurableCalculatedData).
• Go with Filtering for performance efficiencies –

Remember, using Stored Calculations is sometimes correct.

Some Store Calculations Benefits are:

• Flexibility and speed on data entry
• Seeding data for keeping.
• Planning projects & dashboard forms
• Other custom benefits exist.

TIP 9: Improve Cube View performance with Dynamic Calculations: API.Data.GetDataCell. 

• Return API.Data.GetDataCell(“A+B”)
• Example: Return API.Data.GetDataCell(“A#15000+A#15000”)
• Common Uses of API.Data.GetDataCell
• Ratios and simple math for members just for row-based reporting
• Calculated reporting columns (e.g., Scenario variance, % of another Account)
• Dynamic Calc members can refer to other Dynamic Calc members in their Formulas.
• Look up cell values in another Account, Scenario, or Cube in stored calculation formulas.
• Important! Remember that if your Cube View shows hundreds of instances of a Dynamic Calc cell, then the Dynamic Calc formula will be executed hundreds of times. 
  • Avoid using SQL to lookup data in a solution table within a Dynamic Calc, because this query would be repeated hundreds of times. Instead, query the table once, and store the results in the Globals variable for fast retrieval in subsequent calculations. This is outside the topic of this article.

TIP 10: ALWAYS comment out BRAPI.ErrorLog.LogMessage(si, “Here”)!

• Careful when using in loops – it can blow up the error log.
• ALWAYS comment out and delete all logs for the user when done.
• Comment out all Rules not needed in the application.
• Many consultants put LogMessage statements inside a loop. This will slow your application performance down to a crawl. Instead, build up your message using a StringBuilder , and using the AppendLine method inside the loop. After the loop , write one single LogMessage , with the contents of the StringBuilder passed into the detail (that is the 2^nd argument in LogMessage).
  BRApi.ErrorLog.LogMessage(si, “Items found”,  myMessages.ToString() )

Tip 11:  Avoid embedding Decimal Variables inside a Calculate statement.

Nearly all of us have done something like this at some time in our career:

Dim dTaxRate As Decimal = 0.123
api.Data.Calculate("A#[Result] = A#[Source] * " & taxRate)

This will break when executed by a user with a non-English culture code setting.
Usually this will happen after go-live, when it is too late and the damage has already been done, because nobody tested the application with a non-English culture code setting.

Correct way to use decimals inside calculations:

Some consultants would point out that using XFToStringForFormula() would make your calculation culture-safe. However, this has the side effect of truncating your decimal amount to 9 decimal places, and the added overhead of converting decimals to strings.

For best results, use the FormulaVariables  method, as in the following example:

Dim dTaxRate As Decimal = 0.123
api.Data.FormulaVariables.SetDecimalVariable("taxRate", dTaxRate)
api.Data.Calculate("A#[Result] = A#[Source] * $taxRate")

This way you ensure no culture issues, and no loss of accuracy since you don’t have to convert decimals to strings.

Tip 14: When working with solution tables with date columns (such as a People Planning Register), never assume a particular date format.
We have seen code that assumes dates are presented in the form ‘mm/dd/yyyy’ and extracts characters in a particular position, assuming that will always be the day or month. Many consultants were unaware that the user’s culture setting affects the interpretation of decimals and dates on the server too!

This is particularly common when embedding filter parameters into SQL queries.
This practice even has an industry-wide name: SQL Command Injection.

For example:

Instead, the offending lines should be changed , to separate the parameter values from the query string.

This way your business rule is not vulnerable to SQL Command Injection (security concern), and the added benefit is that your rules become globally safe for various users’ culture codes and regional settings.

Key Points to remember:

1. Always keep application performance in mind
2. Understand when, where, and how a formula or business rule runs.
3. Consider several possible data cells when writing formulas.
4. Always use Remove Functions to prevent calculations from running on NoData and/or zero cells.
5. Always Consider different methods of calculating (custom calc vs stored).
6. Always remember OneStream rule-writing helpers
7. Before going live, always test your application using a non-English culture code on the user setting (such as FR, DE) to rule out the genuine risk that your calculations suddenly break as soon as someone from outside the US starts using your application. It does not matter that your servers may be based in the US or whether it’s a US customer.

Pay particular attention to the correct interpretation of decimals, especially where you may have embedded decimal variables inside Calculate Statements (not recommended). Check also for correct interpretations of dates if using solution tables: Remember that most countries do not use mm/dd/yyy format.

We have seen many emergencies being raised on production applications that have worked fine until a user logs in from France or Germany for example. So we cannot stress this enough.

Conclusion
Understanding how data is stored and organized in the Cube is vital to writing efficient and effective Calculations and Business Rules.  Mastering OneStream's Data Buffer Math: Where One Equation Rules the Financial Galaxy! Say Goodbye to Intersection Chaos and Hello to Data Wizardry with api.Data.Calculate. Because in the World of Corporate Finance, Performance is King and Precision Reigns Supreme!

For more information:

1. OneStream Finance Rules and Calculations Handbook. 
2. Level 2: Financial Model Rules course.
3. Navigator micro courses and QuickTips on Rules.