Actual Costs are extracted from the Cost Distribution Lines table in the Project Costing module in E-Business Suite and loaded into the Cost Line Fact (W_PROJ_COST_LINE_F) table. For E-Business Suite, Transaction Currency is the Document Currency for this fact.
The GL Date is assigned to the Cost Distribution Line only (during Cost distribution) and not to the Expenditure Item records. The Expenditure data can only be analyzed by the Enterprise Calendar dimension and not by the GL calendar. The Expenditure data cannot be analyzed by the GL Account because the GL account is associated only when the data is distributed.
Cost Fact Canonical Date
The Canonical Date dimension for the Cost fact is based on the PRVDR_GL_DATE from Distribution Line table, whereas the Canonical Date dimension for the Expenditure fact is based on the EXPENDITURE_DATE from the Expenditure Items table.
The multi calendar date dimension contains calendars for multiple organizations. It is essential that all records in a report analyzing data by the Fiscal Calendar (Dim - Fiscal Calendar) point to the same calendar. For this reason, all reports in the dashboard are filtered on the Project Business Unit. To make all Cost records in a Project Business Unit point to the same calendar, the RCVR_GL_DATE and RCVR_PA_DATE columns are used to populate the GL_ACCOUNTING_DT_WID and PROJ_ACCOUNTING_DT_WID columns in the fact table respectively. Expenditure OU view (in Cost Fact) can be built using Enterprise Calendar as well.
Domain Values for Cost Fact
The Project Cost Transfer Status has been modeled as a domain value and can be configured in FSM.
Incremental Logic for Cost Fact
The incremental extract logic for the Cost fact table depends on the 'REQUEST_ID' field of the Cost Distribution Lines table. The W_PROJ_ETL_PS parameter table facilitates this logic. Using a separate ODI interface, the maximum Request Id in the source table at the time of the ETL run is stored in this table, which is subsequently used to populate the SDE task (SDE_ORA_PROJECTCOSTLINE) level ODI variable #EBS_REQUEST_ID_1. It is initialized using the following query: SELECT COALESCE((SELECT PRE_REQUEST_ID FROM QUALIFY_DS(W_PROJ_ETL_PS) WHERE TBL_NAME = 'PA_COST_DISTRIBUTION_LINES_ALL'),0) FROM_DUAL()
Note:
If you are missing some Cost records in W_PROJ_COST_LINE_F after an incremental update, download patch 9896800 from My Oracle Support. The Tech Note included with the patch explains the scenarios where this can happen, and the proposed solution.
Configuring the Project Cost Aggregate Table
The Project Cost aggregate table (W_PROJ_COST_A) is used to capture information about the project cost distributions for the expenditure items. You need to configure the Project Cost Lines aggregate table before the initial ETL run and subsequent incremental ETL.Before the initial ETL run, you need to configure the COST_TIME_GRAIN parameter in FSM for the time aggregation level in the Project Cost Lines aggregate fact table.By default, the COST_TIME_GRAIN parameter has a value of PERIOD. The possible values for the COST_TIME_GRAIN parameter are:
PERIOD
QUARTER
YEAR
The Project Cost Lines aggregate table is fully loaded from the base table in the initial ETL run. The table can grow to millions of records. Therefore, the Project Cost aggregate table is not fully reloaded from the base table after each incremental ETL run. The Oracle Business Analytics Warehouse minimizes the incremental aggregation effort by modifying the aggregate table incrementally as the base table is updated.
Oracle Business Analytics Warehouse finds the records to be updated in the base table since the last ETL run, and loads them into the W_PROJ_COST_LINE_TMP table. The measures in these records are multiplied by (-1). The mapping responsible for this task is SIL_ProjectCostLinesFact_Derive_PreLoadImage.
Oracle Business Analytics Warehouse finds the inserted or updated records in the base table since the last ETL run, and loads them into the W_PROJ_COST_LINE_TMP table, without changing their sign. The mapping responsible for this task is SIL_ProjectCostLinesFact_Derive_PreLoadImage, which is run before PLP_ProjectCostLinesFact_Derive_PostLoadImage updates or inserts records in the base table.
Oracle Business Analytics Warehouse aggregates the W_PROJ_COST_LINE_TMP table and load to W_PROJ_COST_A_TMP, which has the same granularity as the W_PROJ_COST_A table.
The PLP_ProjectCostLinesAggregate_Derive mapping looks up the W_PROJ_COST_A aggregate table to update existing buckets or insert new buckets in the aggregate table (the mapping is PLP_ProjectCostLinesAggregate_Load).
Configuring Revenue Fact for E-Business Suite
Actual Revenue Line records are extracted from the Revenue/Event Distribution Lines tables (PA_CUST_REV_DISTRIB_LINES_ALL and PA_CUST_EVENT_DIST_ALL) in the Project Costing module in E-Business Suite and are loaded into the Revenue Line Fact (W_PROJ_REVENUE_LINE_F) table.
For E-Business Suite, Revenue Transaction Currency Code is the Document Currency Code for this fact.
Note:
E-Business Suite concurrent programs (such as PRC: Generate Draft Revenue for a Single Project or PRC: Generate Draft Revenue for a Range of Projects) for distributing revenue should be run before the ETL is run to load Oracle Business Analytics Warehouse.
For the Revenue Header Fact (W_PROJ_REVENUE_HDR_F), the primary source is the PA_DRAFT_REVENUES table. Revenue line metrics, such as Bill and Revenue amounts, are aggregated in this table as well.
Revenue Fact Canonical Date
The Revenue Fact Canonical Date dimension is based on the GL_DATE from the Draft Revenues table.
Revenue Facts Staging Table
The Revenue Facts Staging Table is a common staging table that loads both the header and the line level revenue fact tables.
Revenue Fact Multicurrency Support
Some metrics such as Unearned Revenue, Unbilled Receivables, Realized Gains, and Realized Losses are only available in Local Currency and Global Currencies. There are three columns in w_proj_revenue_line_f and w_proj_revenue_hdr_f respectively for revenue amounts in global currencies.
Revenue Fact Domain Values
The project revenue status has been modeled as a domain value and can be configured in FSM.
Incremental Logic for Revenue Fact
The incremental extract logic for the Revenue fact table depends on the REQUEST_ID field of the Revenue Distribution Lines table. The W_PROJ_ETL_PS parameter facilitates this logic, and through a separate ODI process, the maximum Request Id in the source table at the time of the ETL run is stored in this table, which is subsequently used to populate the following variables for the SDE_ORA_ProjectRevenueLine task in ODI:
#EBS_REQUEST_ID_2
#EBS_REQUEST_ID_3
#EBS_REQUEST_ID_4
They are initialized using the following queries:
SELECT COALESCE((SELECT COALESCE(PRE_REQUEST_ID,0) FROM QUALIFY_DS(W_PROJ_ETL_PS) WHERE TBL_NAME ='PA_CUST_EVENT_RDL_ALL'),0) FROM_DUAL() SELECT COALESCE((SELECT COALESCE(PRE_REQUEST_ID,0) FROM QUALIFY_DS(W_PROJ_ETL_PS) WHERE TBL_NAME ='PA_CUST_REV_DIST_LINES_ALL'),0) FROM_DUAL() SELECT COALESCE((SELECT COALESCE(PRE_REQUEST_ID,0) FROM QUALIFY_DS(W_PROJ_ETL_PS) WHERE TBL_NAME ='PA_DRAFT_REVENUES_ALL'),0) FROM_DUAL()
Configuring the Project Revenue Aggregate Table
The Project Cost aggregate table (W_PROJ_REVENUE_A) is used to capture information about the project revenue distributions. You need to configure the Project Revenue Lines aggregate table before the initial ETL run and subsequent incremental ETL.
Before the initial ETL run, you need to configure the REVENUE_TIME_GRAIN parameter in FSM for the time aggregation level in the Project Revenue Lines aggregate fact table.
By default, the REVENUE _TIME_GRAIN parameter has a value of PERIOD. The possible values for the REVENUE_TIME_GRAIN parameter are:
PERIOD
QUARTER
YEAR
The Project Revenue Lines aggregate table is fully loaded from the base table in the initial ETL run. The table can grow to millions of records. Therefore, the Project Revenue aggregate table is not fully reloaded from the base table after each incremental ETL run. The Oracle Business Analytics Warehouse minimizes the incremental aggregation effort by modifying the aggregate table incrementally as the base table is updated.
The process is as follows:
Oracle Business Analytics Warehouse finds the records to be updated in the base table since the last ETL run, and loads them into the W_PROJ_ REVENUE_LINE_TMP table. The measures in these records are multiplied by (-1). The mapping responsible for this task is SIL_Project RevenueLinesFact_Derive_PreLoadImage.
Oracle Business Analytics Warehouse finds the inserted or updated records in the base table since the last ETL run, and loads them into the W_PROJ_REVENUE_LINE_TMP table, without changing their sign. The mapping responsible for this task is SIL_ProjectRevenueLinesFact_Derive_PreLoadImage, which is run before PLP_ProjectRevenueLinesFact_Derive_PostLoadImage updates or inserts records in the base table.
Oracle Business Analytics Warehouse aggregates the W_PROJ_ REVENUE _LINE_TMP table and load to W_PROJ_REVENUE_A_TMP, which has the same granularity as the W_PROJ_REVENUE_A table.
The PLP_ProjectRevenueLinesAggregate_Derive mapping looks up the W_PROJ_REVENUE_A aggregate table to update existing buckets or insert new buckets in the aggregate table (the mapping is PLP_ProjectRevenueLinesAggregate_Load).