project-accounting-cube-projcube-for-microsoft-dynamics-ax-2012-r2-and-r3.md

title	TOCTitle	ms:assetid	ms:mtpsurl	ms:contentKeyID	author	ms.date	mtps_version
Project accounting cube (ProjCube) for Microsoft Dynamics AX 2012 R2 and R3	Project accounting cube (ProjCube)	61eb1a13-eb8f-407f-a9d3-1cce32ffda1a	https://technet.microsoft.com/library/JJ710391(v=AX.60)	49384284	tonyafehr	07/23/2014	v=AX.60

Project accounting cube (ProjCube) for Microsoft Dynamics AX 2012 R2 and R3

[!INCLUDEarchive-banner]

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2

The Project accounting cube for Microsoft Dynamics AX is used to report on project profitability, revenue costs, committed costs, employee utilization, and cash flow for one or multiple projects in a company. This article provides details about the cube.

Deployment Configuration keys Tables and views Measures Calculated measures Key performance indicators Security

Analytics in Microsoft Dynamics AX Cube and KPI reference for Microsoft Dynamics AX 2012 R2 and R3 Cube and KPI reference for Microsoft Dynamics AX 2012 and Microsoft Dynamics AX 2012 Feature Pack

Deployment

The Project accounting cube is included in the Dynamics AX project. For information about how deploy the Dynamics AX project—and the cubes that it contains—see Deploy the default cubes.

Configuration keys

The following configuration keys are required to use all features of the Project accounting cube:

• Project I (ProjBasic)

• Project II (ProjAdvanced)

Tables and views

The Project accounting cube uses data from the following tables and views:

• CustGroup table

• ForecastModel table

• ProjCategory table

• ProjFundingSource table

• ProjGroup table

• ProjInvoiceTable table

• CostControlTransCommittedCostCube view

• InventTableExpanded view

• ProjActivityCompletedCostView view

• ProjBIEmplTrans view

• ProjBIForecastEmpl view

• ProjProjectEstimatesView view

• ProjProjectPlannedCostView view

• ProjProjectPlannedEffortView view

• ProjTransBudgetCube view

• ProjTransPostingCube view

• smmActivitiesCube view

Note

The ProjActivityCompletedCostView, ProjProjectEstimatesView, ProjProjectPlannedCostView, and ProjProjectPlannedEffortView views are used with this cube only in Microsoft Dynamics AX 2012 R3.

Measures

The Project accounting cube includes the following measure groups.

Actuals

This measure group is based on the ProjTransPostingCube view and includes the following measures.

