Derivative

Derivative rules apply logic to stage data and generate additional records in the Stage environment. Derivative rule types include 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

NOTE: The formula reference in BI Blend for the Log P1 function is not calculated for values <=1 and returns null in that case.

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	Creates 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 Operators

Logical Operators 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

A derivative record is created if the value is greater than a specified threshold.

Create If < x

A derivative record is created if the value is less than a specified threshold.

Returns the past value (lag) per the number of years set in the Math Value field of the rule. Math Value = 1 by default. The lag operator looks at the first date in the set, then returns the lag value for the number of years specified. In the data set shown below, the value 12 would be returned if the Lag (Years) value is set to 1:

Lag (Months)

Returns the past value (lag) per the number of months set in the Math Value field of the rule. Math Value = 1 by default. The lag operator looks at the first date in the set, then returns the lag value for the number of months specified. In the data set shown below, the value 9.99 would be returned if the Lag (Months) value is set to 1:

Lag (Days)

The Lag (Days) logical operator returns the past value (lag) per the number of days set in the Math Value field of the rule. The data set is first sorted in descending order by the Created date. The lag operator looks at the first date in the set, then returns the lag value for the number of days specified. In the data set shown below, the value 23.39486017 would be returned if the Lag (Days) value is set to 2:

Lag (Hours)

Returns the past value (lag) per the number of hours set in the Math Value field of the rule. The data set is first sorted in descending order by the Created date/time. The lag operator looks at the first date/time in the set, then returns the lag value for the number of hours specified. In the data set shown below, the value 43.53177343 would be returned if the Lag (Hours) value is set to 2:

Lag (Minutes)

Returns the past value (lag) per the number of minutes set in the Math Value field of the rule. The data set is first sorted in descending order by the Created date/time. The lag operator looks at the first date/time in the set, then returns the lag value for the number of minutes specified. In the data set shown below, the value 21.12 would be returned if the Lag (Minutes) value is set to 4:

Lag (Seconds)

Returns the past value (lag) per the number of seconds set in the Math Value field of the rule. The data set is first sorted in descending order by the Created date/time. The lag operator looks at the first date/time in the set, then returns the lag value for the number of seconds specified. In the data set shown below, the value 17.77 would be returned if the Lag (Seconds) value is set to 3600 (60 minutes):

Lag Change (Seconds)

Returns the difference between the latest value and a past value (lagged value), per the number of seconds set in the Math Value field of the rule. The data set is sorted in descending order by the Created date/time. The lag change operator looks at the first date/time in the set, then looks at the lag value based on the number of seconds set in the rule. The difference between the latest value and the lag value is returned. In the data set shown below, the value -2.25 would be returned if the Lag Change (Seconds) value is set to 3600 (60 minutes):

Lag Change (Minutes)

Returns the difference between the latest value and a past value (lagged value), per the number of minutes set in the Math Value field of the rule. The data set is sorted in descending order by the Created date/time. The lag change operator looks at the first date/time in the set, then looks at the lag value based on the number of minutes set in the rule. The difference between the latest value and the lag value is returned. In the data set shown below, the value -5.60 would be returned if the Lag Change (Minutes) value is set to 4:

Lag Change (Days)

Returns the difference between the latest value and a past value (lagged value), per the number of days set in the Math Value field of the rule. The data set is sorted in descending order by the Created date/time. The lag change operator looks at the first date/time in the set, then looks at the lag value based on the number of days set in the rule. The difference between the latest value and the lag value is returned. In the data set shown below, the value 8.85 would be returned if the Lag Change (Days) value is set to 3:

Lag Change (Months)

Returns the difference between the latest value and a past value (lagged value), per the number of months set in the Math Value field of the rule. The data set is sorted in descending order by the Created date/time. The lag change operator looks at the first date/time in the set, then looks at the lag value based on the number of months set in the rule. The difference between the latest value and the lag value is returned. In the data set shown below, the value -3.37686333 would be returned if the Lag Change (Months) value is set to 1:

Lag Change (Years)

Returns the difference between the latest value and a past value (lagged value), per the number of years set in the Math Value field of the rule. The data set is sorted in descending order by the Created date/time. The lag change operator looks at the first date/time in the set, then looks at the lag value based on the number of years set in the rule. The difference between the latest value and the lag value is returned. In the data set shown below, the value 3.37686333 would be returned if the Lag Change (Years) value is set to 1:

Lag Change (Step Back)

First, sorts a data set in descending order by the Created date. Then, it “Steps Back” from the first record in the data set and selects the record based on the number of positions back specified in the Math Value. The first record in the data set is position zero. In the data set below, the difference between the 1st record and the 6th record is returned:

Derivative Rule Expressions

Derivative rule expressions determine how derived records are grouped. Groupings occur on an attribute, unless the rule expression is explicitly set to “None.”

In the following example, notice that attribute A4 is not specified in the rule. As a result, groupings occur based on A4. It corresponds to the Responsibility column (assigned to column in load file) in the BI Blend tables.

Example: A#[TicketCost]=LagDays2_GroupbyResponsibility:A1#[]=None:A2#[]=None:xBDT#[2022-01-01 00;00;00~2022-12-31 23;59;59]!M

When a BI Blend file is loaded to the workflow, the calculation based on the logical operator is performed on each grouping.

Group By Day

Rule expressions in Transformation Rules can group derived records by day. In the rule expression shown below, the !D at the end of the expression indicates that the derivative records are grouped by day.

Example: A#[TicketCost]=AvgGroupByDay:A1#[]=None:A2#[]=None:A4#[*]=None:xBDT#[2022-01-01 00;00;00~2022-01-31 23;59;59]!D

When used with a logical operator, the calculation is performed on each grouping. For example, if the Average logical operator is used on the data set shown below, the average is calculated for each day. If the day only has 1 value, that value is returned.

Group By Month

Rule expressions in Transformation Rules can group derived records by month. In the rule expression shown below, the !M at the end of the expression indicates that the derivative records are grouped by month.

Example: A#[TicketCost]=AvgGroupByMonth:A1#[**_]=None:A2#[_**]=None:A4#[*]=None:xBDT#[2022-01-01 00;00;00~2022-01-31 23;59;59]!M

When used with a logical operator, the calculation is performed on each grouping. For example, if the Average logical operator is used, the average is calculated for each month. If the month only has 1 value, that value is returned.

Group By Year

Rule expressions in Transformation Rules can group derived records by year. In the rule expression shown below, the !Y at the end of the expression indicates that the derivative records are grouped by year.

Example: A#[TicketCost]=CountGroupByYear:A1#[]=None:A2#[]=None:A4#[*]=None:xBDT#[2015-01-01 00;00;00~2022-12-31 23;59;59]!Y

When used with a logical operator, the calculation is performed on each grouping. For example, if the Count logical operator is used on the data set shown below, the following counts are returned for each year:

Group By All

Rule expressions in Transformation Rules can group all derived records into one group when specified in a rule expression. In the rule expression shown below, the !All at the end of the expression indicates that the derivative records are grouped into one group that contains all records.

Example: A#[TicketCost]=CountGroupByAll:A1#[]=None:A2#[]=None:A4#[*]=None:xBDT#[2015-01-01 00;00;00~2022-12-31 23;59;59]!All

When used with a logical operator, the calculation is performed on the whole data set. For example, if the Count logical operator is used on the data set shown below, a count of 15 is returned:

Derivative Types

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.