Data sources

In Incentives, you can join two or more tables or calculations together to define the data sources for your calculation by adding columns. This is referred to as a join.

Joining data sources by adding columns is typically used when the data from two or more sources will be compared against each other, or used in a formula.

Normally, you join data sources together based on a point of commonality by defining restrictions for the join. By default, columns with the same name and of the same type are joined automatically. You can choose to disable the automatic creation of joins.

For example, consider a situation where you join a product table to a payee table to display the products that were sold by each payee. If you restrict, or join, your results based on the payee ID, you can get the results shown in the following tables.

You can use restrictions to join each new source to an existing source based on a point of commonality. For example, if the initial source contains transactional data for each payee, and the second source contains compensation rates to be applied to sales, you must specify how to associate the two types of payee data. If the compensation rates vary based on job title, and Title ID is contained in both tables, they can be joined on that basis, for example, ERP.TitleID = Rates.TitleID.

Adding restrictions is an important part of adding a data source. If you do not create restrictions, each row in the new source is added to each row in the existing source. This action creates many more rows than either source would have individually.

For example, if the first source had 100 rows and the second had 50 rows, the result of a join with no matches would be 100 x 50 = 5000 rows, or a row for each possible combination of rows in the two sources.

While restrictions are normally necessary, there might occasionally be instances where you want to display all the possible row combinations when you join two sources. Imagine that you have a product table and a payee table, and you want a table that shows each product that might potentially be sold by each payee.

Imagine that you complete the following steps:

Your join produces the results in the following table.

In Incentives, you can create a union between data sources by adding data from one source to the bottom of another source. This creates one data source.

You might find that joining sources (adding columns) does not organize your data in a way that creates the results that you want. If so, you can append sources by adding rows. This feature is similar to adding columns in that both options are used to relate information from multiple sources.

Consider a plan that pays compensation to employees based on their total sales minus any returns that are made by month. Look at the information for employee 001.

If you follow these steps to add rows from the return data table to the sales data table, the results match the Results of adding rows table.

A result is shown for Month 3, even though there is no record for that month in the return data table. If you link these tables by adding columns, the row containing March would not be displayed.

All columns in the source table must be mapped to a column in the union table. For this reason, the columns in the source table must be similar to the columns in the union table. For example, both the Sales data table and the Return data table had employee ID, amount, and date columns.

In Incentives, use the Anchor feature when joining sources in data stores, calculations, and Presenter report data sources.

Enabling the Anchor feature allows you to display rows for all records for the first data source even if the join does not find matching records in the second data source. Only matched records from the second data source are returned. If the second data source returns one row and the first data source returns more than one matching row for it, the values in the second source are repeated for each distinct row in the first data source.

Consider a situation where you have the following two data sources:

If you preview from Data Sources tab when the Anchor feature is used, records from the first data source are returned even when there is no matching record in the second source.

When you partition an anchored source in calculations, any partitions that have null results are removed. When you preview from the Formulas tab with Name, Department ID, and Department Name as partitions, Chris Ackles is removed from the data set. However, if you partition by Name only, Chris Ackles is included.