Measure	Measure field name	Aggregation	Description	Associated dimensions
Actuals count	Not applicable	Count	The number of project actual transactions.	Company Customer Released products Activity Project funding source Project category Project groups Project contracts Project Activity Completed Cost View Project Estimates View Project Planned Costs View Project Planned Effort view Projects Worker Actuals Date (ledger date) Date (project transaction date) Date (exchange rate date) Note The Project groups, Project Activity Completed Cost View, Project Estimates View, Project Planned Costs View, and Project Planned Effort view dimensions are associated with this measure only in Microsoft Dynamics AX 2012 R3.
Amount	ProjTransPostingCube.AmountMST	Sum	The sum of the value, in the accounting currency, of project transactions.
Actual accrued loss	ProjTransPostingCube.ActualAccruedLoss	Sum	The sum of the value, in the accounting currency, of project accrued loss transactions.
Actual accrued revenue	ProjTransPostingCube.ActualAccruedRevenue	Sum	The sum of the value, in the accounting currency, of project accrued revenue transactions.
Actual cash inflow	ProjTransPostingCube.ActualCashInflow	Sum	The sum of the value, in the accounting currency, of project cash inflow transactions.
Actual cash outflow	ProjTransPostingCube.ActualCashOutflow	Sum	The sum of the value, in the accounting currency, of project cash outflow transactions.
Actual consumption	ProjTransPostingCube.ActualConsumption	Sum	The sum of the value, in the accounting currency, of project consumption transactions.
Actual consumption cost expense	ProjTransPostingCube.ActualConsumptionCostExpense	Sum	The sum of the value, in the accounting currency, of project expense cost consumption transactions.
Actual consumption cost hours	ProjTransPostingCube.ActualConsumptionCostHours	Sum	The sum of the value, in the accounting currency, of project hour cost consumption transactions.
Actual consumption cost item	ProjTransPostingCube.ActualConsumptionCostItem	Sum	The sum of the value, in the accounting currency, of project item cost consumption transactions.
Actual cost	ProjTransPostingCube.ActualCost	Sum	The sum of the value, in the accounting currency, of project actual cost transactions.
Actual cost committed	ProjTransPostingCube.ActualCostCommitted	Sum	The sum of the value, in the accounting currency, of project committed cost transactions.
Actual cost expense	ProjTransPostingCube.ActualCostExpense	Sum	The sum of the value, in the accounting currency, of project expense cost transactions.
Actual cost hour	ProjTransPostingCube.ActualCostHour	Sum	The sum of the value, in the accounting currency, of project hours cost transactions.
Actual cost item	ProjTransPostingCube.ActualCostItem	Sum	The sum of the value, in the accounting currency, of project item costs transactions.
Actual gross margin	ProjTransPostingCube.ActualGrossMargin	Sum	The sum of the value, in the accounting currency, of project revenue less project cost transactions.
Actual gross WIP cost	ProjTransPostingCube.ActualGrossWIPCost	Sum	The sum of the value, in the accounting currency, of project work in progress revenue and project work in progress cost transactions.
Actual hours	ProjTransPostingCube.ActualHours	Sum	The sum of the hours logged against projects.
Actual invoiced on account	ProjTransPostingCube.ActualInvoicedOnAccount	Sum	The sum of the value, in the accounting currency, of project invoiced on account revenue transactions.
Actual invoiced revenue	ProjTransPostingCube.ActualInvoicedRevenue	Sum	The sum of the value, in the accounting currency, of project invoiced revenue transactions.
Actual net cash flow	ProjTransPostingCube.ActualNetCashflow	Sum	The sum of the value, in the accounting currency, of project cash inflow less project cash outflow transactions.
Actual net WIP	ProjTransPostingCube.ActualNetWIP	Sum	The sum of the value, in the accounting currency, of gross project work in progress revenue, less project work in progress on-account transactions.
Actual payroll allocation	ProjTransPostingCube.ActualPayrollAllocation	Sum	The sum of the value, in the accounting currency, of project payroll allocation transactions.
Actual revenue	ProjTransPostingCube.ActualRevenue	Sum	The sum of the value, in the accounting currency, of project revenue transactions.
Actual WIP cost	ProjTransPostingCube.ActualWIPCost	Sum	The sum of the value, in the accounting currency, of project work in progress cost transactions.
Actual WIP invoiced on account	ProjTransPostingCube.ActualWIPInvoicedOnAccount	Sum	The sum of the value, in the accounting currency, of project invoiced work in progress on account transactions.
Actual WIP sales	ProjTransPostingCube.ActualWIPSales	Sum	The sum of the value, in the accounting currency, of project work in progress sales transactions.

Committed costs

This measure group is based on the CostControlTransCommittedCostCube view and includes the following measures.

Measure	Measure field name	Aggregation	Description	Associated dimensions
Committed costs count	Not applicable	Count	The number of committed cost transactions.	Currency Company Customer Activity Vendor Project category Project contracts Project Activity Completed Cost View Project Estimates View Project Planned Costs View Project Planned Effort view Projects Worker Committed costs Date (committed date) Date (exchange rate date) Note The Project Activity Completed Cost View, Project Estimates View, Project Planned Costs View, and Project Planned Effort view dimensions are associated with this measure only in Microsoft Dynamics AX 2012 R3.
Committed cost	CostControlTransCommittedCostCube.CommittedCost	Sum	The total value of committed cost transactions, in the accounting currency.
Committed amount	CostControlTransCommittedCostCube.AmountMST	Sum	The total value of committed cost transactions, in the accounting currency.
Committed cost updates quantity	CostControlTransCommittedCostCube.Qty	Sum	The sum of the quantity for committed item cost transactions.
Committed hour cost	CostControlTransCommittedCostCube.CommittedHourCost	Sum	The sum of committed hour costs, in the accounting currency.
Committed item cost	CostControlTransCommittedCostCube.CommittedItemCost	Sum	The sum of committed item costs, in the accounting currency.

