Derivative
Derivative rules apply logic to stage data and generate additional records in the Stage environment. The two types of derivative rules are Source and Target.
Source Derivative Rules
Source derivative rules are created to apply logic defined by the inbound source data in the Stage environment. This will create new members as a data record, which are stored in the stage area, to be mapped to a cube or a temporary member.
Target Derivative Rules
Target derivative rules apply logic defined by the post-transformed stage environment data. This will create new members as a data record, which are stored in the stage area. Since these are post-transformation, any records stored as final will not be processed by transformation rules.
Rule Name
A unique name given to a particular derivative rule.
Description
A detailed description used in the Label field of the data imported.
BI Blend Derivative Rules
This is a class of derivative rules specifically designed for use with the external database tables generated from the BI Blend Workflow.
BlendUnit All
BlendUnit Base
BlendUnit Parent
For more information, see the BI Blend Design and Reference Guide.
Derivative Rule Expressions
Below are some examples of the syntax used to write derivative rules. The variation between the use of Source or Target derivative rules is by the definition of the rule. Source derivative rules reference the inbound record members. Target type derivative rules reference the post-transformed records. In the rule expression samples, assume these rules will run in this order presented in the example.
| Rule Expression | Expression Type | Derivative Type | Notes |
|---|---|---|---|
| A#[11*]=Cash | None | Final | Accounts that start with 11 aggregate to a new Account called Cash and stored in Stage. |
| A#[12*]=AR | None | Interim | Accounts that start with 12 aggregate to a new Account called AR, but not stored. |
| A#[1300-000;Cash]=CashNoCalc | None | Interim (Exclude Calc) | The Derivative Account Cash is excluded because the calc is excluded. The CashNoCalc interim Account is created as an aggregate of Account 1300-000, but not stored. |
| A#[1310-000;Cash]=CashIncludeCalc | None | Interim | The two Accounts (1310-000 and Cash) aggregate to equal the new Derivative Account called CashIncludeCalc because the calc is included. Note the use of the semicolon (;) as a list separator. |
The following rules create additional rows in the Stage area when importing data based on logic.
| Rule Expression | Expression Type | Derivative Type | Notes |
|---|---|---|---|
| A#[1000~1999]<<New_:E#[Tex*]=TX | None | Applies to All Types | Ccreates a new row in the Stage for any Account that falls between 1000 and 1999 in the source data, but will add a suffix to it. Account 1010 will create a new row for Account New_1010. The end of the rule syntax shows each Entity name starting with “Tex” will be created as the Entity called TX in these new Stage rows. |
| A#[2000~2999]>>_:Liability:U2#[*]= None:U3[*]=None:U4#[*]=None | None | Applies to All Types | Creates a new row in the Stage for every Account between 2000 and 2999 with a prefix. Account 2300 will come into a new row as 2300_Liability. The rest of the rule means all UD2, UD3 and UD4 Dimension Members will be set as the None Member. |
| A#[3000~3999]@3:E#[Tex*]@1,1 | None | Applies to All Types | Takes the first three digits of each Account between 3000 and 3999 to create new rows in Stage. Each Entity starting with Tex will be shown as “T” since the @1,1 syntax starts at the first position of the string and looks one character to the right. |
Logical Operator
Logical Operator provides the ability to extend a normal mapping rule with VB.Net scripting functionality.
Derivative Expression Types
These are used to perform additional calculations on the transformed Member’s data.
None
This is the default and no changes will be made.
Business Rule
A business rule runs on the resulting Derivative member data. This business rule must have Derivative as its type.
Complex Expression
Write a script here instead of a shared business rule and it will run against the resulting Derivative member’s data.
Multiply
This will multiply the resulting Derivative member’s value by another specified value.
Divide
This will divide the resulting Derivative member’s value by another specified value.
Add
This will add the resulting Derivative member’s value by another specified value.
Subtract
This will subtract a specified value from the resulting Derivative Member’s value.
Create If > x
If the resulting Derivative Member’s value is greater than a specified value, it will be created.
Create If < x
If the resulting Derivative Member’s value is less than a specified value, it will be created.
Derivative Type
Derivative types determine if the resulting Derivative Member will be created, not created, or if the member will be calculated.
Interim
This will not be stored in the Stage area and cannot be mapped to a target member. It can be used within other subsequently run derivative rules.
Interim (Exclude Calc)
This is similar to Interim but will be excluded in other derivative calculations.
Final
This will be stored in the Stage area and available to be mapped to a target member.
Final (Exclude Calc)
This is similar to Final but will be excluded in other derivative calculations.
Check Rule
This is a custom validation rule that uses the same syntax as member filters and can be applied to the source data during the Validation task of the workflow.
Check Rule (Exclude Calc)
This is similar to Check Rule but will be excluded in other derivative calculations.
Order
The Order field allows a value to be assigned to a record which will allow a custom sort order of the mapping table.