Go to primary content

Oracle Product Lifecycle Analytics Installation and Setup Guide Release 3.6.1 E83715-03 |
| Contents

Previous |

# 12 Using External .csv Files

External data templates help you to analyze and make improved Product and Project decisions by enabling other enterprise data to be available for analysis, such as Units Shipped and Demand and Inventory. Oracle Product Lifecycle Analytics supports the following templates:

Template | Description | File Name | OPLA Subject Area | Example Analysis
--- | --- | --- | --- | ---
Project Summary | Product Performance |  |  |
Project Revenue | Planned or actual cost entered on a date. There can be more than one entry per Project | PRJ_FORECAST.CSV | X | Impact of Project delays on revenue
Project Cost | Planned or actual cost entered on a date. There can be more than one entry per Project. | PRJ_COST.CSV | X | Budget vs. Actual analysis (if Project Cost is managed external to Agile PPM)
Product Revenue | Planned or actual revenue by customer on a date. If Customer is not entered, it is assumed to be undefined. | PPM_PRD_REVENUE.CSV | X | Revenue to SKU ratio for better SKU management
Product Demand | Product Demand on a certain date | PPM_PRD_DEMAND.CSV | X | Impact of Project delays based on Product Demand
Product Units Shipped | Units shipped by customer on a date. If Customer is not entered, it is assumed to be undefined | PPM_PRD_UNIT_SHIP.CSV | X | Parts per million defects
Product Units Received | Units received from supplier on a date. If Supplier is not entered, it is assumed to be undefined. | PPM_PRD_UNIT_REC.CSV | X | Parts per million defects for Supplier
Product Inventory (Product Inventory Quantity) | Units available on a certain date | PPM_PRD_INV_QTY.CSV | X | Impact of Change based on Inventory.
Product Inventory (Product Inventory Value) | Value of units available on a certain date | PPM_PRD_INV_VALUE.CSV | X | Cost Impact of Change based on Inventory value

Note All external data templates are supported for Agile PLM customers. For Agile PLM for Process customers, only the PRJ_COST.CSV and PRJ_FORECAST.CSV templates are supported.

## 12.1 Preparing the Data

After the data is extracted from the Enterprise system, it must be prepared to load into Oracle Product Lifecycle Analytics. Make sure that the required fields are correctly populated, lengths are not exceeded, and data types are consistent with those specified, to avoid ETL failures.

The ETL process loads each data source file as Full load each time. So, make sure that only the most relevant and latest data is available. Also, there are no validations performed on the data, so verify that the data type complies exactly with the listed data types.

### 12.1.1 Project Cost

Field | Data Type | Field Required | Field Description
--- | --- | --- | ---
PROJECT_NO Project Number | VARCHAR2(150 CHAR) | Required | The exact Project Number in Agile PLM against which the data is being loaded.
TRANSACTION_TYPE Transaction Type | VARCHAR2(256 CHAR) | Optional | This optional field is not exposed in Oracle Product Lifecycle Analytics.
EXT_TEMPLATE_DATE External Template Date | DATE | Required | This date is used as a date dimension for analyzing the external measures.
REF_NO Reference Number | VARCHAR2(256 CHAR) | Optional | This field may be used as an external template dimension to refer to that brings in the reference number from an external source.
PLAN_VS_ACTUAL Plan vs. Actual | VARCHAR2(256 CHAR) | Required | This field is used to identify if the row is Planned or Actual
AMOUNT Project Total Cost Amount | NUMBER(22, 7) | Required | This field denotes the cost of the Project.

### 12.1.2 Project Revenue

Field | Data Type | Field Required | Field Description
--- | --- | --- | ---
PROJECT_NO Project Number | VARCHAR2(150 CHAR) | Required | The exact Project Number in Agile PLM against which the data is being loaded.
TRANSACTION_TYPE Transaction Type | VARCHAR2(256 CHAR) | Optional | This field is not exposed in Oracle Product Lifecycle Analytics
EXT_TEMPLATE_DATE External Template Date | DATE | Required | This date is used as a date dimension for analyzing the external measures.
REF_NO Reference Number | VARCHAR2(256 CHAR) | Optional | This field may be used as an external template dimension to bring in the reference number from an external source.
PLAN_VS_ACTUAL Plan vs. Actual | VARCHAR2(256 CHAR) | Required | This field is used to identify if the row is Planned or Actual.
AMOUNT Amount | NUMBER(22,7) | Required | This field is used to denote the revenue for the Project.

### 12.1.3 Product Revenue

