Retail cube (RetailCube) 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
The Retail cube for Microsoft Dynamics AX is used to help manage a chain of stores so that your business can improve service, manage growth, reach customers, and streamline efficiencies. This article provides details about the cube.
The Retail 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 Retail cube uses data from the following tables and views:

InventSite table

MCRSourceCodeSetup table

RetailHour table

RetailTenderTypeTable table

CustPackingSlipTransExpanded view

CustTableCube view

InventTableExpanded view

MCRSourceSalesSummary view

PdsRebateExpanded view

RetailCategoryExpanded view

RetailChannelView view

RetailCustInvoiceJourView view

RetailCustInvoiceTransExpanded view

RetailInventValueCube view

RetailOMHierarchyView view

RetailTerminalView view

RetailTransactionDiscountTransView view

RetailTransactionPaymentTransView view

RetailTransactionSalesTransView view

RetailTransactionTableView view

RetailTransactionTaxTransView view

SalesLineExpanded view
Note 

The MCRSourceCodeSetup table and the MCRSourceSalesSummary and PdsRebateExpanded views are used with this cube only in Microsoft Dynamics AX 2012 R3. 
The Retail cube includes the following measure groups.
This measure group is based on the CustPackingSlipTransExpanded view and includes the following measures.
Measure  Measure field name  Aggregation  Description  Associated dimensions 

Customer packing slip lines count  Not applicable  Count  The number of packing slip lines.  Company Units Released products Customer Retail category Warehouses Geographic location Styles Colors Configurations Sizes Customer packing slip lines Date Sales category (sales category – historic) Geographic location (delivery location) Units (sales units) 
Packing slip quantity – inventory unit  CustPackingSlipTransExpanded.InventQty  Sum  The quantity per packing slip line, in storage unit of measure.  
Days late confirmed ship date  CustPackingSlipTransExpanded.DaysDelayedConfirmedDate  Sum  The number of days (per packing slip line) from the confirmed ship date to the packing slip date. If the packing slip date is before the confirmed ship date (that is—it is not delayed), the measure is 0.  
Days late requested ship date  CustPackingSlipTransExpanded.DaysDelayedRequestedDate  Sum  The number of days (per packing slip line) from the requested ship date to the packing slip date. If the packing slip date is before the requested ship date (that is—it is not delayed), the measure is 0.  
Packing slip quantity – sales unit  CustPackingSlipTransExpanded.Qty  Sum  The quantity per packing slip line, in sold unit of measure. 
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.  Currency Company Released products Styles Colors Configurations Sizes 
This measure group is based on the RetailTransactionDiscountTransView view and includes the following measures.
Measure  Measure field name  Aggregation  Description  Associated dimensions 

Retail transaction discounts count  Not applicable  Count  The number of transaction discounts.  Company Hour of day Customer Released products Retail channel POS terminal Worker Date 
Cash discount amount  RetailTransactionDiscountTransView.Amount  Sum  The amount of cash discount. 
This measure group is based on the RetailTransactionPaymentTransView view and includes the following measures.
Measure  Measure field name  Aggregation  Description  Associated dimensions 

Retail transaction payments count  Not applicable  Count  The number of retail transaction payments.  Currency Company Hour of day Payment methods Customer POS terminal Retail channel Worker Retail transaction payments Date Date (exchange rate date) 
Payment amount in transaction currency  RetailTransactionPaymentTransView.AmountCur  Sum  The total transaction amount in the transaction currency.  
Payment amount  RetailTransactionPaymentTransView.AmountMST  Sum  The total transaction amount.  
Tendered  RetailTransactionPaymentTransView.AmountTendered  Sum  The total amount tendered.  
Quantity  RetailTransactionPaymentTransView.Qty  Sum  The total quantity value of all products. 
This measure group is based on the RetailTransactionSalesTransView view and includes the following measures.
Measure  Measure field name  Aggregation  Description  Associated dimensions 

