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

Updated: July 22, 2014

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

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.

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.

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

  • General ledger (LedgerBasic)

  • Trade (LogisticsBasic)

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

NoteNote

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

The Purchase cube includes the following measure groups.

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

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.

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.

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).

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)

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.

NoteNote

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).

NoteNote

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.

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

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

NoteNote

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.

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.

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

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


Announcements: To see known issues and recent fixes, use Issue search in Microsoft Dynamics Lifecycle Services (LCS).

Community Additions

ADD
Show: