Purchase cube (PurchCube) for Microsoft Dynamics AX 2012 R2 and R3

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 R3, Microsoft Dynamics AX 2012 R2

The Purchase cube for Microsoft Dynamics AX is used to report on purchase transactions. This article provides details about the cube.

Units of measure and reporting with the Purchase 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

Units of measure and reporting with the Purchase cube

When working with Microsoft SQL Server Reporting Services reports that display a quantity of an item, the appropriate unit of measure must be incorporated into the report to make sure that the quantity is correct, instead of just presenting a SUM value.

For the Quantity measures in the purchase unit of measure, slicing on the Purchase Units dimension separates the quantities by unit of measure. Quantity measures in inventory unit of measure can be sliced by the Unit dimension, which represents the inventory unit.

Deployment

The Purchase 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 Purchase cube:

• General ledger (LedgerBasic)

• Trade (LogisticsBasic)

Tables and views

The Purchase cube uses data from the following tables and views:

• DlvMode table

• InventBuyerGroup table

• VendInvoiceJour table

• InventTableExpanded view

• PurchLineExpanded view

• TAMVendRebateExpanded view

• VendInvoiceTransExpanded view

• VendPackingSlipTransStage view

• VendTableCube view

Note

The TAMVendRebateExpanded view is used with this cube only in Microsoft Dynamics AX 2012 R3.

Measures

The Purchase cube includes the following measure groups.

Released products

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

Measure	Measure field name	Aggregation	Description	Associated dimensions
Released products count	Not applicable	Count	The number of released products in all organizations.	Currency Company Released products Buyer groups Vendor Styles Colors Configurations Sizes

Purchase lines

This measure group is based on the PurchLineExpanded view and includes the measures in the following table.

From the measures it is possible to read the cycle time in calendar days that it takes for a purchase to be fulfilled. The measure is based on events on a purchase line. The events on a purchase line included in this context are:

• Confirming the purchase order the first time; the delivery date on the purchase line is the requested date

• Adding the confirmed delivery date after the purchase order has been confirmed

• Registering the first product receipt (fully received or partially received)

• Registering the last product receipt (first product receipt = last product receipt if there is only one)

Only purchase order lines where the products are received by products receipts and have status received or invoiced are included in the cube measures.

Measure	Measure field name	Aggregation	Description	Associated dimensions
Purchase lines count	Not applicable	Count	The number of purchase lines that are included in the measures in the measure group. This includes lines where the products are received by products receipts and have status Received or Invoiced.	Currency Company Units Released products Mode of delivery Vendor Warehouses Procurement category Colors Configurations Sizes Purchase lines Date (date of first purchase order confirmation) Date (requested delivery date on first confirmation) Date (receipt date of last delivery) Procurement category (procurement category – historic) Units (purchase units)
Purchase line total days from first confirmation to received	PurchLineExpanded.DaysConfirmedToReceived	Sum	The total number of days on all purchase lines from the date where the first confirmation happened to the date where the last product receipt was registered (product receipt date). This measure is primarily used to generate the corresponding average calculated measure.
Purchase line total days from first requested delivery date to received	PurchLineExpanded.DaysExpectedToReceived	Sum	The total number of days on all purchase lines from the delivery date requested on the first confirmation to the date where the last product receipt was registered (product receipt date). This measure is primarily used to generate the corresponding average calculated measure.
Purchase line total number of product receipts	PurchLineExpanded.CountProductReceipts	Sum	The total number of product receipts that have been registered on the purchase lines.
Purchase line total number of confirmations by purchase line	PurchLineExpanded.CountPurchaseConfirmations	Sum	The total number of confirmations of the purchase order that the purchase line is related to. This indicates how many changes there have been in the purchase order lifecycle. Note that the number is per purchase line. For example, if there are 2 purchase lines on a purchase order and the purchase order has been confirmed 2 times then the measure shows 4.
Purchase line total days from first confirmation to first receipt	PurchLineExpanded.DaysConfirmedToFirstReceived	Sum	The total number of days on all purchase lines from the date where the first confirmation happened to the date where the first product receipt was registered (product receipt date). This measure is primarily used to generate the corresponding average calculated measure.
Purchase line total days from first receipt to received	PurchLineExpanded.DaysPartialToFullReceipt	Sum	The total number of days on all purchase lines from the date where the first product receipt was registered to the date where the last product receipt was registered (product receipt date). This measure is primarily used to generate the corresponding average calculated measure.
Purchase line total days from first confirmation to delivery date is confirmed	PurchLineExpanded.DaysConfirmedToFirstResponse	Sum	The total number of days on all purchase lines from the date where the first confirmation happened to the date where the confirmed delivery date is added to the purchase line. This measure is primarily used to generate the corresponding average calculated measure.