Retail transaction lines count  Not applicable  Count  The total number of transaction lines.  Currency Company Sites Released products Hour of day Customer POS terminal Retail category Retail channel Warehouses Worker Retail transaction lines Date Date (exchange rate date) 
Discount percentage  RetailTransactionSalesTransView.TotalDiscPct  Sum  The total discount points.  
Unit quantity  RetailTransactionSalesTransView.UnitQty  Sum  The total unit quantity.  
Cost amount  RetailTransactionSalesTransView.CostAmount  Sum  The total cost amount.  
Customer discount  RetailTransactionSalesTransView.CustDiscAmount  Sum  The total customer discount.  
Discount amount  RetailTransactionSalesTransView.DiscAmount  Sum  The total discount amount.  
Line discount amount  RetailTransactionSalesTransView.LineDscAmount  Sum  The total line discount amount.  
Net amount  RetailTransactionSalesTransView.NetAmount  Sum  The total net amount.  
Net amount including tax  RetailTransactionSalesTransView.NetAmountInclTax  Sum  The total net amount including tax.  
Net price  RetailTransactionSalesTransView.NetPrice  Sum  The total net price.  
Periodic discount amount  RetailTransactionSalesTransView.PeriodicDiscAmount  Sum  The total periodic discount amount.  
Price  RetailTransactionSalesTransView.Price  Sum  The total price.  
Sales transactions quantity  RetailTransactionSalesTransView.Qty  Sum  The total transaction quantity.  
Return quantity  RetailTransactionSalesTransView.ReturnQty  Sum  The total return quantity.  
Total discount amount  RetailTransactionSalesTransView.TotalDiscAmount  Sum  The total discount amount.  
Rounded amount  RetailTransactionSalesTransView.TotalRoundedAmount  Sum  The total rounded amount.  
Unit price  RetailTransactionSalesTransView.UnitPrice  Sum  The total unit price. 
This measure group is based on the RetailTransactionTableView view and includes the following measures.
Measure  Measure field name  Aggregation  Description  Associated dimensions 

Retail transactions count  Not applicable  Count  The number of transactions.  Currency Company Sites Hour of day Customer POS terminal Retail channel Warehouses Worker Retail transactions POS terminal (POS terminal – register number) POS terminal (POS terminal – register number 1) Date 
Customer account  RetailTransactionTableView.CustAccount  DistinctCount  The number of customers.  
Number of invoices  RetailTransactionTableView.NumberOfInvoices  Sum  The total number of invoices.  
Number of product lines  RetailTransactionTableView.NumberOfItemLines  Sum  The total number of product lines.  
Number of products  RetailTransactionTableView.NumberOfItems  Sum  The total number of items.  
Number of payment lines  RetailTransactionTableView.NumberOfPaymentLines  Sum  The total number of payment lines.  
Amount to account  RetailTransactionTableView.AmountToAccount  Sum  The total amount owed on account.  
Retail transaction cost amount  RetailTransactionTableView.CostAmount  Sum  The total cost amount.  
Retail transaction customer discount  RetailTransactionTableView.CustDiscAmount  Sum  The total discount amount.  
Retail transaction discount amount  RetailTransactionTableView.DiscAmount  Sum  The total discount amount.  
Gross amount  RetailTransactionTableView.GrossAmount  Sum  The total gross amount.  
Retail transaction net amount  RetailTransactionTableView.NetAmount  Sum  The total net amount.  
Retail transactions payment amount  RetailTransactionTableView.PaymentAmount  Sum  The total payment amount.  
Retail transactions rounded amount  RetailTransactionTableView.RoundedAmount  Sum  The total rounded amount.  
Sales invoice amount  RetailTransactionTableView.SalesInvoiceAmount  Sum  The total invoice amount.  
Sales order amount  RetailTransactionTableView.SalesOrderAmount  Sum  The total sales order amount.  
Retail transaction total discount amount  RetailTransactionTableView.TotalDiscAmount  Sum  The total discount amount. 
This measure group is based on the RetailTransactionTaxTransView view and includes the following measures.
Measure  Measure field name  Aggregation  Description  Associated dimensions 

Retail transaction taxes count  Not applicable  Count  The number of transaction tax lines.  Company Hour of day Customer POS terminal Retail channel Worker Date 
Tax amount  RetailTransactionTaxTransView.Amount  Sum  The total tax amount. 
This measure group is based on the SalesLineExpanded view and includes the following measures.
Measure  Measure field name  Aggregation  Description  Associated dimensions 