Actual hour utilization

This measure group is based on the ProjBIEmplTrans view and includes the following measures.

Measure	Measure field name	Aggregation	Description	Associated dimensions
Actual hour utilization count	Not applicable	Count	The number of actual hour transactions.	Currency Company Customer Activity Project category Project contracts Project Activity Completed Cost View Project Estimates View Project Planned Costs View Project Planned Effort view Projects Worker Date (ledger date) Date (project transaction date) Note The Project Activity Completed Cost View, Project Estimates View, Project Planned Costs View, and Project Planned Effort view dimensions are associated with this measure only in Microsoft Dynamics AX 2012 R3.
Actual efficiency burden hours	ProjBIEmplTrans.ActualBurdenEfficiencyRate	Sum	The number of burden hours using the efficiency calculation method.
Actual efficiency utilized hours	ProjBIEmplTrans.ActualUtilizationEfficiencyRate	Sum	The number of utilized hours logged using the efficiency calculation method.
Actual billable utilized hours	ProjBIEmplTrans.ActualUtilizationBillableRate	Sum	The number of utilized hours logged using the billable calculation method.
Actual billable burden hours	ProjBIEmplTrans.ActualBurdenBillableRate	Sum	The number of burden hours logged using the billable calculation method.
Actual efficiency overtime hours	ProjBIEmplTrans.ActualOvertimeHoursEfficiencyRate	Sum	The number of overtime hours logged using the efficiency calculation method.
Actual billable overtime hours	ProjBIEmplTrans.ActualOvertimeHoursBillableRate	Sum	The number of overtime hours logged using the billable calculation method.
Hours quantity	ProjBIEmplTrans.Qty	Sum	The total number of hours logged.

Forecast hour utilization

This measure group is based on the ProjBIForecastEmpl view and includes the following measures.

Measure	Measure field name	Aggregation	Description	Associated dimensions
Forecast hour utilization count	Not applicable	Count	The number of forecast hour transactions.	Currency Company Customer Activity Forecast models Project category Project contracts Project Activity Completed Cost View Project Estimates View Project Planned Costs View Project Planned Effort view Projects Worker Date (ledger date) Date (project transaction date) Note The Project Activity Completed Cost View, Project Estimates View, Project Planned Costs View, and Project Planned Effort view dimensions are associated with this measure only in Microsoft Dynamics AX 2012 R3.
Hour forecast quantity	ProjBIForecastEmpl.Qty	Sum	The total forecast hours.
Budget efficiency utilized hours	ProjBIForecastEmpl.BudgetUtilizationEfficiencyRate	Sum	The number of utilized hours forecast using the efficiency calculation method.
Budget efficiency burden hours	ProjBIForecastEmpl.BudgetBurdenEfficiencyRate	Sum	The number of burden hours forecast using the efficiency calculation method.
Hour forecast cost price	ProjBIForecastEmpl.CostPrice	Sum	The cost price per hour for forecasted hours.
Budget billable utilized hours	ProjBIForecastEmpl.BudgetUtilizationBillableRate	Sum	The number of utilized hours forecast using the billable calculation method.
Budget billable burden hours	ProjBIForecastEmpl.BudgetBurdenBillableRate	Sum	The number of burden hours forecast using the billable calculation method.
Budget billable overtime hours	ProjBIForecastEmpl.BudgetOvertimeHoursBillableRate	Sum	The number of overtime hours forecast using the billable calculation method.
Budget efficiency overtime hours	ProjBIForecastEmpl.BudgetOvertimeHoursEfficiencyRate	Sum	The number of overtime hours forecast using the efficiency calculation method.

Forecasts

