Share via


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:

  • 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