Blog4 #OneStream : The Underlooked asset called ‘Derivative’.

Not actually an asset but a Dimension. Did i say Dimension?? Yes, the 19th Dimension.

Derivative Rules apply logic to Stage Data. The output of Derivative Rules is to generate additional records in the Stage Environment.

I would love to start the blog by defining a USE CASE.

User is running a ‘Import-> Validate -> Load process’ and wants to do a custom validation-

Sum of all records with the same Product code in the transformed table, has to be zero. If it is zero for all product codes, then proceed to LOAD step, else display the ‘errored product code’ and stop the workflow process at VALIDATE step.

Solution

Create a transformation group under Derivative Dimension with type as Target, since i want to apply the validation on the transformed rows.

1. Rule Name- Mention the Product Code name
2. Rule Expression — Use [*] and filter on Product code assuming UD2 here. As a result of the expression defined, This will create an additional row in the stage engine.
3. Logical Operator — Business Rule — We have to create a derivative rule to define our validation.
4. Derivative Type — Check Rule — Additional records created in the stage will only be checked and not loaded.

Derivative Business Rule

Dim variance As Decimal = 0
  If Math.Abs(args.ColumnValue) = variance Then
       'Passed
    Return True   
   Else
       'Failed
    Dim product As String = args.GetSource("UD2#")
    args.CheckRuleMessage = "Fail: Product code (" & product &") doesn't sum to zero.Sum = " & int(args.ColumnValue) 
    Return False
   End If

Result

Failing the validation

PS — There is a drawback to the solution. Since the user has 300+ Product codes, We have to create 300 derivative rules and update them manually which is tiresome.

Bulk updating transformation rule ( Using trx or XML) is not startight forward in Onestream. In my next blog , I will introduce you to a new tool To be continued……