Table types

In every model, Varicent Incentives 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 Incentives. 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.

Table 15. Data for a custom table

Product	Quarter	Compensation Rate
100	Quarter 1	8.00%
100	Quarter 2	7.95%
100	Quarter 3	7.75%

Data tables

In Incentives, 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 Incentives.

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 data records. The primary key column uniquely identifies each record and a parent column identifies the parent of each record. Each parent can have many children, but each child can have only one parent.

There are two ways you can set up your hierarchy tables:

Self-referential - this means that the parent column in hierarchies has a self-referencing relationship, or self-join, within the table. To set a parent for a child record, that parent record must already exist within the table.
Sourced from an external pick list - this means that you can select an external table to pull data from. This lets you choose both parent and child records from a pick list sourced from your external table.

For example, you can manually add payee information to a hierarchy table and create the structure as you add records. Alternatively, you could source payee data from an external table and use the hierarchy table to create an organizational structure for your payees. No matter the approach, your hierarchy table may resemble the following example, where the Parent column contains the PayeeID for each payee's manager.

Table 16. Parent column example

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

If we were to view this table in the hierarchy structure view, it would look something like this:

E210 KAREY (Doug Karey)
- E040 YOUNG (Diana Young)
  - E030 CLARKSON (Debbie Clarkson)
    E020 HUMPHREY (Darnell Humphrey)
    E010 HUDDLE (Dan Huddle)

The following table describes the hierarchy tables that are added automatically when you create a model.

Table 17. Types of hierarchy tables

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, general ledger 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.

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, general ledger 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 Incentives. 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 Incentives. System tables include the Title, Salutation, Currency, Group Member, Country, Status, and PayeeEffectiveDatedInfo 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 18. Types of system tables

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 are 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.
Country_System	The PayeeEffectiveDatedInfo table picks from this table to populate the CountryID column. It lists all of the countries that the payees in the Payee table are from. More fields can be added to this table if needed.
Status_System	The PayeeEffectiveDatedInfo table picks from this table to populate the StatusID column. It lists the statuses that a payee in the Payee table can hold; by default, these statuses are Active and Inactive. More fields can be added to this table if needed.
PayeeEffectiveDatedInfo_System	This table is used to track the entitlement usage of payees in the model through the PayeeTypeID (Standard/Non-Standard) and StatusID (Active/Inactive) columns. All payees in this table must also be in the Payee table. More fields can be added to this table if needed.