Business Rules Examples

Learn more about commonly used business rules for data transformation.

Overview

Business rules are applied during data load to transform group data based on defined conditions, allowing you to modify data to ensure accuracy and consistency. Understanding these transformations, and when to use them, will help you efficiently manage your event streams. For more information, see Manage event streams.

Business rules are categorized as follows:

• Visier Business Rule Language (VRBL): These rules are created and defined using the Visier Business Rule Language (VBRL) to transform subject data in your event stream. For more information, see Visier Business Rule Language (VBRL).

• Predefined business rules: These rules are built into the platform and already exist in Visier's internal rule library.

Predefined business rules

Predefined business rules are executed using the call tag.

Syntax

1
2
3
4
5
call RULENAME(
    ARG1, 
    ARG2, 
    …
)

• call: Instructs Visier to call the predefined rule in the internal rule library.
• RULENAME: Specifies the rule to use. Each rule executes different logic against the arguments in the formula.
• ARG1, ARG2: Specifies the arguments, such as subjects or properties, to validate. Rules accept zero, one, or more arguments.

If a predefined rule has optional parameters, you must define parameters in the order listed in the rule syntax. For example, to declare a value for the optional2 parameter in the code below, you must present a value for optional1.

1
2
3
4
call predefinedRule(
    mandatory1, 
    mandatory2 [, optional1, optional2]
) 

backfillChanges

Fills in missing values previously set by adjusting the initial values of specific properties starting from a defined date. This adds information to the subject when existing values are not set. You can explicitly set the start date with startDate. Otherwise, Visier infers the start date from the earliest recorded change in the specified properties.

Syntax

1
2
3
4
5
6
call backfillChanges(
    skipNone, 
    backfillPrimaryStreamOnly, 
    {properties} 
    [, startDate]
)

• skipNone (optional, Boolean, default: false): A flag that determines whether properties with None values should be skipped in the rule application.
• backfillPrimaryStreamOnly (optional, Boolean, default: false): A flag to include the primary event stream in the rule and exclude the auxiliary event stream.
• Properties: Specifies the properties to be adjusted in the backfill.
• startDate: (optional): The assigned new date for the data to be backfilled to. If there is no declared startDate the rule sets the date of the first change in any of the specified properties. The format used in the rule is YYYY, MM, DD.

Examples

Say you want to backfill a property to a specific date with skipNone value set to true and the backfillPrimaryStreamOnly set to false. In this example, the First_Name property backfills using the preset values to the specified start date of January 1, 2015. This rule ignores all First_Name property values of None.

1
2
3
4
5
6
call backfillChanges(
    true, 
    false, 
    {First_Name}, 
    Date(2015, 01, 01)
)

The following table shows how the data is presented before the backfillChanges rule.

The following table shows how the data is presented after the backfillChanges rule.

If you want to backfill a specific property to a date with the skipNone flag set to the default false value. This example backfills the First_Name property using the specified start date and includes any First_Name property with value of None in the backfill. The backfillPrimaryStreamOnly flag is not specified, so the default value is false and the rule applies to both the primary and auxiliary event stream.

1
2
3
4
call backfillChanges(
    {First_Name}, 
    Date(2015, 01, 01)
)

You can backfill a property to a defined date with skipNone true, and only account for the primary stream when determining the startDate. In this example the rule fills in all values of the First_Name property to January 1, 2015 on the primary stream and ignores events in the auxiliary stream.

1
2
3
4
5
6
call backfillChanges(
    true, 
    true, 
    {First_Name}, 
    Date(2015, 01, 01)
)

You can backfill missing values for multiple attributes in a single application of the backfillChanges rule. In this example, you can call the backfillChanges rule on the First_Name, Last_Name, Pay_Level, and Birth_Date properties. As the startDate is not included, the backfillChanges rule resets the values in the specified properties to the value on the date of the earliest recorded change.

call backfillChanges(
    false, 
    {First_Name, Last_Name, Pay_Level, Birth_Date}
)

When dealing with several properties in the same rule, it makes sense to adjust all properties with a few exceptions. To define a set of properties to ignore when backfilling, use allPropertiesExcept. In this example you will learn how to backfill all properties except NonActive with the skipNone flag set to true. If no properties are specified in the allPropertiesExcept parameter (allPropertiesExcept({})), all properties defined before the rule are included in the backfill. Be aware that this may lead to unintended effects on time-sensitive properties like NonActive or Performance.