Product receipt lines

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

Measure	Measure field name	Aggregation	Description	Associated dimensions
Product receipt lines count	Not applicable	Count	The number of product receipt lines.	Company Units Released products Buyer groups Vendor Warehouses Procurement category Worker Colors Configurations Sizes Product receipt lines Date (expected delivery date) Vendor (vendor – invoice account) Date Procurement category (procurement category – historic) Units (purchase units)
Days late	VendPackingSlipTransStage.DaysDelayed	Sum	The number of days that the product receipt is delayed seen as the difference between the product receipt date that is given when posting the product receipt (date-physical) and the actual expected date. The measure has a value if the product receipt is posted after the expected date. The expected date is the Confirmed delivery date that is given on the purchase order line. If the Confirmed delivery date on the purchase order line is blank, then the measure is calculated as if the date is January 1, 1900.
Days early	VendPackingSlipTransStage.DaysEarly	Sum	The number of days that the product receipt is early, seen as the difference between the product receipt date that is given when posting the product receipt (date-physical) and the actual expected date. The measure has a value when the product receipt is posted before the expected date. The expected date is the Confirmed delivery date that is given on the purchase order line.
Product receipt lines quantity - inventory unit	VendPackingSlipTransStage.InventQty	Sum	The number of items received on a product receipt, in an inventory unit of measure.
Product receipt lines amount - accounting currency	VendPackingSlipTransStage.ValueMST	Sum	The cost of the items, which appears only after the invoice is posted. For product receipts that have been posted, the value is 0 (zero) until the corresponding invoice is posted.

Vendor invoice

This measure group is based on the VendInvoiceJour table and includes the following measures.

Measure	Measure field name	Aggregation	Description	Associated dimensions
Vendor invoice count	Not applicable	Count	The number of invoices.	Currency Company Mode of delivery Buyer groups Vendor Vendor invoice journal Vendor (vendor – invoice account) Date Date (due date)
Vendor invoice amount – accounting currency	VendInvoiceJour.InvoiceAmountMST	Sum	The invoiced amounts, in the accounting currency.
Vendor invoice charges amount – accounting currency	VendInvoiceJour.SumMarkupMST	Sum	The miscellaneous charges, such as transportation fees, that are allocated to the order, in the accounting currency.
Vendor invoice discount – accounting currency	VendInvoiceJour.EndDiscMST	Sum	The total discount, in the accounting currency, that is given on the order (the line discount is not included).

Vendors

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

Measure	Measure field name	Aggregation	Description	Associated dimensions
Vendors count	Not applicable	Count	The number of vendor accounts.	Currency Company Vendor Warehouses Vendor (vendor – invoice account)

Vendor invoice lines

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

