Changing the Time Grain for Cost and Revenue

You can configure the grain of Cost aggregate (W_PROJ_COST_A) and Revenue aggregate (W_PROJ_REVENUE_A) to Period, Quarter, or Year. As installed by default, the grain of the cost aggregate and revenue aggregate are set at Fiscal Period.

However, you can modify the grain of the aggregate to either Period or Quarter or Year. This is done by configuring the FSM Parameters - COST_TIME_GRAIN and REVENUE_TIME_GRAIN - to 'PERIOD' or 'QUARTER' or 'YEAR'. In addition, you must make changes to the BI metadata repository (that is, the RPD file) that are included in this section.

Note:

Oracle recommends that you back up the BI metadata repository (that is, the RPD file) before applying making changes.

The tasks to change the time grain parameters are:

Setting the Time Grain Parameters in FSM

By default, the parameters COST_TIME_GRAIN and REVENUE_TIME_GRAIN is set to 'PERIOD'. If you want to change the grain of aggregates, you will have to set these variables to desired levels and concurrently the joins in the RPD should be updated to reflect the appropriate tables.

To change the values in FSM, navigate to Manage Parameters, select 'COST_TIME_GRAIN' and click the Edit button.

  1. Navigate to Manage Parameters.
  2. Select COST_TIME_GRAIN and click the Edit button.
  3. In the Manage Parameter Default values area, specify a value in the Default Value field. The allowed values are:

    • PERIOD

    • QUARTER

    • YEAR

  4. Repeat the above steps for REVENUE_TIME_GRAIN.

Changing the Time Grain of the Cost Aggregate table to Fiscal/Project/Enterprise Period

You must ensure that the COST_TIME_GRAIN is set to PERIOD in FSM, and that the following RPD joins are in place. This is the default configuration.

  1. Verify the joins to Fiscal Calendar (Dim-Date Fiscal Calendar).

    In the Business Model and Mapping layer, select the 'Dim_W_MCAL_PERIOD_D_Fiscal_Period' Logical Table Source from the 'Dim - Date Fiscal Calendar' and the 'Fact_Agg_W_PROJ_COST_A_Project_Cost' and 'Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD' Logical Table Sources in 'Fact - Project Cost' and then right-click and select physical diagram, then selected objects only and verify the following physical joins, then click OK.

    This screen shot is described in surrounding text.
    Description of the illustration GUID-01AE479F-1461-460F-83BC-CB94700E729D-default.gif

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Fiscal_Period"."MCAL_PERIOD_START_DAY_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost"."PRVDR_GL_ACCT_PRD_STRT_DAY_WID"
    This screen shot is described in surrounding text.
    Description of the illustration GUID-4A05A3CC-5FED-4589-BE81-B47534E04CDE-default.gif

    Join B:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Fiscal_Period"."MCAL_PERIOD_END_DAY_WID" >=   "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_GL_ACCT_PRD_END_DAY_WID" AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Fiscal_Period"."MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_GL_MCAL_CAL_WID"
    This screen shot is described in surrounding text.
    Description of the illustration GUID-A998AFC7-9349-45F6-8F66-587178F2E308-default.gif

    Join C:

    "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_GL_ACCT_PRD_END_DAY_WID" <= "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_QTR_D_Fiscal_Quarter"."MCAL_QTR_END_DAY_WID" AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_QTR_D_Fiscal_Quarter"."MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_GL_MCAL_CAL_WID"

    Join D:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_YEAR_D_Fiscal_Year"."MCAL_YEAR_END_DAY_WID" >= "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_GL_ACCT_PRD_END_DAY_WID" AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_YEAR_D_Fiscal_Year"."MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_GL_MCAL_CAL_WID"
  2. Verify the joins to Project Calendar (Dim-Date Project Calendar).

    In the Business Model and Mapping layer, select the 'Dim_W_MCAL_PERIOD_D_Project_Period' Logical Table Source from the 'Dim - Date Project Calendar' and the 'Fact_Agg_W_PROJ_COST_A_Project_Cost' and 'Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD' Logical Table Sources in 'Fact - Project Cost' and then right-click and select physical diagram, then selected objects only and verify the following physical join, then click OK.

    This screen shot is described in surrounding text.
    Description of the illustration GUID-67B5EC18-A4D0-4EB2-8213-A4671D845B08-default.gif

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Project_Period"."MCAL_PERIOD_START_DAY_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost"."PRVDR_PRJ_ACCT_PRD_ST_DAY_WID"
    This screen shot is described in surrounding text.
    Description of the illustration GUID-2A6979A7-7330-4D88-9084-06CCE3D9F4A8-default.gif

    Join B:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Project_Period"."MCAL_PERIOD_END_DAY_WID" >= "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_PRJ_ACCT_PRD_END_DAY_WID" AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Project_Period"."MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_PROJ_MCAL_CAL_WID"
    This screen shot is described in surrounding text.
    Description of the illustration GUID-AAC8007B-DB83-431E-AC77-C1C6577B7CD9-default.gif

    Join C:

    "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_PRJ_ACCT_PRD_END_DAY_WID" <= "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_QTR_D_Project_Quarter"."MCAL_QTR_END_DAY_WID" AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_QTR_D_Project_Quarter"."MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_PROJ_MCAL_CAL_WID"

    Join D:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_YEAR_D_Project_Year"."MCAL_YEAR_END_DAY_WID" >= "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_PRJ_ACCT_PRD_END_DAY_WID" AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_YEAR_D_Project_Year"."MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_PROJ_MCAL_CAL_WID"
  3. Verify the joins to Enterprise Calendar (Dim-Date).

    In the Business Model and Mapping layer, select the 'Dim_W_ENT_PERIOD_D' Logical Table Source from the 'Dim - Date' and the 'Fact_Agg_W_PROJ_COST_A_Project_Cost' and 'Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD' Logical Table Sources in 'Fact - Project Cost' and then right-click and select physical diagram, then selected objects only and verify the following physical join, then click OK.

    This screen shot is described in surrounding text.
    Description of the illustration GUID-EBD752AA-F492-4CD5-86D9-D22D9391D1F9-default.gif

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_PERIOD_D"."ENT_PERIOD_START_DT_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost"."ENT_PERIOD_START_DAY_WID"
    This screen shot is described in surrounding text.
    Description of the illustration GUID-A4878086-E660-4960-AED0-9EEFAD6F0ED9-default.gif

    Join B:

    "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."ENT_PERIOD_END_DAY_WID" <= "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_PERIOD_D"."ENT_PERIOD_END_DT_WID"
    This screen shot is described in surrounding text.
    Description of the illustration GUID-8A6ACB05-AFF6-492F-9D60-4CBE4032B131-default.gif

    Join C:

    "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."ENT_PERIOD_END_DAY_WID" <= "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_QTR_D"."ENT_QTR_END_DT_WID"

    Join D:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_YEAR_D"."ENT_YEAR_END_DT_WID" >= "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."ENT_PERIOD_END_DAY_WID"
  4. Change the Content Aggregation Level in the Business Model and Mapping layer.

    As installed by default, the grain for cost aggregate is set to Period against the dimensions Dim-Date Fiscal Calendar, Dim-Date Project Calendar and Dim - Date. In the Business Model and Mapping layer open these two Logical Table Sources in 'Fact – Project Cost' and verify if grain is set at Period level.

    This screen shot is described in surrounding text.
    Description of the illustration GUID-B2A2AF82-A0CF-412A-B90F-9B2E64C8C20F-default.gif
  5. Save the changes.

    Run the Consistency Check and ensure that there are no errors, save the RPD file, and clear Oracle BI Enterprise Edition Cache. If you are making the changes in offline mode, then restart the Oracle BI Server and Oracle BI Presentation Services.