Sales order lines count  Not applicable  Count  The number of sales order lines.  Company Units Released products Customer Retail category Warehouses Colors Configurations Sizes Projects Sales order lines Date (confirmed ship date on 1st packing slip) Date (requested ship date on 1st packing slip) Sales category (sales category – historic) Units (sales units) 
This measure group is based on the RetailChannelView view in Microsoft Dynamics AX 2012 R3 and on the RetailInventValueCube view in prior releases. It includes the following measures.
Measure  Measure field name  Aggregation  Description  Associated dimensions 

Retail channel count  Not applicable  Count  The number of retail channels.  Retail channel 
Store area  In Microsoft Dynamics AX 2012 R3, RetailChannelView.StoreArea. In prior releases, RetailInventValueCube.StoreArea.  Sum  The total unit of area. 
This measure group is based on the RetailCustInvoiceTransExpanded view in Microsoft Dynamics AX 2012 R3 and on the CustInvoiceTransExpanded view in prior releases. It includes the following measures.
Measure  Measure field name  Aggregation  Description  Associated dimensions 

Customer invoice lines count  Not applicable  Count  The number of invoice transactions, also known as invoice lines.  Currency Company Units Sites Released products Hour of day Customer POS terminal Retail category Retail channel Organization unit Warehouses Geographic location Worker Styles Colors Configurations Fiscal period date Retail customer invoice transaction Date Date (exchange rate date) Sales category (sales category – historic) Geographic location (delivery location) Units (sales units) 
Commission line amount – accounting currency  In Microsoft Dynamics AX 2012 R3, RetailCustInvoiceTransExpanded.CommissAmountMST. In prior releases, CustInvoiceTransExpanded.CommissAmountMST.  Sum  The commission allocated per invoiced line.  
Customer invoice quantity – sales unit  In Microsoft Dynamics AX 2012 R3, RetailCustInvoiceTransExpanded.Qty. In prior releases, CustInvoiceTransExpanded.Qty.  Sum  The quantity invoiced per sold unit of measure.  
Quantity delivered without packing slip – sales unit  In Microsoft Dynamics AX 2012 R3, RetailCustInvoiceTransExpanded.QtyPhysical. In prior releases, CustInvoiceTransExpanded.QtyPhysical.  Sum  The quantity that is delivered directly with the invoice without a preceding packing slip. This quantity is 0 (zero) if a packing slip has been created for the item. If no packing slip has been created, this field contains the quantity sold in selling unit of measure.  
Sales tax line amount – accounting currency  In Microsoft Dynamics AX 2012 R3, RetailCustInvoiceTransExpanded.TaxAmountMST. In prior releases, CustInvoiceTransExpanded.TaxAmountMST.  Sum  The invoiced tax amount per invoice line.  
Cost of goods sold – accounting currency  In Microsoft Dynamics AX 2012 R3, RetailCustInvoiceTransExpanded.COGS. In prior releases, CustInvoiceTransExpanded.COGS.  Sum  The cost of goods sold (COGS) for the particular invoiced item. The COGS value is based on the corresponding inventory transaction. This measure may need an inventory closing where a potential adjustment may occur.  
Customer invoice quantity – inventory unit  In Microsoft Dynamics AX 2012 R3, RetailCustInvoiceTransExpanded.InventQty. In prior releases, CustInvoiceTransExpanded.InventQty.  Sum  The quantity invoiced per storage unit of measure.  
Customer invoice line amount – accounting currency  In Microsoft Dynamics AX 2012 R3, RetailCustInvoiceTransExpanded.LineAmountMST. In prior releases, CustInvoiceTransExpanded.LineAmountMST.  Sum  The invoiced amount per line, in the accounting currency, excluding tax.  
Sales tax included in customer invoice line amount – accounting currency  In Microsoft Dynamics AX 2012 R3, RetailCustInvoiceTransExpanded.LineAmountTaxMST. In prior releases, CustInvoiceTransExpanded.LineAmountTaxMST.  Sum  The value in this field is the same as the TaxAmountMST whenever the tax is included in the price on the invoice. The value in this field is 0 (zero) if the price on the invoice does not include tax. 
This measure group is based on the RetailInventValueCube view and includes the following measures.
Measure  Measure field name  Aggregation  Description  Associated dimensions 

