Process Flow
This chapter covers the following topics:
- Handling Time Periods
- ETL Processing
- Incremental Copy
- Flattened Hierarchy Data Structure
- Product Hierarchy Versioning
- Salesperson Quota Distribution
Handling Time Periods
The Period parameter is the time period for which data is aggregated in the reports. The plan period is the smallest period within the plan duration for which the goals are set and attainment is measured. Period can be month, quarter or full year.
Periods play an important role when you measure commission of salespersons. Commissions can be calculated against transactions individually at different rates or they can be cumulated for a period and calculated together at a single rate. While calculating commissions for a period, the application ensures that the periods do not overlap.
Note: For more information about periods, see the Oracle Incentive Compensation User Guide.
ETL Processing
OIC Analytics for ODI uses ODI to load data into the datamart tables, using ETL processing.
The ETL process automatically runs in either of two modes determined at runtime:
-
Full: This mode is invoked the first time the process is run and there is no prior data in the datamart tables. Entire data from the transaction system is copied to the datamart tables, which is then synchronized with the source.
-
Incremental: Data that has changed since the last ETL run, or data that has been added after the last ETL run is copied to the datamart tables.
Note: The ETL process should be run after all changes are completed and the calculation engine has finished processing so that the datamart tables are in sync with the OIC system.
During the ODI process, data is copied from source system to the datamart tables. In some cases data is staged in intermediate tables prior to insertion into the datamart tables.
The following objects are staged to the intermediate tables:
-
Product Hierarchy
-
Resource Hierarchy
-
Group Hierarchy
-
Transaction Type
-
Earnings
-
Quota
The following objects are not staged to the intermediate tables:
-
Organization
-
Product
-
Resource
-
Compensation Plan
-
Group
-
Time
-
Scenario
-
Earnings Attribute
The ETL process performs the following functions:
-
Copy data from source to target datamart tables.
-
De-normalize data for efficient reporting.
-
Maintain integrity of dimensions and facts.
-
Perform error handling and management.
-
Incremental data move.
Maps
The application uses maps to load data from the OIC source tables to the datamart tables.
For dimensions and facts that are staged, the application uses maps to load data from source tables to staging tables and subsequently from staging tables to datamart tables. Maps commonly use BATCH_ID, ORGANIZATION_ID and LAST_RUN_DATE as the input parameters for every phase of processing.
Note: Dimension data is always moved before fact data.
Incentive Compensation Analytics – ODI Concurrent Program
Users run the Incentive Compensation Analytics – ODI concurrent program to copy operational data from the OIC system to the datamart tables. This concurrent program initiates an ELT process by invoking the CN_ANALYTICS_PKG ODI scenario.
Note: The concurrent program runs in a mutually exclusive mode with other OIC calculation processes.
Enter the following parameters to run the concurrent program:
-
Organization ID: Identifies the Business Unit where the data will be copied to, in the datamart.
-
User ID: Identifies the user who has requested to run the ELT process.
-
Source System ID: Identifies the source system where data is stored. This parameter is used if you move data to a different instance.
-
ELT Load Type: Identifies if the ELT process is run in Full mode or Incremental mode.
-
Model Flag: Identifies if the data will be used for modeling purpose.
Important: To run this concurrent program you must host the ODI invocation libraries on the EBS Application Server. For information on setup and deployment of these libraries refer the 7631642:R12.CN.B OIC Analytics Patch readme.
Incremental Copy
With the incremental copy feature, OIC Analytics for ODI identifies any data that has changed since the last ETL run and copies only those rows to the datamart tables. The incremental copy process improves the efficiency of the system, by reducing the load on the system.
At the end of the process, the ODI scenario updates the status in its audit tables. This is used to track the incremental process execution.
To ensure that your data is up-to-date, it is recommended that you run the incremental process to optimize the data load performance. Your BI administrator schedules the incremental request sets daily or at regular intervals.
Note: The first data load run will be Full data load. Any subsequent loads will use the Incremental Copy feature.
Flattened Hierarchy Data Structure
When the application copies data from the OIC source tables to the datamart tables, the data is flattened into a structure that can be used by BI. Flattening data optimizes the performance of queries executed by the dashboards and reports.
OIC Analytics for ODI uses column flattening to flatten out data copied from OIC source tables into the target hierarchical datamart tables. The resource and product entities in the datamart tables are column flattened. In this technique, one record exists per chain of hierarchy.
The following is an example of source hierarchical relationship.
The hierarchical structure represented above depicts the entities in the source.
The following table lists the data structured in a manner that reflects datamart, in a column flattened hierarchy:
| Level 1 | Level 2 | Level 3 | Level 4 | Leaf |
|---|---|---|---|---|
| A | A | |||
| A | B | B | ||
| A | B | D | D | |
| A | C | C | ||
| A | C | E | E | |
| A | A | F | F | |
| A | C | E | G | G |
| A | C | E | H | H |
Product Hierarchy Versioning
Products are versioned in the datamart, so that the application can keep an account of changes to the product hierarchy.
The application reloads a product hierarchy when any hierarchy member’s last update date and time is later than the previous ODI ETL processing date. The application will set an end date for the existing hierarchy in the datamart, and the newly loaded hierarchy will be created with a new effective start date.
If the entire product hierarchy is deleted in OIC, then the application will set an end date for the corresponding product hierarchy within the target datamart.
Note: If the products are used in either classification rules or a product hierarchy, you cannot delete the product from OIC system
Salesperson Quota Distribution
In the following organizational structure, Lucas & Jacob have period-based quotas while Robert and John have quarterly quotas.
The following table depicts the period-wise and quarterly quota distributions for the resources within the organization.
| Name | Period | Quota (USD) |
|---|---|---|
| Lucas | Mar-08 | 4000 |
| Jacob | Mar-08 | 5000 |
| Robert | Qtr1-2008 | 12000 |
| John | Qtr1-2008 | 15000 |
If the organization distributes the quotas, then the period-wise allocation would be:
| Name | Period | Quota (USD) |
|---|---|---|
| Lucas | Mar-08 | 4000 |
| Jacob | Mar-08 | 5000 |
| Robert | Jan-08 | 4000 |
| Robert | Feb-08 | 4000 |
| Robert | Mar-08 | 4000 |
| John | Jan-08 | 5000 |
| John | Feb-08 | 5000 |
| John | Mar-08 | 5000 |
Since Robert is set up for the first quarter, the application distributes the total quota equally among the three months of the quarter. Similarly, for John, the application distributes the quota equally among the months in the quarter.
If Robert has achieved a USD 1000 target for Jan-2008, then the attainment for the quarter would be 11.11%. If measured for the month, the attainment will be 33.33%. The following table shows the quota distribution and attainment, as stored in the OIC datamart:
| Name | Interval Type | Period | Period-to-Date Quota | Interval-to-Date Quota | Period-to-Date Attainment | Interval-to-Date Attainment | Attainment % |
|---|---|---|---|---|---|---|---|
| Robert | Quarter | Jan | 0 | 12000 | 3000 | 3000 | 25 |
| Feb | 0 | 12000 | 3000 | 6000 | 50 | ||
| Mar | 12000 | 12000 | 3000 | 9000 | 75 | ||
| John | Quarter | Jan | 0 | 15000 | 5000 | 5000 | 33 |
| Feb | 0 | 15000 | 5000 | 10000 | 66 | ||
| Mar | 15000 | 15000 | 5000 | 15000 | 100 | ||
| Lucas | Month | Jan | 4000 | 4000 | 4000 | 4000 | 100 |
| Feb | 4000 | 4000 | 3000 | 3000 | 75 | ||
| Mar | 4000 | 4000 | 3500 | 3500 | 90 |
If you change any quota, then the application sets an end date for existing quota in the datamart and stores the latest quota with a new effective date.
For example, assume that you have set monthly quotas for Lucas and Jacob, as follows:
| Name | Period | Quota | Creation Date |
|---|---|---|---|
| Lucas | Mar-09 | 5000 | 15-Dec-08 |
| Jacob | Mar-09 | 5000 | 15-Dec-08 |
Data loaded on Dec 15, 2008 into OIC Analytics will be stored as:
| Name | Period | Quota | Effective From Date | Effective to Date | Last Record |
|---|---|---|---|---|---|
| Lucas | Mar-09 | 5000 | 15-Dec-08 | Yes | |
| Jacob | Mar-09 | 5000 | 15-Dec-08 | Yes |
If you change Lucas’ quota on Feb 10, 2009, for the period Mar 2009, from 5000 to 6000, then subsequent propagation of this changed data after Feb 10, 2009 will result in the following data storage within the datamart:
| Name | Period | Quota | Effective From Date | Effective to Date | Last Record |
|---|---|---|---|---|---|
| Lucas | Mar-09 | 6000 | 10-Feb-09 | Yes | |
| Jacob | Mar-09 | 5000 | 15-Dec-08 | Yes |
Assume, a change on Mar 10, 2009 to Lucas’ quota for the period Mar 2009 from 6000 to 5500. Data loaded after Mar 10, 2009, will result in the following data storage within the datamart:
| Name | Period | Quota | Effective From Date | Effective to Date | Last Record |
|---|---|---|---|---|---|
| Lucas | Mar-09 | 6000 | 10-Feb-09 | 1-Mar-09 | No |
| Jacob | Mar-09 | 5000 | 15-Dec-08 | Yes | |
| Lucas | Mar-09 | 5500 | 10-Mar-09 | Yes |
If you run an analytics report run on Mar 01, 2009, it will show USD 6000.00 quota for Lucas. A subsequent report run on Mar 10, 2009 will show Lucas having a quota of USD 5500.00.