Changing the Time Grain of the Revenue Aggregate Table to Fiscal/Project/Enterprise Period

You must ensure that the REVENUE_TIME_GRAIN is set to 'PERIOD' in the FSM and that the following RPD joins are in place. This is default configuration.

  1. Verify the joins to Fiscal Calendar (Dim-Date Fiscal Calendar).

    In the Business Model and Mapping layer, select the 'Dim_W_MCAL_PERIOD_D_Fiscal_Period' Logical Table Source from the 'Dim - Date Fiscal Calendar' and the ' Fact_Agg_W_PROJ_REVENUE_A_Revenue' and 'Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD' Logical Table Sources in 'Fact - Project Revenue' and then right-click and select physical diagram, then selected objects only and verify the following physical join, then click OK.

    This screen shot is described in surrounding text.
    Description of the illustration GUID-1502B7C9-914E-4BEB-A9D6-95C111BEA55D-default.gif

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Fiscal_Period"."MCAL_PERIOD_START_DAY_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue"."GL_ACCT_PERIOD_START_DAY_WID"
    This screen shot is described in surrounding text.
    Description of the illustration GUID-532F4AC7-186A-441F-9F5B-98F6F2CE1FD6-default.gif

    Join B:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Fiscal_Period"."MCAL_PERIOD_END_DAY_WID" >= "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."GL_ACCT_PERIOD_END_DAY_WID" AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Fiscal_Period"."MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."GL_MCAL_CAL_WID"
    This screen shot is described in surrounding text.
    Description of the illustration GUID-197875E5-972D-4B84-84DB-18FC640A7CFA-default.gif

    Join C:

    "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."GL_ACCT_PERIOD_END_DAY_WID" <= "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_QTR_D_Fiscal_Quarter"."MCAL_QTR_END_DAY_WID" AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_QTR_D_Fiscal_Quarter"."MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."GL_MCAL_CAL_WID"

    Join D:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_YEAR_D_Fiscal_Year"."MCAL_YEAR_END_DAY_WID" >= "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."GL_ACCT_PERIOD_END_DAY_WID" AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_YEAR_D_Fiscal_Year"."MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."GL_MCAL_CAL_WID"
  2. Verify the joins to Project Calendar (Dim-Date Project Calendar).

    In the Business Model and Mapping layer, select the 'Dim_W_MCAL_PERIOD_D_Project_Period' Logical Table Source from the 'Dim - Date Project Calendar' and the ' Fact_Agg_W_PROJ_REVENUE_A_Revenue' and 'Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD' Logical Table Sources in 'Fact - Project Revenue' and then right-click and select physical diagram, then selected objects only and verify the following physical join, then click OK.

    This screen shot is described in surrounding text.
    Description of the illustration GUID-C981EF59-DFEA-4A08-B201-AB2A2D960FFA-default.gif

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Project_Period"."MCAL_PERIOD_START_DAY_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue"."PROJ_ACCT_PERIOD_START_DAY_WID"
    This screen shot is described in surrounding text.
    Description of the illustration GUID-E5D8E317-225F-4BE5-917D-96CD33130A6A-default.gif

    Join B:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Project_Period"."MCAL_PERIOD_END_DAY_WID" >= "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."PROJ_ACCT_PERIOD_END_DAY_WID" AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Project_Period"."MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."PROJ_MCAL_CAL_WID"
    This screen shot is described in surrounding text.
    Description of the illustration GUID-5F6B8899-D18F-46FD-8DA5-FA85F299E3B6-default.gif

    Join C:

    "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."PROJ_ACCT_PERIOD_END_DAY_WID" <= "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_QTR_D_Project_Quarter"."MCAL_QTR_END_DAY_WID" AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_QTR_D_Project_Quarter"."MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."PROJ_MCAL_CAL_WID"

    Join D:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_YEAR_D_Project_Year"."MCAL_YEAR_END_DAY_WID" >= "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."PROJ_ACCT_PERIOD_END_DAY_WID" AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_YEAR_D_Project_Year"."MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."PROJ_MCAL_CAL_WID"
  3. Verify the joins to Enterprise Calendar (Dim-Date).

    In the Business Model and Mapping layer, select the 'Dim_W_ENT_PERIOD_D' Logical Table Source from the 'Dim - Date' and the 'Fact_Agg_W_PROJ_COST_A_Project_Cost' and the 'Fact_Agg_W_PROJ_REVENUE_A_Revenue' and ' Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD' Logical Table Sources in 'Fact - Project Revenue' and then right-click and select physical diagram, then selected objects only and verify the following physical join, then click OK.

    This screen shot is described in surrounding text.
    Description of the illustration GUID-5B33676F-BAA5-4954-A6C5-B4457D01C11E-default.gif

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_PERIOD_D"."ENT_PERIOD_START_DT_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue"."ENT_PERIOD_START_DAY_WID"
    This screen shot is described in surrounding text.
    Description of the illustration GUID-DC6BA43C-D171-48B8-9C05-B0820A53D9D9-default.gif

    Join B:

    "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."ENT_PERIOD_END_DAY_WID" <= "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_PERIOD_D"."ENT_PERIOD_END_DT_WID"
    This screen shot is described in surrounding text.
    Description of the illustration GUID-0F04F80D-3AB4-4F66-A3F0-2A467D8CA13C-default.gif

    Join C:

    "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."ENT_PERIOD_END_DAY_WID" <= "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_QTR_D"."ENT_QTR_END_DT_WID"

    Join D:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_YEAR_D"."ENT_YEAR_END_DT_WID" >= "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."ENT_PERIOD_END_DAY_WID"
  4. Change the Content Aggregation Level in the Business Model and Mapping layer.

    As installed by default, the grain for cost aggregate is set to Period against the dimensions Dim-Date Fiscal Calendar, Dim-Date Project Calendar and Dim- Date. In the Business Model and Mapping layer open these two Logical Table Sources in 'Fact – Project Revenue' and verify if grain is set at Period level.

    This screen shot is described in surrounding text.
    Description of the illustration GUID-74110534-02C8-455A-B383-C1D928E73297-default.gif
  5. Save the changes.

    Run the Consistency Check and ensure that there are no errors, save the RPD file, and clear Oracle BI Enterprise Edition Cache. If you are making the changes in offline mode, then restart the Oracle BI Server and Oracle BI Presentation Services.