1
2
3
4
call backfillChanges(
    true, 
    allPropertiesExcept({NonActive})
)

groupAndPick

Picks a single event out of a set of events, and deletes all other events that remain in the set. This is useful for removing duplicate events, resolving inconsistencies between data sources, and handling multiple changes on the same timestamp.

Syntax

1
2
3
4
5
call groupAndPick(
    picker,      
    groupBy,    
    groupFilter 
)

• picker: Defines how to select a single event from the grouped set. For more information, see Picker functions.
• groupBy (optional): Groups events before passing them to the picker.
• groupFilter (optional): Defines which events are considered by the rule. This parameter is used to limit the events deleted by groupAndPick.

To use groupAndPick:

1. Select events that satisfy the eventFunction in the picker. The eventFunction specifies which events are considered for the picking strategy. For more information, see Picker functions.
2. Filter these events using groupFilter. Those that satisfy the filter are candidates of being grouped while others are ignored.
3. Group the filtered events according to the groupBy parameters.
4. Filter the groups with the pickFilter parameter.
5. For each group, select a single event using the picker. For example, pickEarliest or pickLatest.

Picker functions

A picker function in the groupAndPick rule limits the events picked after grouping, or extends the range of the picking strategy to more than one date. The following table describes each picker function used in the rule.

Count functions

The following table describes the time span function used in the pickAndGroup rule. A value of function() with no numeric count included in the brackets is equivalent to a single instance. For example, one day is day(), one month is month(), and one year is year().

Examples

This business rule example picks a single event out of all events on a date. This examines all Pay_Change regular events on a single date and picks the Pay_Change which either has the most recent timestamp value or whichever event is most recent.

1
2
3
call groupAndPick(
    pickLatest(Pay_Change)
)

In this example, the rule selects the first event on a given date, pay changes occurring within two days of the initial member are grouped together, and the earliest one is chosen.

1
2
3
call groupAndPick(
    pickEarliest(Pay_Change, true, days(2))
)

In this example, the groupAndPick rule selects a single event from all Pay_Change regular events on a date. This example will examine all Pay_Change regular events on a date and divides them into different categories based on their PayType value. Then, for each category, only the Pay_Change with the most recent event is picked.

1
2
3
4
call groupAndPick(
    pickLatest(Pay_Change), 
    Pay_Change.PayType
)

In this example, the groupAndPick rule deletes events that are not the Regular pay type.

1
2
3
4
5
call groupAndPick(
    pickEarliest(Pay_Change), 
    "A constant value", 
    Pay_Change.PayType = "Regular"
)

In addition to groupFunction, there is another way to control the events that are picked by groupAndPick. The following example shows how pickEarliest and pickLatest have an optional parameter called pickFilter permitting finer control over picking operation. In this example, the rule excludes events with Amount less than or equal to fifty from the selection process.

1
2
3
4
5
call groupAndPick(
    pickEarliest(Compensation_Payout.Payout_Amount > 50), 
    Compensation_Payout.Payout_Type
)

augmentingMapping

Uses the values of a property to look up values in the target mapping file object and apply them to the properties in the current subject. This rule does not override existing property values, so it is good for adding data to empty properties or for filling in gaps or missing data.

Syntax

1
2
3
4
5
call augmentingMapping(
    mappingName, 
    mappingKey, 
    mappedProperties [, ifUnmapped, filter, ifOutputExist, ignoreIfNoValue]
)

• mappingName: The file name of the mapping file.
• mappingKey: The lookup key property in the current subject. This must be an existing property in the subject and should not be a subject ID like EmployeeID.
• mappedProperties: The properties in the current subject that are mapped to strings corresponding to column names in the target mapping. The syntax is Map[Property -> String].
• ifUnmapped (optional, useDefault() or ignore(), default: ignore()): Specifies behavior when no match is found in the target object.
• useDefault() (optional): Inserts a default-valued event if no lookup value found.
• ignore() (optional): Does nothing when lookup key does not exist in lookup stream.
• filter (optional): Applies a filter to the lookup process.
• ifOutputExist (optional, augmentAnyways() or noAugmentIfValueExist(), default: augmentAnyways()): Determines if augmentation occurs when values already exist.
• augmentAnyways() (optional): Always augments the event stream with lookup events.
• noAugmentIfValueExist() (optional): Does not augment the event stream if the stream contains existing property values.
• ignoreIfNoValue (optional, Boolean, default: false): If true, it prevents event insertion when no lookup value is found.