This measure group is based on the ProjTransBudgetCube view and includes the following measures.

Measures	Measure field name	Aggregation	Description	Associated dimensions
Forecasts count	Not applicable	Count	The number of forecast transactions.	Company Customer Released products Activity Forecast models Project funding source Project category Project groups Project contracts Project Activity Completed Cost View Project Estimates View Project Planned Costs View Project Planned Effort view Projects Worker Forecasts Date (ledger date) Date (project transaction date) Date (exchange rate date) Note The Project groups, Project Activity Completed Cost View, Project Estimates View, Project Planned Costs View, and Project Planned Effort view dimensions are associated with this measure only in Microsoft Dynamics AX 2012 R3.
Budget updates quantity	ProjTransBudgetCube.Qty	Sum	The sum of quantities for forecast item transactions.
Budget invoiced revenue	ProjTransBudgetCube.BudgetInvoiceRevenue	Sum	The sum of the value, in the accounting currency, of forecast invoiced revenue transactions.
Budget updates amount	ProjTransBudgetCube.AmountMST	Sum	The sum of the value, in the accounting currency, of forecast transactions.
Budget accrued revenue	ProjTransBudgetCube.BudgetAccruedRevenue	Sum	The sum of the value, in the accounting currency, of forecast accrued revenue transactions.
Budget revenue	ProjTransBudgetCube.BudgetRevenue	Sum	The sum of the value, in the accounting currency, of forecast revenue transactions.
Budget cost hour	ProjTransBudgetCube.BudgetCostHour	Sum	The sum of the value, in the accounting currency, of forecast hour cost transactions.
Budget cost expense	ProjTransBudgetCube.BudgetCostExpense	Sum	The sum of the value, in the accounting currency, of forecast expense cost transactions.
Budget cost item	ProjTransBudgetCube.BudgetCostItem	Sum	The sum of the value, in the accounting currency, of forecast item cost transactions.
Budget cost	ProjTransBudgetCube.BudgetCost	Sum	The sum of the value, in the accounting currency, of forecast cost transactions.
Budget WIP cost	ProjTransBudgetCube.BudgetWIPCost	Sum	The sum of the value, in the accounting currency, of forecast work in progress cost transactions.
Budget WIP sales	ProjTransBudgetCube.BudgetWIPSales	Sum	The sum of the value, in the accounting currency, of forecast work in progress sales transactions.
Budget hours	ProjTransBudgetCube.BudgetHours	Sum	The sum of the value, in the accounting currency, of forecast hour transactions.
Budget consumption cost hours	ProjTransBudgetCube.BudgetConsumptionCostHours	Sum	The sum of the value, in the accounting currency, of forecast hour consumption cost transactions.
Budget consumption cost expense	ProjTransBudgetCube.BudgetConsumptionCostExpense	Sum	The sum of the value, in the accounting currency, of forecast expense consumption cost transactions.
Budget consumption cost item	ProjTransBudgetCube.BudgetConsumptionCostItem	Sum	The sum of the value, in the accounting currency, of forecast item consumption cost transactions.
Budget consumption	ProjTransBudgetCube.BudgetConsumption	Sum	The sum of the value, in the accounting currency, of forecast consumption cost transactions.
Budget payroll allocation	ProjTransBudgetCube.BudgetPayrollAllocation	Sum	The sum of the value, in the accounting currency, of forecast payroll allocation transactions.
Budget invoiced on account	ProjTransBudgetCube.BudgetInvoicedOnAccount	Sum	The sum of the value, in the accounting currency, of forecast on account transactions.
Total budget cost	ProjTransBudgetCube.TotalBudgetCost	Sum	The sum of the value, in the accounting currency, of forecast cost transactions.
Remaining budget cost	ProjTransBudgetCube.RemainingBudgetCost	Sum	The sum of the value, in the accounting currency, of remaining forecast cost transactions.
Original budget cost	ProjTransBudgetCube.OriginalBudgetCost	Sum	The sum of the value, in the accounting currency, of forecast original budget transactions.
Budget gross WIP cost	ProjTransBudgetCube.BudgetGrossWIPCost	Sum	The sum of the value, in the accounting currency, of forecast work in progress cost transactions.
Budget gross margin	ProjTransBudgetCube.BudgetGrossMargin	Sum	The sum of the value, in the accounting currency, of forecast revenue less forecast cost transactions.
Budget net WIP	ProjTransBudgetCube.BudgetNetWIP	Sum	The sum of the value, in the accounting currency, of forecast work in progress revenue less forecast work in progress cost transactions.
Remaining budget deviation	ProjTransBudgetCube.RemainingBudgetDeviation	Sum	The sum of the value, in the accounting currency, of actual to budget deviation.
Budget cash inflow	ProjTransBudgetCube.BudgetCashInflow	Sum	The sum of the value, in the accounting currency, of forecast cash inflows.
Budget cash outflow	ProjTransBudgetCube.BudgetCashOutflow	Sum	The sum of the value, in the accounting currency, of cash outflows.
Budget net cash flow	ProjTransBudgetCube.BudgetNetCashflow	Sum	The sum of the value, in the accounting currency, of forecast cash inflow less forecast cash outflow.
Budget WIP invoiced on account	ProjTransBudgetCube.BudgetWIPInvoicedOnAccount	Sum	The sum of the value, in the accounting currency, of forecast work in progress on account transactions.