Changing the Time Grain of the Cost Aggregate table to Fiscal/Project/Enterprise Quarter

If the grain of Cost aggregate is at quarter level, then you must ensure that the COST_TIME_GRAIN is set to 'QUARTER' in the FSM.

In addition, make the following metadata changes for the Fiscal, Project, and Enterprise calendars:

  1. Delete the joins to Dim_W_MCAL_PERIOD_D_Fiscal_Period/ Dim_W_MCAL_ PERIOD_D_Project_Period /Dim_W_ENT_ PERIOD_D.

    Delete the existing physical joins between Fact_Agg_W_PROJ_COST_A_Project_Cost (under logical fact 'Fact – Project Cost') to Dim_W_MCAL_PERIOD_D_Fiscal_Period (under logical dimension 'Dim – Date Fiscal Calendar'), Dim_W_MCAL_PERIOD_D_Project_Period (under logical dimension 'Dim – Date Project Calendar') and Dim_W_ENT_PERIOD_D (under logical dimension 'Dim - Date')

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Fiscal_Period"."MCAL_PERIOD_START_DAY_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost"."PRVDR_GL_ACCT_PRD_STRT_DAY_WID"

    Join B:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Project_Period"."MCAL_PERIOD_START_DAY_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost"."PRVDR_PRJ_ACCT_PRD_ST_DAY_WID"

    Join C:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_PERIOD_D"."ENT_PERIOD_START_DT_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost"."ENT_PERIOD_START_DAY_WID"

    Delete the existing physical joins between Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD (under logical fact 'Fact – Project Cost') to Dim_W_MCAL_PERIOD_D_Fiscal_Period (under logical dimension 'Dim – Date Fiscal Calendar'), Dim_W_MCAL_PERIOD_D_Project_Period (under logical dimension 'Dim – Date Project Calendar') and Dim_W_ENT_PERIOD_D (under logical dimension 'Dim - Date').

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Fiscal_Period"."MCAL_PERIOD_END_DAY_WID" >=   "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_GL_ACCT_PRD_END_DAY_WID" AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Fiscal_Period"."MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_GL_MCAL_CAL_WID"

    Join B:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Project_Period"."MCAL_PERIOD_END_DAY_WID" >= "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_PRJ_ACCT_PRD_END_DAY_WID" AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Project_Period"."MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_PROJ_MCAL_CAL_WID"

    Join C:

    "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."ENT_PERIOD_END_DAY_WID" <= "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_PERIOD_D"."ENT_PERIOD_END_DT_WID"
  2. Create join to Dim_W_MCAL_QTR_D_Fiscal_Quarter.

    In the Business Model and Mapping layer, select the Dim_W_MCAL_QTR_D_Project_Quarter/ Dim_W_MCAL_YEAR_D_Project_Year Logical Table Source from the 'Dim - Date Project Calendar' and the Fact_Agg_W_PROJ_COST_A_Project_Cost and Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD Logical Table Sources in 'Fact - Project Cost' and then right click and select 'physical diagram->selected objects only' and click ok. Create following physical join:

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_QTR_D_Fiscal_Quarter"."MCAL_QTR_START_DAY_WID" = 
        "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost"."PRVDR_GL_ACCT_PERIOD_START_DAY_WID"

    And verify the following joins:

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_GL_ACCT_PERIOD_END_DAY_WID" <=   "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_QTR_D_Fiscal_Quarter"."MCAL_QTR_END_DAY_WID" AND 
        "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_GL_MCAL_CAL_WID" = 
        "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_QTR_D_Fiscal_Quarter"."MCAL_CAL_WID"

    Join B:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_YEAR_D_Fiscal_Year"."MCAL_YEAR_END_DAY_WID"
  >= "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_GL_ACCT_PERIOD_END_DAY_WID"
        AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_YEAR_D_Fiscal_Year"."MCAL_CAL_WID" = 
        "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_GL_MCAL_CAL_WID"
  3. Create joins to Dim_W_MCAL_QTR_D_Project_Quarter.

    In the Business Model and Mapping layer, select the Dim_W_MCAL_QTR_D_Project_Quarter/ Dim_W_MCAL_YEAR_D_Project_Year Logical Table Source from the 'Dim - Date Project Calendar' and the Fact_Agg_W_PROJ_COST_A_Project_Cost and Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD Logical Table Sources in 'Fact - Project Cost' and then right click and select 'physical diagram->selected objects only' and click ok. Create following physical join:

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_QTR_D_Project_Quarter"."MCAL_QTR_START_DAY_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost"."PRVDR_PRJ_ACCT_PRD_START_DAY_WID"

    And verify the following joins:

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_PRJ_ACCT_PRD_END_DAY_WID" <= "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_QTR_D_Project_Quarter"."MCAL_QTR_END_DAY_WID" 
AND "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_PROJ_MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_QTR_D_Project_Quarter"."MCAL_CAL_WID"

    Join B:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_YEAR_D_Project_Year"."MCAL_YEAR_END_DAY_WID" >= 
"Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_PRJ_ACCT_PRD_END_DAY_WID" 
AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_YEAR_D_Project_Year"."MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_PROJ_MCAL_CAL_WID"
  4. Create joins to Dim_W_ENT_QTR_D.

    In the Business Model and Mapping layer, select the Dim_W_ENT_QTR_D / Dim_W_ENT_YEAR_D Logical Table Source from the 'Dim - Date' and the Fact_Agg_W_PROJ_COST_A_Project_Cost and Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD Logical Table Sources in 'Fact - Project Cost' and then right click and select 'physical diagram->selected objects only' and click ok. Create following physical join:

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_QTR_D"."ENT_QTR_START_DT_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost"."ENT_PERIOD_START_DAY_WID"

    And verify following joins:

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."ENT_PERIOD_END_DAY_WID" <=  
        "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_QTR_D"."ENT_QTR_END_DT_WID"

    Join B:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_YEAR_D"."ENT_YEAR_END_DT_WID" >= 
        "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."ENT_PERIOD_END_DAY_WID"
  5. Change the Content Aggregation Level in the Business Model and Mapping layer.

    As installed by default, the grain for cost aggregate is set to Period against the dimensions Dim-Date Fiscal Calendar, Dim-Date Project Calendar and Dim - Date.

    Instead of Fiscal/Project/Enterprise Period you must set this to Fiscal Quarter for Dim – Date Fiscal Calendar, Project Quarter for Dim – Date Project Calendar and Enterprise Quarter for Dim - Date.

  6. Save the changes.

    When these changes are complete, run the Consistency Check and ensure that there are no errors, save the RPD file, and clear the Oracle BI Enterprise Edition Cache. If you are making the changes in offline mode, then restart the Oracle BI Server and Oracle BI Presentation Services.

