Project accounting cube (ProjCube) for Microsoft Dynamics AX 2012 and Microsoft Dynamics AX 2012 Feature Pack
Important
This content is archived and is not being updated. For the latest documentation, see Microsoft Dynamics 365 product documentation. For the latest release plans, see Dynamics 365 and Microsoft Power Platform release plans.
Applies To: Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012
Use the Project accounting cube to report on project profitability, committed cost, employee utilization, and cash flow for one or multiple projects in a company.
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
ProjPeriodEmpl table
CostControlTransCommittedCostCube view
InventTableExpanded view
ProjBIEmplTrans view
ProjBIForecastEmpl view
ProjTableCube view
ProjTransBudgetCube view
ProjTransPostingCube view
smmActivitesCube view
Measures
The Project accounting cube includes the following measure groups.
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 Projects Activity Project category Project contracts Worker Date (ledger date) Date (project transaction date) |
| 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 Projects Activity Model Project category Project contracts Worker Date (ledger date) Date (project transaction date) |
| 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 sum of costs 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. |
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 Vendor Customer Projects Activity Project category Project contracts Worker Committed costs Date (committed date) Date (exchange rate date) |
| 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 hours costs, in the accounting currency. |
|
| Committed item cost |
CostControlTransCommittedCostCube.CommittedItemCost |
Sum |
The sum of committed item costs, in the accounting currency. |
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 Released products Customer Projects Activity Forecast models Project funding source Project category Project contracts Worker Forecasts Date (ledger date) Date (project transaction date) Date (exchange rate date) |
| 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 hours cost transactions. |
|
| Budget cost expense |
ProjTransBudgetCube.BudgetCostExpense |
Sum |
The sum of the value, in the accounting currency, of forecast T&E 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 T&E 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 forecast remaining 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 forecast budget deviation transactions. |
|
| Budget cash inflow |
ProjTransBudgetCube.BudgetCashInflow |
Sum |
The sum of the value, in the accounting currency, of forecast income transactions. |
|
| Budget cash outflow |
ProjTransBudgetCube.BudgetCashOutflow |
Sum |
The sum of the value, in the accounting currency, of forecast outlay transactions. |
|
| Budget net cash flow |
ProjTransBudgetCube.BudgetNetCashflow |
Sum |
The sum of the value, in the accounting currency, of forecast inflow less forecast outflow transactions. |
|
| 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. |
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 transactions. |
Company Released products Customer Projects Activity Project funding source Project category Project contracts Worker Actuals Date (ledger date) Date (project transaction date) Date (exchange rate date) |
| 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 income transactions. |
|
| Actual cash outflow |
ProjTransPostingCube.ActualCashOutflow |
Sum |
The sum of the value, in the accounting currency, of project outlay 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 consumption T&E cost transactions. |
|
| Actual consumption cost hours |
ProjTransPostingCube.ActualConsumptionCostHours |
Sum |
The sum of the value, in the accounting currency, of project consumption hours cost transactions. |
|
| Actual consumption cost item |
ProjTransPostingCube.ActualConsumptionCostItem |
Sum |
The sum of the value, in the accounting currency, of project item cost transactions. |
|
| Actual cost |
ProjTransPostingCube.ActualCost |
Sum |
The sum of the value, in the accounting currency, of project 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 T&E 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 less 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 project work in progress revenue less project work in progress cost 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. |
Project periods - employee
This measure group is based on the ProjPeriodEmpl table and includes the following measures.
| Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
| Project periods – employee count |
Not applicable |
Count |
The number of project period records. |
Company Project periods - employee Worker Date (ledger date) Date (project transaction date) Date (start date – date) |
| Norm billable |
ProjPeriodEmpl.NormBillable |
Sum |
The sum of the number of hours that is billable. |
|
| Norm efficiency |
ProjPeriodEmpl.NormEfficiency |
Sum |
The sum of the number of hours that is utilizable. |
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 |
Calculated measures
The Project accounting cube contains the following calculated measures.
Calculated measure |
Aggregation |
Description |
|---|---|---|
Invoiced revenue deviation |
Sum |
The difference between forecast and actual invoiced revenue. |
Accrued revenue deviation |
Sum |
The difference between forecast and actual accrued revenue. |
Revenue deviation |
Sum |
The difference between forecast and actual revenue. |
Cost deviation |
Sum |
The difference between forecast and actual costs. |
Cost hour deviation |
Sum |
The difference between forecast and actual hours costs. |
Cost expense deviation |
Sum |
The difference between forecast and actual T&E costs. |
Cost item deviation |
Sum |
The difference between forecast and actual item costs. |
Gross margin deviation |
Sum |
The difference between forecast and actual gross margin. |
WIP cost deviation |
Sum |
The difference between forecast and actual work in progress costs. |
WIP sales deviation |
Sum |
The difference between forecast and actual work in progress sales. |
Gross WIP cost deviation |
Sum |
The difference between forecast and actual gross work in progress costs. |
WIP invoiced on account deviation |
Sum |
The difference between forecast and actual work in progress on account. |
Net WIP deviation |
Sum |
The difference between forecast and actual net work in progress. |
Hours deviation |
Sum |
The difference between forecast and actual hours. |
Consumption deviation |
Sum |
The difference between forecast and actual consumption costs. |
Consumption cost hours deviation |
Sum |
The difference between forecast and actual consumption hours costs. |
Consumption cost expense deviation |
Sum |
The difference between forecast and actual consumption T&E costs. |
Consumption cost item deviation |
Sum |
The difference between forecast and actual consumption item costs. |
Payroll allocation deviation |
Sum |
The difference between forecast and actual payroll allocation. |
Total cost total budget |
Sum |
The difference between forecast and actual costs. |
Deviation total budget |
Sum |
The difference between forecast and actual revenue. |
Remaining budget cost1 |
Sum |
The remaining forecast costs. |
Cash inflow deviation |
Sum |
The difference between forecast and actual cash inflow. |
Cash outflow deviation |
Sum |
The difference between forecast and actual cash outflow. |
Net cash flow deviation |
Sum |
The difference between forecast and actual net cash flow. |
Actual billable utilization |
Sum |
The utilization of employees using the billable calculation method. |
Actual efficiency utilization |
Sum |
The utilization of employees using the efficiency calculation method. |
Budget billable utilization |
Sum |
The forecast utilization of employees using the billable calculation method. |
Budget efficiency utilization |
Sum |
The forecast utilization of employees using the efficiency calculation method. |
Actual efficiency hours ratio |
Sum |
The ratio between actual hours and burden hours using the efficiency calculation method. |
Actual billable hours ratio |
Sum |
The ratio between actual hours and burden hours using the billable calculation method. |
Budget efficiency hours ratio |
Sum |
The ratio between forecast hours and burden hours using the efficiency calculation method. |
Budget billable hours ratio |
Sum |
The ratio between forecast hours and burden hours using the efficiency 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 |
Calculation |
|---|---|
Revenue deviation |
The difference between actual and forecast revenue. |
Gross margin deviation |
The difference between actual and forecast gross margin. |
Net WIP deviation |
The difference between actual and forecast net work in progress. |
Cash inflow devation |
The difference between actual and forecast cash inflow. |
Cash outflow deviation |
The difference between actual and forecast cash outflow. |
Net cashflow deviation |
The difference between actual and forecast cash inflow less cash outflow. |
Role Centers
The following table lists the Role Centers and web parts that display the KPIs associated with the Project accounting cube.
Role Center |
Web parts KPIs |
|---|---|
Project manager |
KPI for project manager:
|
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