Example

Let's say you want to use the values of a property to look up values in the target mapping file object and apply them to the properties in the current subject. This will augment a single mapping by adjusting the property values in the subject property. In this example, the rule assigns the lookup property CompMax to the Employee.Pay_Level_Compensation_Maximum property, and CompMin to the Employee.Pay_Level_Compensation_Minimum property. This rule only applies if the property is unmapped in the existing mapping and if the date of change is on or after January 1, 2018. This applies to values that already exist by adding the aumentAnyways(). Because ignoreIfNoValue is false, the rule allows event insertion when no lookup value is found. This example relies on the pay_levels_post_stg lookup existing in the data.

1
2
3
4
5
6
7
8
9
10
11
12
call augmentingMapping(
    "pay_levels_post_stg", 
    Employee.JobPayLevelCode,
    {
    Employee.Pay_Level_Compensation_Maximum -> "CompMax", 
    Employee.Pay_Level_Compensation_Minimum -> "CompMin" 
    },
    useDefault(),
    dateOf(changeIn(Employee.Pay_Level)) >= date(2018,1,1),  
    augmentAnyways(),
    false
)

augmentingMultipleMapping

The augmentingMultipleMapping rule is similar to the regular augmenting mapping rule. However, augmentingMultipleMapping accepts a mapping targetGroup instead of a single mapping name. By including a targetGroup, the business rule can be associated with all lookup mappings defined with the target group, and combine information from these mappings into the auxiliary stream.

Syntax

1
2
3
4
5
6
7
8
9
call augmentingMultipleMappings(
    targetGroup, 
    mappingKey, 
    mappedProperties, 
    ifUnmapped, 
    filter, 
    ifOutputExist, 
    ignoreIfNoValue
)

• targetGroup: Includes supplemental data in a mapping that is considered separate from the main source. For more information, see Target group.

changeDates

Inserts a value for targetProperty based on the provided nameChangeDateProperties. The targetProperty value is the corresponding string for the property's namedChangeDateProperties. Use this rule if you want to insert different values based on different dates provided in the source.

Syntax

1
2
3
4
5
changeDates(
    targetProperty, 
    {namedChangeDateProperties},
    changeAll
)

• targetProperty: The property changed by the rule application.
• namedChangeDateProperties: A map of date properties to string values that would be inserted for targetProperty. The syntax is namedChangeDateProperties -> new string value. For example, Requisition.temp_CreatedDate -> "Created".
• changeAll (optional, Boolean, default: false): If true an event is inserted at every timestamp for each nameChangeDateProperties profile change event. If false an event is inserted at the last timestamp for each nameChangeDateProperties profile change event.

Examples

The code samples below demonstrate how the changeDates rule is applied to the source data. In this example it is used to insert a value for the target property, Requisition.Requisition_Status, based on the provided nameChangeDateProperties.

1
2
3
4
5
6
call changeDates(
    Requisition.Requisition_Status, 
    {Requisition.temp_CreatedDate -> "Created",
    Requisition.temp_ApprovedDate -> "Approved",
    Requisition.temp_ClosedDate -> "Closed"}
)

The following table represents the source data stream before applying the rule. Note that the data is presented in a wide format.

The following table represents the resulting stream after the changeDates rule is applied. Because changeAll is true, the rule inserts an event at every timestamp for each nameChangeDateProperties profile change event.

consolidateRegularEvents

Consolidates multiple regular events on the same date or over a range of dates into a single event. For example, running this rule sums all pay events that happen in the same month as designated by EventDate.

Syntax

1
2
3
4
5
6
call consolidateRegularEvents(
    regularEventSchema,     
    groupingProperty,       
    resolutionStrategies    
    filter,                 
)