Field | Data Type | Field Required | Field Description
--- | --- | --- | ---
ITEM_NO Item Number | VARCHAR2(256 CHAR) | Required | The exact Item Number in Agile PLM that represents the Product against which the data is being loaded
ERP_ITEM_NO ERP Item Number | VARCHAR2(256 CHAR) | Optional | This field may be used as an external template dimension to bring in the ERP Item number.
TRANSACTION_TYPE Transaction Type | VARCHAR2(256 CHAR) | Optional | This field is not exposed in Oracle Product Lifecycle Analytics.
EXT_TEMPLATE_DATE External Template Date | DATE | Required | This date is used as a date dimension for analyzing the external measures.
REF_NO Reference Number | VARCHAR2(256 CHAR) | Optional | This field may be used as an external template to bring in the reference number from an external source.
AMOUNT Amount | NUMBER(22,7) | Required | This field denotes the revenue for the Item Number referred to in this row.
PLAN_VS_ACTUAL Plan vs. Actual | VARCHAR2(256 CHAR) | Required | This field is used to identify if the row is Planned or Actual.
CUSTOMER_NO Customer Number | VARCHAR2(256 CHAR) | Required | This field contains the exact Customer number for this customer in Agile PLM.
SITE Site | VARCHAR2(256 CHAR) | Required | Enter Global if you do not use Sites within Agile PLM. Enter the name of the specific site, if you use Agile PLM and wish to count the Units shipped against a particular site.

### 12.1.4 Product Demand

Field | Data Type | Field Required | Field Description
--- | --- | --- | ---
ITEM_NO Item Number | VARCHAR2(256 CHAR) | Required | The exact Item Number in Agile PLM that represents the Product against which the data is being loaded
ERP_ITEM_NO ERP Item Number | VARCHAR2(256 CHAR) | Optional | This field may be used as an external template dimension to bring in the ERP Item number.
TRANSACTION_TYPE Transaction Type | VARCHAR2(256 CHAR) | Optional | This field is not exposed in Oracle Product Lifecycle Analytics.
EXT_TEMPLATE_DATE External Template Date | DATE | Required | This date is used as a date dimension for analyzing the external measures.
REF_NO Reference Number | VARCHAR2(256 CHAR) | Optional | This field may be used as an external template to bring in the reference number from an external source.
NO_OF_UNITS Number of Units | NUMBER(22) | Required | This field contains the number of units.
PLAN_VS_ACTUAL Plan vs. Actual | VARCHAR2(256 CHAR) | Required | This field is used to identify if the row is Planned or Actual.
CUSTOMER_NO Customer Number | VARCHAR2(256 CHAR) | Required | This field contains the exact Customer number for this customer in Agile PLM.
SUPPLIER_NO Supplier Number | VARCHAR2(256 CHAR) | Optional | This field contains the exact Supplier number for this customer in Agile PLM.
SITE Site | VARCHAR2(256 CHAR) | Required | Enter Global if you do not use Sites within Agile PLM. Enter the name of the specific site, if you use Agile PLM and wish to count the Units shipped against a particular site.

### 12.1.5 Product Units Received

Field | Data Type | Field Required | Field Description
--- | --- | --- | ---
ITEM_NO Item Number | VARCHAR2(256 CHAR) | Required | The exact Item Number in Agile PLM that represents the Product against which the data is being loaded
ERP_ITEM_NO ERP Item Number | VARCHAR2(256 CHAR) | Optional | This field may be used as an external template dimension to bring in the ERP Item number.
TRANSACTION_TYPE Transaction Type | VARCHAR2(256 CHAR) | Optional | This field is not exposed in Oracle Product Lifecycle Analytics.
EXT_TEMPLATE_DATE External Template Date | DATE | Required | This date is used as a date dimension for analyzing the external measures.
REF_NO Reference Number | VARCHAR2(256 CHAR) | Optional | This field may be used as an external template to bring in the reference number from an external source.
NO_OF_UNITS Number of Units | NUMBER(22) | Required | This field contains the number of units being received.
PLAN_VS_ACTUAL Plan vs. Actual | VARCHAR2(256 CHAR) | Required | This field is used to identify if the row is Planned or Actual.
SUPPLIER_NO Supplier Number | VARCHAR2(256 CHAR) | Optional | This field contains the exact Supplier number for this customer in Agile PLM.
SITE Site | VARCHAR2(256 CHAR) | Required | Enter Global if you do not use Sites within Agile PLM. Enter the name of the specific site, if you use Agile PLM and wish to count the Units received against a particular site.

### 12.1.6 Product Units Shipped