Net amount change  RetailInventValueCube.Amount  Sum  The sum of transactions between two periods, in monetary value.  Company Sites Released products Retail category Styles Colors Configurations Sizes Fiscal period date Retail inventory value 
Net quantity change  RetailInventValueCube.Qty  Sum  The sum of transactions between two periods, disregarding the unit of measure. 
This measure group is based on the PDSRebateExpanded 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 

Sales rebate count  Not applicable  Count  Count of rebate given or applied.  Currency Company Customer Released products Sales rebate Worker Date (Process date – Date) Date (Calculation date – Date) 
Corrected rebate amount  PDSRebateExpanded.PDSCorrectedRebateAmt  Sum  Corrected rebate amount associated with a sales line, invoice or ledger account. 
The Retail cube contains the following calculated measures.
Calculated measure  Aggregation  Associated measure group  Description 

All sales order lines  Sum  Sales order lines  The sales order lines that have a status of delivered or invoiced, and have at least one packing slip. 
% Sales order lines shipped in full  Sum  Sales order lines  The percent shipped in full of sales order lines with at least one related packing slip and not of status Open order. 
% Sales order lines not shipped in full  Sum  Sales order lines  The percent not shipped in full of sales order lines with at least one related packing slip and not of status Open order. 
Average days late (confirmed ship date)  Average  Customer packing slip lines  The average difference from the confirmed ship date to the packing slip receipt date. 
Average days late (requested ship date)  Average  Customer packing slip lines  The average difference from the requested ship date to the packing slip receipt date. 
Beginning quantity  Sum  Inventory value  The balance as of period start, disregarding the unit of measure. 
Ending quantity  Sum  Inventory value  The balance as of period end, disregarding the unit of measure. 
Net issues quantity  Sum  Inventory value  The sum of issue transactions holding an InventTransType specified in the Issue transaction field, disregarding the unit of measure. 
Beginning amount  Sum  Inventory value  The balance as of period start, in monetary value. 
Ending amount  Sum  Inventory value  The balance as of period end, in monetary value. 
Net issues amount  Sum  Inventory value  The sum of issue transactions holding an InventTransType specified in the Issue transaction field, in monetary value. 
Days to date  Sum  Undefined  The number of days between periods. 
Products with transactions quantity  Sum  Not applicable  The products that hold an ending balance of type Quantity. 
Product rank quantity  Sum  Inventory value  The numeric ranking of products based on quantity. 
Cumulative quantity  Sum  Inventory value  The cumulative quantity value of product N. 
Cumulative quantity previous  Sum  Undefined  The cumulative quantity value of product N1. 
Total quantity  Sum  Inventory value  The total quantity value of all products. 
Cumulative % of the total quantity  Sum  Inventory value  The cumulative percentage of the total quantity of product N. 
Cumulative % of the total quantity previous  Sum  Inventory value  The cumulative percentage of the total quantity of product N1. 
ABC category quantity  Sum  Inventory value  The ABC classification of the product based on quantity measurement (C:80%, B:15%, A:5%). 
Products with transactions amount  Sum  Not applicable  The products that hold an ending balance of type Amount. 
Product rank amount  Sum  Inventory value  The numeric ranking of products based on quantity. 
Cumulative amount  Sum  Inventory value  The cumulative amount of product N. 
Cumulative amount previous  Sum  Undefined  The cumulative amount of product N1. 
Total amount  Sum  Inventory value  The total amount of all products. 
Cumulative % of the total amount  Sum  Inventory value  The cumulative percentage of the total amount of product N. 
Cumulative % of the total amount previous  Sum  Undefined  The cumulative percentage of the total amount of product N1. 
ABC category amount  Sum  Inventory value  The ABC classification of the product based on amount measurement (C:80%, B:15%, A:5%). 
Inventory turn quantity  Sum  Inventory value  This value is calculated as: Net issues quantity / ((Beginning quantity + Ending quantity) / 2) 
Inventory turn amount  Sum  Inventory value  This value is calculated as: Net issues amount / ((Beginning amount + Ending amount) / 2) 
Gross margin return on inventory investment  Sum  Inventory value  Indicates how much gross margin a retailer gets back for each dollar invested in inventory. This value is calculated as: (Sales / Average inventory at cost) * Gross margin percentage 
Sell through rate  Sum  Inventory value  This value is calculated as: Net issues quantity / [Measures].[Beginning quantity] 
Unit cost  Sum  Inventory value  This value is calculated as: Ending amount / Ending quantity 
Rank  Sum  Inventory value  The numeric ranking of products based on quantity. 
Issue transactions  Sum  Not applicable  This value is used to filter InventTransType of type Sales, ProdLine, Project, Asset, KanbanJobPickingList, KanbanJobWIP, and KanbanEmptied. 
Returns  Sum  Retail transactions  The amount of total returns. 
Gross profit margin  Sum  Customer invoice lines  The amount remaining after the cost of goods sold (COGS) has been deducted from the total sales for an item or a given quantity of inventory. 
Gross profit margin percentage  Sum  Customer invoice lines  Gross profit margin divided by the total sales revenue, expressed as a percentage. The gross profit margin represents the percent of total sales revenue that a retailer retains after incurring the direct costs associated with producing the goods and services sold. The higher the percentage, the more the retailer retains on each dollar of sales to service its other costs and obligations. 
Average ticket  Average  Retail transactions  The average amount of a retail transaction. 
Average number of items per transaction  Average  Retail transactions  The average number of items per retail transaction. 
Average number of payments per transaction  Average  Retail transactions  The average number of payments per transaction. 
Sales per unit area  Average  Retail transactions  The amount of sales per square foot or square meter. 
The following section describes the key performance indicators (KPIs) in the Retail cube.
The following table lists the KPIs that are associated with the Retail cube. You can use the information in the following table to help verify the information in your KPIs.
KPI  Associated measure group  Calculation 

Days of inventory quantity  All  This value is calculated as: Ending quantity / (Net issues quantity / Days to date) 
Weeks of inventory quantity  All  This value is calculated as: Ending quantity / (Net issues quantity / (Days to date / 7)) 
Months of inventory quantity  All  This value is calculated as: Ending quantity / (Net issues quantity / (Days to date / 30 )) 
Inventory turn quantity  All  This value is calculated as: Net issues quantity / ((Beginning quantity + Ending quantity) / 2) 
Days of inventory amount  All  This value is calculated as: Ending amount / (Net issues amount / Days to date) 
Weeks of inventory amount  All  This value is calculated as: Ending amount / (Net issues amount / (Days to date / 7 )) 
Months of inventory amount  All  This value is calculated as: Ending amount / (Net issues amount / (Days to date / 30)) 
Inventory turn amount  All  This value is calculated as: Net issues amount / ((Beginning amount + Ending amount) / 2) 
Total sales  All  The total net amount. 
Total returns  Retail transaction  The amount of total returns. 
Average ticket size  Retail transaction  This value is calculated as: Gross amount / Retail transactions count 
COGS  Customer invoice lines  The cost of goods sold (COGS) for the particular invoiced item. The COGS value is based on the corresponding inventory transaction. This measure may need an inventory closing where a potential adjustment may occur. 
Gross margin  Customer invoice lines  The amount remaining after the cost of goods sold (COGS) has been deducted from the total sales for an item or a given quantity of inventory. 
Gross margin percentage  Customer invoice lines  The gross profit margin divided by the total sales revenue, expressed as a percentage. The gross profit margin represents the percent of total sales revenue that a retailer retains after incurring the direct costs associated with producing the goods and services sold. The higher the percentage, the more the retailer retains on each dollar of sales to service its other costs and obligations. 
Sales by hour  Retail transaction lines  The average net amount of sales revenue by hour. 
Inventory turnover  Inventory value  This value is calculated as: Net issues amount / ((Beginning amount + Ending amount) / 2) 
GMROII  Inventory value  Indicates how much gross margin a retailer gets back for each dollar invested in inventory. This value is calculated as: (Sales / Average inventory at cost) * Gross margin percentage 
Sales per unit area  Retail transaction lines  The amount of sales per square foot or square meter. 
Total customers  Retail transactions  The number of customers. 
By default, the KPIs of the Retail cube are not displayed on Role Center pages. For information about how to add them to Role Center pages, see Manage KPIs.
The Retail cube can be accessed by users assigned to the following Microsoft SQL Server Analysis Services roles.

Accounting manager

Accounts receivable manager

Chief executive officer

Chief financial officer

Compliance manager

Financial controller

Retail merchandising manager

Retail operations manager

Retail store manager

Sales manager

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