Exchange rates by day

This measure group is based on the BIExchangeRateView view and includes the following measures.

Measure	Measure field name	Aggregation	Description	Associated dimensions
Exchange rate	BIExchangeRateView.CrossRate	Max	The exchange rate.	Currency Date (exchange rate date) Analysis currency

Total estimated costs

This measure group is based on the ProjActivityCompletedCostView view and includes the following measures.

Note

This measure group is available only in Microsoft Dynamics AX 2012 R3.

Measure	Measure field name	Aggregation	Description	Associated dimensions
Total estimated costs count	Not applicable	Count	The number of estimated costs.	Company Activity Project activity completed cost view
Completed activity planned cost	ProjActivityCompletedCostView.TotalCostPrice	Sum	The total planned costs for activities that are marked as complete.

Project estimates view

This measure group is based on the ProjProjectEstimatesView view and includes the following measures.

Note

This measure group is available only in Microsoft Dynamics AX 2012 R3.

Measure	Measure field name	Aggregation	Description	Associated dimensions
Project estimates view count	Not applicable	Count	The number of project estimates.	Company Project estimates view Projects
Estimate at complete	ProjProjectEstimatesView.CostAtComplete	Sum	The effort in hours that is estimated to be incurred at completion, taking into consideration any changes or delays.
Effort at complete	ProjProjectEstimatesView.EffortAtComplete	Sum	The total effort in hours that will be incurred at completion.

Project planned costs view

This measure group is based on the ProjProjectPlannedCostView view and includes the following measures.

Note

This measure group is available only in Microsoft Dynamics AX 2012 R3.

Measure	Measure field name	Aggregation	Description	Associated dimensions
Project planned costs view count	Not applicable	Count	The number of project planned costs.	Company Project planned costs view Projects Date (Scheduled end date)
Planned cost	ProjProjectPlannedCostView.SumOfTotalCostPrice	Sum	The initial planned cost for the various activities and tasks in the project.

Project planned effort view

This measure group is based on the ProjProjectPlannedEffortView view and includes the following measures.

Note

This measure group is available only in Microsoft Dynamics AX 2012 R3.

Measure	Measure field name	Aggregation	Description	Associated dimensions
Project planned effort view count	Not applicable	Count	The number of project planned efforts.	Company Project planned effort view Projects Date (Scheduled end date)
Planned effort	ProjProjectPlannedEffortView.Effort	Sum	The initial planned effort in hours for the various activities and tasks in the project.

Calculated measures

The Project accounting cube contains the following calculated measures.

