Retail cube (RetailCube) for Microsoft Dynamics AX 2012 R2 and R3
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.
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 
Deployment
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.
Configuration keys
The following configuration keys are required to use all features of the Retail cube:
 Retail Headquarters (RetailHeadquarters)
Tables and views
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.
Measures
The Retail cube includes the following measure groups.
Customer packing slip lines
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. 
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. 
Currency Company Released products Styles Colors Configurations Sizes 
Customers
This measure group is based on the CustTableCube view and includes the following measures.
Measure 
Measure field name 
Aggregation 
Description 
Associated dimensions 

Customers count 
Not applicable 
Count 
The number of customer records. 
Currency Company Customer Warehouses Worker 
Retail transaction discounts
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. 
Retail transaction payments
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. 
Retail transaction lines
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. 
Retail transactions
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. 
Retail transaction taxes
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. 
Sales order lines
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) 
Retail channel
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. 
Customer invoice lines
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. 
Inventory value
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. 
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 
Sales rebate
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. 
Calculated measures
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. 
Key performance indicators
The following section describes the key performance indicators (KPIs) in the Retail cube.
KPI calculations
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. 
Role Centers
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.
Security
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
Feedback
Loading feedback...