Field | Data Type | Field Required | Field Description
--- | --- | --- | ---
ITEM_NO Item Number | VARCHAR2(256 CHAR) | Required | The exact Item Number in Agile PLM that represents the Product against which the data is being loaded
ERP_ITEM_NO ERP Item Number | VARCHAR2(256 CHAR) | Optional | This field may be used as an external template dimension to bring in the ERP Item number.
TRANSACTION_TYPE Transaction Type | VARCHAR2(256 CHAR) | Optional | This field is not exposed in Oracle Product Lifecycle Analytics.
EXT_TEMPLATE_DATE External Template Date | DATE | Required | This date is used as a date dimension for analyzing the external measures.
REF_NO Reference Number | VARCHAR2(256 CHAR) | Optional | This field may be used as an external template to bring in the reference number from an external source.
NO_OF_UNITS Number of Units | NUMBER(22) | Required | This field contains the number of units being shipped.
PLAN_VS_ACTUAL Plan vs. Actual | VARCHAR2(256 CHAR) | Required | This field is used to identify if the row is Planned or Actual.
CUSTOMER_NO Customer Number | VARCHAR2(256 CHAR) | Required | This field contains the exact Customer number for this customer in Agile PLM.
SUPPLIER_NO Supplier Number | VARCHAR2(256 CHAR) | Optional | This field contains the exact Supplier number for this customer in Agile PLM.
SITE Site | VARCHAR2(256 CHAR) | Required | Enter Global if you do not use Sites within Agile PLM. Enter the name of the specific site, if you use Agile PLM and wish to count the Units shipped against a particular site.

### 12.1.7 Product Inventory Quantity

Field | Data Type | Field Required | Field Description
--- | --- | --- | ---
ITEM_NO Item Number | VARCHAR2(256 CHAR) | Required | The exact Item Number in Agile PLM that represents the Product against which the data is being loaded
ERP_ITEM_NO ERP Item Number | VARCHAR2(256 CHAR) | Optional | This field may be used as an external template dimension to bring in the ERP Item number.
TRANSACTION_TYPE Transaction Type | VARCHAR2(256 CHAR) | Optional | This field is not exposed in Oracle Product Lifecycle Analytics.
EXT_TEMPLATE_DATE External Template Date | DATE | Required | This date is used as a date dimension for analyzing the external measures.
REF_NO Reference Number | VARCHAR2(256 CHAR) | Optional | This field may be used as an external template to bring in the reference number from an external source.
NO_OF_UNITS Number of Units | NUMBER(22) | Required | This field contains the number of units.
PLAN_VS_ACTUAL Plan vs. Actual | VARCHAR2(256 CHAR) | Required | This field is used to identify if the row is Planned or Actual.
SUPPLIER_NO Supplier Number | VARCHAR2(256 CHAR) | Optional | This field contains the exact Supplier number for this customer in Agile PLM.
SITE Site | VARCHAR2(256 CHAR) | Required | Enter Global if you do not use Sites within Agile PLM. Enter the name of the specific site, if you use Agile PLM and wish to count the Units shipped against a particular site.

### 12.1.8 Product Inventory Value

Field | Data Type | Field Required | Field Description
--- | --- | --- | ---
ITEM_NO Item Number | VARCHAR2(256 CHAR) | Required | The exact Item Number in Agile PLM that represents the Product against which the data is being loaded
ERP_ITEM_NO ERP Item Number | VARCHAR2(256 CHAR) | Optional | This field may be used as an external template dimension to bring in the ERP Item number.
TRANSACTION_TYPE Transaction Type | VARCHAR2(256 CHAR) | Optional | This field is not exposed in Oracle Product Lifecycle Analytics.
EXT_TEMPLATE_DATE External Template Date | DATE | Required | This date is used as a date dimension for analyzing the external measures.
REF_NO Reference Number | VARCHAR2(256 CHAR) | Optional | This field may be used as an external template to bring in the reference number from an external source.
AMOUNT Amount | NUMBER(22,7) | Required | This field denotes the value for the Item Number referred to in this row.
PLAN_VS_ACTUAL Plan vs. Actual | VARCHAR2(256 CHAR) | Required | This field is used to identify if the row is Planned or Actual.
SUPPLIER_NO Supplier Number | VARCHAR2(256 CHAR) | Optional | This field contains the exact Supplier number for this customer in Agile PLM.
SITE Site | VARCHAR2(256 CHAR) | Required | Enter Global if you do not use Sites within Agile PLM. Enter the name of the specific site, if you use Agile PLM and wish to count the Units shipped against a particular site.

## 12.2 Loading the Data

To load the data:

1. Save the correctly formatted data as a .csv file.

2. Run the ETL.

3. Observe the results in Reports that use external metrics and dimensions.