Measure	Measure field name	Aggregation	Description	Associated dimensions
Vendor invoice lines count	Not applicable	Count	The number of invoiced purchase lines that has been posted. Every partial invoice of a purchase line counts as one.	Currency Company Units Released products Buyer groups Vendor Warehouses Procurement category Styles Colors Configurations Sizes Vendor invoice lines Vendor (vendor – invoice account) Date Date (exchange rate date) Procurement category (procurement category – historic) Units (purchase units)
Purchase and return quantity – purchase unit	VendInvoiceTransExpanded.Qty	Sum	The number of items that has been purchased or returned in a unit of measure used on the purchase order. Use the Purchase Units dimension to identify the unit of measure. A returned quantity appears as a negative number.
Purchase quantity – inventory unit	VendInvoiceTransExpanded.PurchaseQty	Sum	The number of items that has been purchased, in an inventory unit of measure, where items appear as a positive value on the purchase order. Note Use the Unit dimension to slice for inventory units.
Vendor invoice line amount returns not included – accounting currency	VendInvoiceTransExpanded.PurchaseAmountMST	Sum	The invoiced amount of the purchased items where items appear as a positive quantity on the purchase order.
Return quantity – inventory unit	VendInvoiceTransExpanded.ReturnQty	Sum	The number of items that has been returned to the vendor in an inventory unit of measure (or appears as a negative quantity on the invoiced purchase order). Note Use the Unit dimension to slice for inventory units.
Vendor invoice line amount returns – accounting currency	VendInvoiceTransExpanded.ReturnAmountMST	Sum	The invoiced amount of the returned items where items appear as a negative quantity on the purchase order.
Vendor invoice line amount – accounting currency	VendInvoiceTransExpanded.LineAmountMST	Sum	The invoiced amount per line, in the company currency.

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

Vendor Rebate

This measure group is based on the TAMVendRebateExpanded 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
Vendor rebate count	Not applicable	Count	Total number of Open and Approved rebate claims.	Currency Company Released Products Vendor Vendor Rebate Date (Calculation date – Date)
Vendor rebate amount	TAMVendRebateExpanded.StartingRebateAmt	Sum	Total dollar value of Open and Approved rebate claims.

Calculated measures

The Purchase cube contains the following calculated measures.

Calculated measure	Aggregation	Associated measure group	Description
Average days late	Average	Product receipt lines	The average difference from expected delivery to actual product receipt for all late product receipts.
Average days early	Average	Product receipt lines	The average difference from expected delivery to actual product receipt for all early product receipt.
Vendor performance on product receipts - percent late	Average	Product receipt lines	The percent of all product receipts where the product receipt lines are received late.
Vendor performance on product receipts - percent on time	Average	Product receipt lines	The percent of all product receipts where the product receipt lines are received on time.
Vendor performance on product receipts - percent early	Average	Product receipt lines	The percent of all product receipts where the product receipt lines are received early.
Purchase line average days from first confirmation to received	Average	Purchase lines	The average difference from first confirmation to the last product receipt registered.
Purchase line average days late from first requested delivery date to received	Average	Purchase lines	The average difference from the requested delivery date of first confirmation to the last product receipt registered for purchase lines with late receipt.
Purchase line average days early from first requested delivery date to received	Average	Purchase lines	The average difference from the requested delivery date of first confirmation to the last product receipt registered for purchase lines with early receipt.
Purchase line average days from first confirmation to first receipt	Average	Purchase lines	The average difference from first confirmation to the first product receipt registered.
Purchase line average days from first receipt to received	Average	Purchase lines	The average difference from the first product receipt registered to the last product receipt registered.
Purchase line average days from first confirmation to delivery date is confirmed	Average	Purchase lines	The average difference from first confirmation to addition of the confirmed delivery date to the purchase line.
Purchase line average number of confirmations	Average	Purchase lines	The average number of confirmed purchase orders per related purchase line.
Purchase line average number of product receipts	Average	Purchase lines	The average number of product receipts registered on the purchase lines.

Key performance indicators

The Purchase cube does not include any key performance indicators (KPIs).

Security

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

• Accountant

• Accounting manager

• Accounting payable manager

• Accounting supervisor

• Accounts payable centralized payments clerk

• Accounts payable clerk

• Accounts payable manager

• Buying agent

• Chief executive officer

• Chief financial officer

• Compliance manager

• Financial controller

• Project manager

• Purchasing manager