Changing the Time Grain of the Revenue Aggregate table to Fiscal/Project/Enterprise Quarter

If the grain of Revenue aggregate is at quarter level, then you must ensure that the REVENUE_TIME_GRAIN is set to 'QUARTER' in FSM.

Also, the following metadata changes should be made for the Fiscal, Project, and Enterprise calendars:

  1. Delete the joins to Dim_W_MCAL_PERIOD_D_Fiscal_Period/ Dim_W_MCAL_ PERIOD_D_Project_Period /Dim_W_ENT_ PERIOD_D.

    Delete the existing physical joins between Fact_Agg_W_PROJ_REVENUE_A_Revenue (under logical fact 'Fact – Project Revenue') to Dim_W_MCAL_ PERIOD_D_Fiscal_Period (under logical dimension 'Dim – Date Fiscal Calendar'), Dim_W_MCAL_ PERIOD_D_Project_Period (under logical dimension 'Dim – Date Project Calendar') and Dim_W_ENT_ PERIOD_D (under logical dimension 'Dim - Date').

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Fiscal_Period"."MCAL_PERIOD_START_DAY_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue"."GL_ACCT_PERIOD_START_DAY_WID"

    Join B:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Project_Period"."MCAL_PERIOD_START_DAY_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue"."PROJ_ACCT_PERIOD_START_DAY_WID"

    Join C:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_PERIOD_D"."ENT_PERIOD_START_DT_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue"."ENT_PERIOD_START_DAY_WID"

    Delete the existing physical joins between Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD (under logical fact 'Fact – Project Revenue') to Dim_W_MCAL_PERIOD_D_Fiscal_Period (under logical dimension 'Dim – Date Fiscal Calendar'), Dim_W_MCAL_PERIOD_D_Project_Period (under logical dimension 'Dim – Date Project Calendar') and Dim_W_ENT_PERIOD_D (under logical dimension 'Dim - Date').

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Fiscal_Period"."MCAL_PERIOD_END_DAY_WID" >= "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."GL_ACCT_PERIOD_END_DAY_WID" AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Fiscal_Period"."MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."GL_MCAL_CAL_WID"

    Join B:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Project_Period"."MCAL_PERIOD_END_DAY_WID" >= "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."PROJ_ACCT_PERIOD_END_DAY_WID" AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Project_Period"."MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."PROJ_MCAL_CAL_WID"

    Join C:

    "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."ENT_PERIOD_END_DAY_WID" <= "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_PERIOD_D"."ENT_PERIOD_END_DT_WID"
  2. Create joins to Dim_W_MCAL_QTR_D_Fiscal_Quarter.

    In the Business Model and Mapping layer, select the Dim_W_MCAL_QTR_D_Fiscal_Quarter/ Dim_W_MCAL_YEAR_D_Fiscal_Year Logical Table Source from the 'Dim - Date Fiscal Calendar' and the Fact_Agg_W_PROJ_REVENUE_A_Revenue and Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD Logical Table Sources in 'Fact - Project Revenue' and then right click and select 'physical diagram-> selected objects only' and click ok. Create following physical join:

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_QTR_D_Fiscal_Quarter"."MCAL_PERIOD_START_DAY_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue"."GL_ACCT_PERIOD_START_DAY_WID"

    And verify the following joins:

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."GL_ACCT_PERIOD_END_DAY_WID" <= "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_QTR_D_Fiscal_Quarter"."MCAL_QTR_END_DAY_WID" AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_QTR_D_Fiscal_Quarter"."MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."GL_MCAL_CAL_WID"

    Join B:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_YEAR_D_Fiscal_Year"."MCAL_YEAR_END_DAY_WID" >= "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."GL_ACCT_PERIOD_END_DAY_WID" AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_YEAR_D_Fiscal_Year"."MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."GL_MCAL_CAL_WID"
  3. Create joins to Dim_W_MCAL_QTR_D_Project_Quarter.

    In the Business Model and Mapping layer, select the Dim_W_MCAL_QTR_D_Project_Quarter/ Dim_W_MCAL_YEAR_D_Project_Year Logical Table Source from the 'Dim - Date Project Calendar' and the Fact_Agg_W_PROJ_REVENUE_A_Revenue and Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD_Logical Table Sources in 'Fact - Project Revenue' and then right click and select 'physical diagram->selected objects only' and click ok. Create following physical join:

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_QTR_D_Project_Quarter"."MCAL_QTR_START_DAY_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue"."PROJ_ACCT_PERIOD_START_DAY_WID"

    And verify the following joins:

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."PROJ_ACCT_PERIOD_END_DAY_WID" <= "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_QTR_D_Project_Quarter"."MCAL_QTR_END_DAY_WID" AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_QTR_D_Project_Quarter"."MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."PROJ_MCAL_CAL_WID"

    Join B:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_YEAR_D_Project_Year"."MCAL_YEAR_END_DAY_WID" >= "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."PROJ_ACCT_PERIOD_END_DAY_WID" AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_YEAR_D_Project_Year"."MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."PROJ_MCAL_CAL_WID"
  4. Create joins to Dim_W_ENT_QTR_D.

    In the Business Model and Mapping layer, select the Dim_W_ENT_QTR_D / Dim_W_ENT_YEAR_D Logical Table Source from the 'Dim - Date' and the Fact_Agg_W_PROJ_REVENUE_A_Revenue and Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD_ Logical Table Sources in 'Fact - Project Revenue' and then right click and select 'physical diagram->selected objects only' and click ok. Create following physical join:

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_QTR_D"."ENT_QTR_START_DT_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue"."ENT_PERIOD_START_DAY_WID"

    And verify the following joins:

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."ENT_PERIOD_END_DAY_WID" <= "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_QTR_D"."ENT_QTR_END_DT_WID"

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_YEAR_D"."ENT_YEAR_END_DT_WID" >= "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."ENT_PERIOD_END_DAY_WID"
  5. Changing Content Aggregation Level in the Business Model and Mapping layer.

    As installed by default, the grain for revenue aggregate is set to Period against the dimensions Dim-Date Fiscal Calendar, Dim-Date Project Calendar and Dim - Date.

    Instead of Fiscal/Project Period you must set this to Fiscal Quarter for Dim – Date Fiscal Calendar, Project Quarter for Dim – Date Project Calendar and Enterprise Quarter for Dim - Date.

  6. Save the changes.

    When these changes are complete, run the Consistency Check and ensure that there are no errors, save the RPD file, and clear the Oracle BI Enterprise Edition Cache. If you are making the changes in offline mode, then restart the Oracle BI Server and Oracle BI Presentation Services.

