PUBLISHED DECEMBER 11, 2020
Derivative Rules - A Step Towards Data Quality
By Shreyas Deshmukh
Business Analyst, B-EYE APAC
In OneStream, all active dimensions require transformation rules. These are used to ensure that the data goes in the required combinations in the OneStream Solution.
Transformation rules (one to one, composite, range, list & mask), are applied to transform and map this incoming source data to dimension members in the application (target data). There may also be a need to derive data from the existing source data set based on certain specific logics. This is where the derivative transformation rules come handy.
Derivative rules can be applied to source as well as target data.
Types of Derivative Rules
Source Derivative Rules are applied to the inbound source data in the stage environment. The resultant new members are created as data records stored in the stage area. They can then be mapped to cube or a temporary member.
Target Derivative Rules are applied to post-transformed Stage data. This will also create new members as data records and are stored in Stage area. As it is defined on post-transformed data, any record stored as final will not be processed by transformation rule.
The use of Source or Target type Derivative rules must be specified in the rule definition.
While defining a derivative rule, the following three settings are important:
● Rule expression
● Expression type
● Derivative type
A. Rule Expression
Rule Expression enables you to directly add a rule by using member filters and logical expressions. Aggregating accounts based on logics, adding prefixes or suffixes to dimension members, picking up specific characters from dimension members are some of the examples.
For e.g.: A#[10*]=Expense
Accounts that begin with 10 will be aggregated to a new Account called Expense.
B. Derivative Expression Types
Additional calculations can be performed on the data transformed or filtered by the rule expression i.e. resulting derivative member data. Below are the different expression types:
None
Default and no changes made
Basic Mathematical Expressions
Add, Subtract, Divide and Multiply by another specified value
Create If < X & Create If > X
Compare the resulting Derivative Member’s value with a specified value
Complex Expression
Write a script specific to the derivative rule instead of a shared business rule
Business Rule
Business Rule having type as Derivative
C. Derivative Type
Derivative type determines what happens with the resulting derivative member – whether it will be stored, not stored, or if the Member will be calculated. Here is a brief overview of the different derivative types.
Interim
-
No Stage storage
-
Cannot map to target member
-
Can be used in subsequentlt run Derivative Rules
Interim
(Exclude calc)
-
Similar to interim
-
But it will exclude other derivative calculated values
Final
-
Stored in stage and available for mapping
FInal
(Exclude calc)
-
Similar to final
-
But it will exclude other derivative calculated values
Check Rule
-
A custom validation rule which uses the similar syntax as of member filters
-
Applied to source data for validation in workflow
Check Rule
(Exclude calc)
-
Similar to Check Rule
-
But it will exclude other derivative calculated values
Derivative Transformation Rule Examples
To filter members and apply logical operators to stage data
To apply check rules on a data set
B
To retrieve a value from the cube and run calculations in stage
C
To retrieve a value from the transformation cache and use it as input to a calculation
D
A. To Filter Members And Apply Logical Operators To Stage Data:
A multiplier needs to be applied to a certain data set creating a new data set; the original one remaining untouched.
In the above example, data set of Entities containing ‘Houston’ with UD3 Midwest will be multiplied by 0.25 and stored against UD3 Michigan. Source data will remain and additional lines of derived data per above rule expression will be created. Derivative type Final stores the data in stage for further transformation.
B. To Apply Check Rules On A Data Set:
A check rule is required to validate the sum of Trial Balance amounts is zero.
In the above example, Total of Trial balance has been calculated and stored in Account ‘CheckSum’. This rule will set the value of account ‘CheckSum’ to derived account ‘Check Rule’ where U2 = None, U3 = None, U4 = None, Attribute 1 = ALL. The complex expression compares ‘CheckRule’ with zero. Derivative type Check Rule is used in validation process in the workflow.
C. To Retrieve A Value From The Cube For Use In A Calculation In The Stage:
We can also calculate the values using Business Rule to get a particular result using cube data.
It will lookup values mentioned in the member filter in the rule expression and execute the Business Rule
In the above example, it will filter the accounts beginning with 4110 and create a new row by adding a suffix to it. The business rule to retrieve the values from the cube for use in the calculations will be executed. Here Derivative type Final (Exclude Calc) stores the data to the stage but will ignore the other derivative calculations.
D. To Retrieve A Value From The Transformation Cache And Use It As Input To A Calculation:
Our stage tables contain accounts of nature Bad Debts and Receivables. We can derive Bad Debt as a ratio of Receivables by temporary storage in derived accounts to be further used in a business rule to calculate the ratio.
Derive Retained Earnings for the Period from Trial Balance
Taking from what we learned above, to derive retained earnings for the period from the trial balance, the rule expression below will apply.
A#[*]=NetResult:F#[*]=None:I#[*]=None:U1#[*]=Subledger:U2#[*]=None:U3#[*]=None:U4#[*]=None:U5#[None]=None:A1#[Profit & Loss]
Here it will aggregate all P&L Accounts into a new account NetResult where Flow, IC, U2, U3, U4, U5 are set to None. U1 is set to Subledger and Attribute1 is set to Profit & Loss (To identify P&L accounts).
This expression can be used to fetch the retained earnings from the current stage data at time of Trial Balance import.
Round UP
We have found derivative rules quite useful in deriving additional data with almost no manual changes done on the source files.
This ensures integrity with detailed audit trail and drill back capability. Derivative rules are definitely a small, but an important step in the run for data quality and confidence.
- Shreyas
