Table types
In every model, Varicent Incentive Compensation Management (ICM) adds system tables when the model is created. In addition to these system tables, you can create Data, Structural, Custom, and Hierarchy tables.
After a table is created, new fields can be added and existing fields can be deleted. However, the primary key structure of the table cannot change and adding or deleting primary key fields is not permitted after table creation.
Custom tables
Custom tables are used to help define the structure of your model in Varicent ICM. Custom tables are customizable.
Because you can add multiple primary key columns to a custom table, pick lists cannot pick from custom tables.
Custom tables can be created for multiple purposes. One common use of a custom table is to contain data for reference values that vary by one or more key members, or that exist in the form of a lookup. As a reference for compensation calculations, you can add a custom table to hold compensation rates that vary by product and by quarter, or multipliers that define benefits based on a percent of quota attainment. For example, a custom table might contain the format displayed in the following table.
Product | Quarter | Compensation Rate |
|---|---|---|
100 | Quarter 1 | 8.00% |
100 | Quarter 2 | 7.95% |
100 | Quarter 3 | 7.75% |
Data tables
In Varicent ICM, create data tables to store transactional data, such as lists of sales or orders.
A data table contains a date field, but you can add other fields as needed. When periods are locked, all data tables that are associated with the locked calendar are locked by the Date field in the table (other table types are not locked). If more than one Date field exists in the data table, you must select a Lock by Date when the table is created to specify which field to use to lock the table.
Hierarchy tables
Hierarchy tables contain relationships that are necessary to analyze and manage organizational data in Varicent ICM.
After a model is created, the Account, Payee, and Time hierarchy tables are created automatically.
More hierarchy tables can be added after the model is created. Common hierarchy examples include product, customer, and territory hierarchies.
Hierarchies use parent-child relationships to organize structural information. In a parent-child hierarchy, two table columns define the hierarchical relationships among the dimension members. The primary key column uniquely identifies each dimension member and a parent column identifies the parent of each dimension member. Each parent can have many children, but each child can have only one parent. The parent column in hierarchies has a self-referencing relationship, or self-join, within the table.
For example, if the Parent column in the Payee table contains each payee's manager, your table might look like the following table. The Parent column contains the ID for each payee's manager and references the PayeeID column.
PayeeID | Name | Parent |
|---|---|---|
E010 HUDDLE | Dan Huddle | E030 CLARKSON |
E020 HUMPHREY | Darnell Humphrey | E030 CLARKSON |
E030 CLARKSON | Debbie Clarkson | E040 YOUNG |
E040 YOUNG | Diana Young | E210 KAREY |
E210 KAREY | Doug Karey |
The following table describes the hierarchy tables that are added automatically when you create a model.
Table | Description |
|---|---|
Payee | This table is used to store data about individuals who are being compensated by, or who are approving, variable compensation payments. It contains many of the fields that are typically needed to organize payee information. You do not need to use columns that do not suit your organization, and you can add more columns to the table as required. The primary key column is the Payee ID column. The Parent column identifies the groups that your payees are organized into. For example, because payees are often grouped into territories, the Parent column is often used to store payee territories. |
Accounts | This table is used to store loaded values from your source system and output values. The accounts are typically a subset of the general ledger chart of accounts and are associated with commissions and other variable compensation payments. By default, the table contains AccountID, Name, Parent, and DrCr fields. In this table, the account ID column is the primary key field. The Parent column is used to organize your account IDs into groups. For example, GL accounts are typically grouped by account type, so the account type is stored in the Parent column. |
Time | This table is used to store the payroll periods calendar. This table is the only hierarchy that is not displayed as a table in the Composer or Data modules. |
Structural tables
Structural tables help to define the structure of your model in Varicent ICM. Structural tables are customizable.
Because you can add only one primary key column in a structural table, pick lists can pick from structural tables.
System tables
System tables are automatically added in Varicent ICM. System tables include Title, Salutation, Currency, and Group Member tables.
System tables are added to contain common attributes for fields in the Payee table. The Title, Salutation, and Payee Currency fields in the Payee table are populated with information from their respective system tables. Therefore, all possible titles, salutations, and currencies that are needed by the Payee table must be listed in the system tables. More fields can be added to system tables as needed.
Table | Description |
|---|---|
Title | This table contains a TitleID field and a Title field. The Title column in the Payee table picks from the TitleID field in the Title System table, so all job titles attributed to any payee in the Payee table is loaded into the Title table. More fields can be added to this table if needed. |
Salutation | This table contains a Salutation column and is pre-populated with common salutations. The Salutation field in the Payee table picks from the Salutation table, so any salutations attributed to any payee in the Payee table must be loaded into the Salutation table. More fields can be added to this table if needed. |
Currency | This table is populated with USD after model creation. More currencies can be added if necessary. This table contains a CurrencyID field and a Name field. The Payee Currency field in the Payee table picks from the Currency table, so any currencies needed to pay any payee must be contained in the Currency table. More fields can be added to this table if needed. |
Group Member | This table contains the PayeeID, Name, Start Date, and End Date for each payee that has been added to a Payee Group. The Group Member table is created automatically and its structure cannot be edited. The primary key field for this table is the GroupID, which is automatically generated. This table can be used as a calculation source, but you cannot view its structure or contents in the Composer or Data modules. |