Changing the Time Grain of the Cost Aggregate table to Fiscal/Project/Enterprise Year

If the grain of Cost aggregate is at year level, then you must ensure that the COST_TIME_GRAIN is set to 'YEAR' in FSM.

Also, the following metadata changes should be made for the Fiscal, Project, and Enterprise calendars:

  1. Delete the joins to Dim_W_MCAL_PERIOD_D_Fiscal_Period/ Dim_W_MCAL_ PERIOD_D_Project_Period /Dim_W_ENT_ PERIOD_D.

    Delete the existing physical joins between Fact_Agg_W_PROJ_COST_A_Project_Cost (under logical fact 'Fact – Project Cost') to Dim_W_MCAL_PERIOD_D_Fiscal_Period (under logical dimension 'Dim – Date Fiscal Calendar'), Dim_W_MCAL_PERIOD_D_Project_Period (under logical dimension 'Dim – Date Project Calendar') and Dim_W_ENT_PERIOD_D (under logical dimension 'Dim - Date').

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Fiscal_Period"."MCAL_PERIOD_START_DAY_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost"."PRVDR_GL_ACCT_PRD_STRT_DAY_WID"

    Join B:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Project_Period"."MCAL_PERIOD_START_DAY_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost"."PRVDR_PRJ_ACCT_PRD_ST_DAY_WID"

    Join C:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_PERIOD_D"."ENT_PERIOD_START_DT_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost"."ENT_PERIOD_START_DAY_WID"

    Delete the existing physical joins between Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD (under logical fact 'Fact – Project Cost') to Dim_W_MCAL_PERIOD_D_Fiscal_Period (under logical dimension 'Dim – Date Fiscal Calendar'), Dim_W_MCAL_PERIOD_D_Project_Period (under logical dimension 'Dim – Date Project Calendar') and Dim_W_ENT_PERIOD_D (under logical dimension 'Dim - Date').

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Fiscal_Period"."MCAL_PERIOD_END_DAY_WID" >=   "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_GL_ACCT_PRD_END_DAY_WID" AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Fiscal_Period"."MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_GL_MCAL_CAL_WID"

    Join B:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Project_Period"."MCAL_PERIOD_END_DAY_WID" >= "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_PRJ_ACCT_PRD_END_DAY_WID" AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Project_Period"."MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_PROJ_MCAL_CAL_WID"

    Join C:

    "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."ENT_PERIOD_END_DAY_WID" <= "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_PERIOD_D"."ENT_PERIOD_END_DT_WID"
  2. Create joins to Dim_W_MCAL_YEAR_D_Fiscal_Year/ Dim_W_MCAL_YEAR_D_Project_Year/ Dim_W_ENT_YEAR_D.

    Following physical joins need to be created between following Logical Table Source fact Fact_Agg_W_PROJ_COST_A_Project_Cost (under logical fact 'Fact – Project Cost') and Dim_W_MCAL_YEAR_D_Fiscal_Year (under logical dimension 'Dim – Date Fiscal Calendar'), Dim_W_MCAL_YEAR_D_Project_Year (under logical dimension 'Dim – Date Project Calendar') and Dim_W_ENT_YEAR_D (under logical dimension 'Dim – Date').

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_YEAR_D_Fiscal_Year"."MCAL_YEAR_START_DAY_WID" =    
        "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost"."PRVDR_GL_ACCT_PERIOD_START_DAY_WID"

    Join B:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_YEAR_D_Project_Year"."MCAL_YEAR_START_DAY_WID" =    
        "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost"."PRVDR_PRJ_ACCT_PRD_START_DAY_WID"

    Join C:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_YEAR_D"."ENT_YEAR_START_DT_WID" = 
        "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost"."ENT_PERIOD_START_DAY_WID"
  3. Verify the joins to Dim_W_MCAL_YEAR_D_Fiscal_Year/ Dim_W_MCAL_YEAR_D_Project_Year/ Dim_W_ENT_YEAR_D.

    Ensure that there are joins between Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD Logical Table Source in 'Fact - Project Cost' to Dim_W_MCAL_YEAR_D_Fiscal_Year Logical Table Source from the 'Dim - Date Fiscal Calendar', Dim_W_MCAL_YEAR_D_Project_Year Logical Table Source from the 'Dim - Date Project Calendar' and Dim_W_ENT_YEAR_D Logical Table Source from the 'Dim - Date'. These are done by default.

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_YEAR_D_Fiscal_Year"."MCAL_YEAR_END_DAY_WID" >= "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_GL_ACCT_PRD_END_DAY_WID" AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_YEAR_D_Fiscal_Year"."MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_GL_MCAL_CAL_WID"

    Join B:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_YEAR_D_Project_Year"."MCAL_YEAR_END_DAY_WID" >= "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_PRJ_ACCT_PRD_END_DAY_WID" AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_YEAR_D_Project_Year"."MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."PRVDR_PROJ_MCAL_CAL_WID"

    Join C:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_YEAR_D"."ENT_YEAR_END_DT_WID" >= "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_COST_A_Project_Cost_ITD"."ENT_PERIOD_END_DAY_WID"
  4. Change the Content Aggregation Level in the Business Model and Mapping layer.

    As installed by default, the grain for cost aggregate is set to Period against the dimensions Dim-Date Fiscal Calendar, Dim-Date Project Calendar and Dim - Date.

    Instead of Fiscal/Project Period you must set this to Fiscal Year for Dim – Date Fiscal Calendar, Project Year for Dim – Date Project Calendar and Enterprise year for Dim - Date.

  5. Save the changes.

    When these changes are complete, run the Consistency Check and ensure that there are no errors, save the RPD file, and clear the Oracle BI Enterprise Edition Cache. If you are making the changes in offline mode, then restart the Oracle BI Server and Oracle BI Presentation Services.