Calculated measure	Aggregation	Associated measure group	Description
Invoiced revenue deviation	Sum	Actuals	The difference between forecast and actual invoiced revenue.
Accrued revenue deviation	Sum	Actuals	The difference between forecast and actual accrued revenue.
Revenue deviation	Sum	Actuals	The difference between forecast and actual revenue.
Cost deviation	Sum	Actuals	The difference between forecast and actual costs.
Cost hour deviation	Sum	Actuals	The difference between forecast and actual hours costs.
Cost expense deviation	Sum	Actuals	The difference between forecast and actual expense costs.
Cost item deviation	Sum	Actuals	The difference between forecast and actual item costs.
Gross margin deviation	Sum	Actuals	The difference between forecast and actual gross margin.
WIP cost deviation	Sum	Actuals	The difference between forecast and actual work in progress costs.
WIP sales deviation	Sum	Actuals	The difference between forecast and actual sales work in progress.
Gross WIP cost deviation	Sum	Actuals	The difference between forecast and actual gross work in progress costs.
WIP invoiced on account deviation	Sum	Actuals	The difference between forecast and actual on-account work in progress.
Net WIP deviation	Sum	Actuals	The difference between forecast and actual net work in progress.
Hours deviation	Sum	Actuals	The difference between forecast and actual hour quantities.
Consumption deviation	Sum	Actuals	The difference between forecast and actual consumption costs.
Consumption cost hours deviation	Sum	Actuals	The difference between forecast and actual hour consumption costs.
Consumption cost expense deviation	Sum	Actuals	The difference between forecast and actual expense consumption costs.
Consumption cost item deviation	Sum	Actuals	The difference between forecast and actual item consumption costs.
Payroll allocation deviation	Sum	Actuals	The difference between forecast and actual payroll allocation.
Total cost total budget	Sum	Forecasts	The difference between forecast and actual costs.
Deviation total budget	Sum	Forecasts	The difference between forecast and actual revenue.
Remaining budget cost1	Sum	Forecasts	The remaining forecast costs.
Cash inflow deviation	Sum	Actuals	The difference between forecast and actual cash inflows.
Cash outflow deviation	Sum	Actuals	The difference between forecast and actual cash outflows.
Net cash flow deviation	Sum	Actuals	The difference between forecast and actual net cash flow.
Actual efficiency hours ratio	Sum	Actual hour utilization	The ratio between actual hours and burden hours using the efficiency calculation method.
Actual billable hours ratio	Sum	Actual hour utilization	The ratio between actual hours and burden hours using the billable calculation method.
Budget efficiency hours ratio	Sum	Forecasts	The ratio between forecast hours and burden hours using the efficiency calculation method.
Budget billable hours ratio	Sum	Forecasts	The ratio between forecast hours and burden hours using the billable calculation method.

Key performance indicators

The following sections describe the key performance indicators (KPIs) in the Project accounting cube.

KPI calculations

The following table lists the KPIs that are associated with the Project accounting cube. You can use the information in the following table to help verify the information in your KPIs.

The KPIs in the Project accounting cube depend on ledger posting types. Ledger posting types specify the ledger accounts to which project transactions are posted.

KPI	Associated measure group	Calculation
Revenue deviation	Actuals	The difference between actual and forecast revenue.
Gross margin deviation	Actuals	The difference between actual and forecast gross margin.
Net WIP deviation	Actuals	The difference between actual and forecast net work in progress.
Cash inflow devation	Actuals	The difference between actual and forecast cash inflows.
Cash outflow deviation	Actuals	The difference between actual and forecast cash outflows.
Net cashflow deviation	Actuals	The difference between actual and forecast cash inflows less cash outflows.

Role Centers

The following table lists the Role Centers and web parts that display the measures and KPIs associated with the Project accounting cube.

Role Center	Web parts and KPIs
Project manager	Actual vs. forecast: Actual vs. forecast hours Actual vs. forecast revenue Actual vs. forecast costs Business overview: Revenue deviation Gross margin deviation Net WIP deviation Cash inflow deviation Cash outflow deviation Net cash flow deviation

Security

The Project accounting cube can be accessed by users assigned to the following Microsoft SQL Server Analysis Services roles.

• Chief executive officer

• Chief financial officer

• Project manager

• Project supervisor