• regularEventSchema: The regular event to be consolidated. For example, PayEvent.
• groupingProperty: The grouping property applied to the regular event. For example, PayEvent.PayType would group together all PayTypes in the PayEvent.
• resolutionStrategies: Defines the consolidation method. Several strategies can exist in the same rule, but the groupingProperty should never be included in the application of resolutionStrategies. This parameter is required for all available properties except for the primary key and EventDate. The syntax for this parameter is PayEvent.Amount -> sum().
• filter (optional): Provide exceptions to the above rule when consolidating events. For example, PayEvent.PayType != "Bonus" would exclude all values for pay labeled as Bonus.

Resolution strategies

The following strategies can be used as part of the resolutionStrategies parameter.

• sum(): Sums the value of the given attribute for all regular events on the day. Can only be applied to attributes that are integers.
• average(): Takes the average value of the given attribute for all regular events on the day. Only applies to attributes that are floating-point numbers, or numbers that have decimal points.
• ignoreAttribute(): Ignores the attribute and sets it to None in the consolidated event.
• max(): Takes the largest value of the attribute for all regular events on the day and stores it in the consolidated regular event.
• min(): Take the smallest value of the attribute for all regular events on the day and stores it in the consolidated regular event.

Examples

Say you wanted to consolidate multiple regular events happening on the same date. The example below adds together all Amount values of each separate PayType (Labor or Dinner) occurring on the same EventDate.

1
2
3
4
5
6
call consolidateRegularEvents(
    PayEvent,
    PayEvent.PayType,
    { PayEvent.Amount -> sum(), 
    PayEvent.PayLevel -> sum() PayEvent.PaySystem -> ignoreAttribute() } 
)

The following table shows how the data is represented before the consolidateRegularEvents rule is applied.

The following table shows how the data is represented after the consolidateRegularEvents rule is applied. Because the rule specifies PayEvent.PaySystem -> IgnoreAttribute (), the resulting data replaces PaySystem values with None.

Say you wanted to consolidate distinct events of different types in to separate groups using the EventDate attribute. These events would be over a span of days and the source data contains attributes that are not relevant to the grouping and should be ignored. In this example, the values for Amount of each PayType are added and grouped by the different dates.

1
2
3
4
5
6
call consolidateRegularEvents(
    PayEvent,
    PayEvent.PayType, 
    { PayEvent.Amount -> sum(), PayEvent.PayLevel -> sum() PayEvent.PaySystem -> ignoreAttribute() },
    month() 
)

The following table shows how the data is represented before the consolidateRegularEvents rule is applied.

The following table shows how the data is represented after the consolidateRegularEvents rule is applied. Because the rule specifies that PayEvent.Amount -> sum(), PayEvent.PayLevel -> sum() PayEvent.PaySystem -> ignoreAttribute() } month() ). The Amount for each PayType have been summed and filtered by month. Note the PaySystem attribute is ignored in the rule and is replaced by a None value in the data.

addValidRanges

Adds valid ranges to profile change events and can apply to a single property or several properties in the same instance. These ranges may differ between file types, for example temporal versus transactional. For more information, see Data Load Types, Frequency, and Granularity. This rule will look for profile change events that have a property contained in targetProperties and will add a valid range if the following conditions are met.

• The profile change event property exists in targetProperties.
• The profile change events does not have a valid defined range.
• Another profile change event with a defined valid range occurs at the same time as the targetProperties.

Syntax

1
2
3
call addValidRanges(
    targetProperties [, filter]
)

• targetProperties : The properties to add valid ranges to.
• filter (Optional): Controls which properties will be targeted by the rule.

Example

Say you want to add a valid range to an attribute called Employment_Status on the Employee subject. In this example, the rule would add a valid range to all None attributes to match the value of the Employment_Status attribute on the same date. Note how the profile change event on 2018-09-30 is not affected by this rule as there are no other profile events on that date.

1
2
3
call addValidRanges(
    { Employee.Employment_Status }
)

The following table shows how the data is presented before the addValidRanges rule is applied.

The following table shows how the data is presented after the addValidRanges rule is applied.

moveToStartOfDay

Adjusts all events in the event stream to align to 00:00:000 coordinated universal time (UTC). This rule is added when a data load fails due to a mapping that includes timestamps. Employee data typically uses day-level granularity, where timestamps are uncommon. If new data includes timestamps, either the mapping formula can be updated to exclude them, or the rule can be added to remove the timestamps.

Syntax

1
call moveToStartOfDay()

dropProfileIfCondition