Changing the Time Grain of the Revenue Aggregate table to Fiscal/Project/Enterprise Year

If the grain of Revenue aggregate is at year level, then you must ensure that the REVENUE_TIME_GRAIN is set to 'YEAR' in the FSM.

Also, the following metadata changes should be made for the Fiscal, Project, and Enterprise calendars:

  1. Delete the joins to Dim_W_MCAL_PERIOD_D_Fiscal_Period/ Dim_W_MCAL_ PERIOD_D_Project_Period /Dim_W_ENT_ PERIOD_D.

    Delete the existing physical joins between Fact_Agg_W_PROJ_REVENUE_A_Revenue (under logical fact 'Fact – Project Revenue') to Dim_W_MCAL_ PERIOD_D_Fiscal_Period (under logical dimension 'Dim – Date Fiscal Calendar'), Dim_W_MCAL_ PERIOD_D_Project_Period (under logical dimension 'Dim – Date Project Calendar') and Dim_W_ENT_ PERIOD_D (under logical dimension 'Dim - Date').

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Fiscal_Period"."MCAL_PERIOD_START_DAY_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue"."GL_ACCT_PERIOD_START_DAY_WID"

    Join B:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Project_Period"."MCAL_PERIOD_START_DAY_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue"."PROJ_ACCT_PERIOD_START_DAY_WID"

    Join C:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_PERIOD_D"."ENT_PERIOD_START_DT_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue"."ENT_PERIOD_START_DAY_WID"

    Delete the existing physical joins between Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD (under logical fact 'Fact – Project Revenue') to Dim_W_MCAL_PERIOD_D_Fiscal_Period (under logical dimension 'Dim – Date Fiscal Calendar'), Dim_W_MCAL_PERIOD_D_Project_Period (under logical dimension 'Dim – Date Project Calendar') and Dim_W_ENT_PERIOD_D (under logical dimension 'Dim - Date').

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Fiscal_Period"."MCAL_PERIOD_END_DAY_WID" >= "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."GL_ACCT_PERIOD_END_DAY_WID" AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Fiscal_Period"."MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."GL_MCAL_CAL_WID"

    Join B:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Project_Period"."MCAL_PERIOD_END_DAY_WID" >= "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."PROJ_ACCT_PERIOD_END_DAY_WID" AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_PERIOD_D_Project_Period"."MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."PROJ_MCAL_CAL_WID"

    Join C:

    "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."ENT_PERIOD_END_DAY_WID" <= "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_PERIOD_D"."ENT_PERIOD_END_DT_WID"
  2. Create joins to Dim_W_MCAL_YEAR_D_Fiscal_Year/ Dim_W_MCAL_YEAR_D_Project_Year.

    Additional physical joins need to be created between following Logical Table Source fact Fact_Agg_W_PROJ_REVENUE_A_Revenue (under logical fact 'Fact – Project Cost') and Dim_W_MCAL_YEAR_D_Fiscal_Year (under logical dimension 'Dim – Date Fiscal Calendar'), Dim_W_MCAL_YEAR_D_Project_Year (under logical dimension 'Dim – Date Project Calendar') and Dim_W_ENT_YEAR_D (under logical dimension 'Dim – Date').

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_YEAR_D_Fiscal_Year"."MCAL_YEAR_START_DAY_WID" =    
"Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue"."GL_ACCT_PERIOD_START_DAY_WID"

    Join B:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_YEAR_D_Project_Year"."MCAL_YEAR_START_DAY_WID" =    
"Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue"."PROJ_ACCT_PERIOD_START_DAY_WID"

    Join C:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_YEAR_D"."ENT_YEAR_START_DT_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue"."ENT_PERIOD_START_DAY_WID"
  3. Verify the joins to Dim_W_MCAL_YEAR_D_Fiscal_Year/ Dim_W_MCAL_YEAR_D_Project_Year.

    Ensure that there are joins between Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD Logical Table Source in 'Fact - Project Revenue' to Dim_W_MCAL_YEAR_D_Fiscal_Year Logical Table Source from the 'Dim - Date Fiscal Calendar', Dim_W_MCAL_YEAR_D_Project_Year Logical Table Source from the 'Dim - Date Project Calendar' and Dim_W_ENT_YEAR_D Logical Table Source from the 'Dim - Date'. These are done by default.

    Join A:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_YEAR_D_Fiscal_Year"."MCAL_YEAR_END_DAY_WID" >= "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."GL_ACCT_PERIOD_END_DAY_WID" AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_YEAR_D_Fiscal_Year"."MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."GL_MCAL_CAL_WID"

    Join B:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_YEAR_D_Project_Year"."MCAL_YEAR_END_DAY_WID" >= "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."PROJ_ACCT_PERIOD_END_DAY_WID" AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_MCAL_YEAR_D_Project_Year"."MCAL_CAL_WID" = "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."PROJ_MCAL_CAL_WID"

    Join C:

    "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_ENT_YEAR_D"."ENT_YEAR_END_DT_WID" >= "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_PROJ_REVENUE_A_Revenue_ITD"."ENT_PERIOD_END_DAY_WID"
  4. Changing Content Aggregation Level in the Business Model and Mapping layer

    As installed by default, the grain for revenue aggregate is set to Period against the dimensions Dim-Date Fiscal Calendar, Dim-Date Project Calendar and Dim - Date.

    Instead of Fiscal/Project Period you must set this to Fiscal Year for Dim – Date Fiscal Calendar and Project Year for Dim – Date Project Calendar and Enterprise year for Dim - Date.

  5. Save the changes.

    When these changes are complete, run the Consistency Check and ensure that there are no errors, save the RPD file, and clear the Oracle BI Enterprise Edition Cache. If you are making the changes in offline mode, then restart the Oracle BI Server and Oracle BI Presentation Services.