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:
Changing the Time Grain of the Cost Aggregate table to Fiscal/Project/Enterprise Period
Changing the Time Grain of the Revenue Aggregate Table to Fiscal/Project/Enterprise Period
Changing the Time Grain of the Cost Aggregate table to Fiscal/Project/Enterprise Quarter
Changing the Time Grain of the Revenue Aggregate table to Fiscal/Project/Enterprise Quarter
Changing the Time Grain of the Cost Aggregate table to Fiscal/Project/Enterprise Year
Changing the Time grain of the Revenue Aggregate table to Fiscal/Project/Enterprise Year
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 repository 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.
PERIOD
QUARTER
YEAR
You must ensure that the COST_TIME_GRAIN is set to PERIOD in FSM, and that the following repository joins are in place. This is the default configuration.
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.
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_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 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"
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.
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"
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"."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"
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.
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"
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"
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"
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.
Run the Consistency Check and ensure that there are no errors, save the repository 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.
You must ensure that the REVENUE_TIME_GRAIN is set to 'PERIOD' in the FSM and that the following repository joins are in place. This is default configuration.
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.
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_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 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"
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.
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"
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"."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"
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.
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"
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"
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"
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.
Run the Consistency Check and ensure that there are no errors, save the repository 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.
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:
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"
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"
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"
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"
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.
When these changes are complete, run the Consistency Check and ensure that there are no errors, save the repository 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.
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:
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"
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"
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"
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"
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.
When these changes are complete, run the Consistency Check and ensure that there are no errors, save the repository 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.
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:
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"
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"
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"
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.
When these changes are complete, run the Consistency Check and ensure that there are no errors, save the repository 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.
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:
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"
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"
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"
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.
When these changes are complete, run the Consistency Check and ensure that there are no errors, save the repository 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.