Looks for a condition in the existing data and removes all events when that condition is met. The condition is specified using conditionFunction and when satisfied marks all events pertaining to a subject ID for removal. Marked events are then deleted if cancelFunction is never fired using the default value false. When removing multiple IDs, due to duplication or the person is no longer employed with the company, the source data will include a column to explicitly state which employees should be removed. This column can be used as a parameter in the function. If a mistake is made, the same file can be used to reverse the removals.

Syntax

1
2
3
4
5
call dropProfileIfCondition(
    conditionFunction[, 
    cancelFunction, 
    endDate]
)

• conditionFunction: Specifies all events, both profile and regular, for the current subject that will be marked for deletion.
• cancelFunction (optional, default: false): If true cancels the effect of conditionFunction. Events will be marked for deletion but will not be deleted.
• endDate (optional): The date specifying the range of the rule. Only events happening before or on the endDate will be deleted. The format is YYYY, MM, DD

Example

In this example, all conditionFunction values of First_Name equal to Test will be marked for deletion. The cancelFunction default value is false and as there is no endDate specified, all events to the current date will be deleted.

1
2
3
4
call dropProfileIfCondition(
    First_Name = "Test"
    endDate(2016, 01, 01)
)

replacementMapping

Replaces the values of a property with values in the target mapping file object. It can be considered an override where the lookup key is the mapping property.

Syntax

1
2
3
4
5
call replacementMapping(
    mappingName, 
    mappingKey, 
    targetColumn [, filter, useDefault, ignoreIfNoValue]
)

• mappingName: String name of the mapping file object.
• mappingKey: The property used as the lookup key. This property must exist in the current subject for the rule to execute correctly.
• targetColumn: A column name in the target mapping file object. If the lookup fails, the value of targetColumn is set to None.
• filter (optional): A filter to apply to the mapping events.
• useDefault (optional, Boolean, default: true): If true it will insert a default value for the attribute if no lookup is found. If false, it will do nothing to the mapping file object. Note this is only used when the lookup key does not exist in lookup stream.
• ignoreIfNoValue (optional, Boolean, default: false): When true, the rule will not insert events when no lookup value is found.

Examples

In this example, the rule would replace the values in New_Location_Value with the values from Employee.Location.Location_3 in the Location_Lookup mapping file. As the lookup key exists in the lookup stream, useDefault is not necessary and the default value of ignoreIfNoValue is false.

1
2
3
4
5
call replacementMapping(
    "Location_Lookup", 
    Employee.Location.Location_3, 
    "New_Location_Value"
)

In this example we will find and correct values in the New_Location_Value target column by setting the filter value as true and the useDefault as false. Setting useDefault to false means that any value not found will be replaced with Unknown and the rule will only provide values necessary for the changes.

1
2
3
4
5
call replacementMapping(
    "Location_Lookup", 
    Employee.Location.Location_3, 
    "New_Location_Value", true, false
)

createSubjectMember

Inserts a new subject ID and associated properties if no subject member with the same ID is present in the data. This rule can be used to insert virtual members in dimensions or other placeholder subject members. For example, if you want an organization hierarchy for a subset of people to rollup into a different organization first. You can make this new organization using this rule, and adjust the mappings so the subset of people rollup into this new organization instead.

Syntax

1
2
3
4
call createSubjectMember(
    idProperty, 
    id [, otherPropertyAssignments, creationDate]
)

• idProperty: Key property of the subject.
• id: ID of the created subject member.
• otherPropertyAssignments (optional): Extra map of properties to assign in the newly-created member. The mapping format is Map[Property → Value].
• creationDate (optional): A date to create the subject member. If undeclared it defaults to the earliest possible date in the data.

Examples

Say you wanted to insert a new member in the Organization_Hierarchy subject. In this example, a new subject Acme Corp, with the ID value of -1 is created. If a different ID is desired then it can be supplied as an optional second argument.

1
2
3
4
call createSubjectMember(
    Organization_ID, "-1", 
    {Organization_Name -> "Acme Corp"}
)

When the idProperty value is not currently available you can insert an Unknown parent-child dimension member. This inserts a new member in the Organization_Hierarchy subject with the default Unknown value.

1
2
3
4
5
call createSubjectMember(
    Organization_ID, "-999", 
    {Organization_Name -> "Unknown", 
    Parent_Organization_ID -> "-1"}
)