Calculation partition ordering
In Incentives calculations, the primary key comprises all the partitions, excluding the value. The database automatically creates a clustered index based on these partitions.
Recommendations for partition ordering
Narrow primary keys are crucial because more partitions require m ore database resources to sort and store the clustered index, potentially affecting query performance and extending calculation run times.
Granularity-based ordering
When defining partitions in the calculation wizard, the order of partitions significantly impacts performance. Follow these recommendations for optimal partition ordering:
Similar level of granularity or configuration tables
Order the partitions from least granular to most granular.
Example:
Correct {CompPlan, Region, PayeeID}
Incorrect{PayeeID, Region, CompPlan}
Consistency across sources
Ensure that all calculation sources and their partition order remain consistent across calculations.
Example: {CompPlan, Region, PayeeID} for similar granular calculations.
Sample data
CompPlan | Region | PayeeID |
|---|---|---|
SR | Europe | TS0001 |
SR | Europe | TS0002 |
SR | Asia | TS0003 |
SR | US | TS0004 |
SR | US | TS0005 |
CSM | Europe | TS0006 |
CSM | Europe | TS0007 |
CSM | Asia | TS0008 |
CSM | US | TS0009 |
CSM | US | TS0010 |
Config table
CompPlan | Region | Target |
|---|---|---|
SR | Europe | 1000 |
SR | Asia | 800 |
SR | US | 1200 |
TransactionID or PayeeID level calculations
For calculations that are joined at the TransactionID or PayeeID level, order partitions from most granular to least granular.
Example:
Correct {TransactionID, PayeeID, Region}
Incorrect{PayeeID, Region, TransactionID}
Consistency in partition ordering
Ensure that all calculation sources using TransactionID or PayeeID levels have consistent partition orders.
Example: {TransactionID, PayeeID, Region} for consistent calculations at this level.
Sample data
TransactionID | PayeeID | Region |
|---|---|---|
TX00001 | TS0001 | EUROPE |
TX00002 | TS0001 | EUROPE |
TX00003 | TS0001 | EUROPE |
TX00004 | TS0001 | EUROPE |
TX00005 | TS0001 | EUROPE |
TX00006 | TS0001 | EUROPE |
TX00007 | TS0001 | EUROPE |
TX00008 | TS0001 | EUROPE |
TX00009 | TS0002 | EUROPE |
TX00010 | TS0002 | EUROPE |
TX00011 | TS0002 | EUROPE |
TX00012 | TS0002 | EUROPE |
TX00013 | TS0003 | ASIA |
TX00014 | TS0003 | ASIA |
TX00015 | TS0003 | ASIA |
Transactional data
TransactionID | Quantity | OrderDate | ProductID |
|---|---|---|---|
TX00001 | 1 | 5/12/2020 | PID0001 |
TX00002 | 1 | 4/15/2020 | PID0002 |
TX00003 | 2 | 9/7/2020 | PID0003 |
TX00004 | 3 | 5/13/2020 | PID0004 |
TX00005 | 5 | 5/12/2020 | PID0005 |
TX00006 | 8 | 4/15/2020 | PID0001 |
TX00007 | 9 | 9/7/2020 | PID0002 |
TX00008 | 5 | 5/13/2020 | PID0003 |
TX00009 | 3 | 5/12/2020 | PID0004 |
TX00010 | 10 | 4/15/2020 | PID0002 |
TX00011 | 16 | 9/7/2020 | PID0003 |
TX00012 | 5 | 5/13/2020 | PID0004 |
TX00013 | 3 | 5/12/2020 | PID0006 |
TX00014 | 2 | 4/15/2020 | PID0007 |
TX00015 | 8 | 9/7/2020 | PID0003 |