Retail cube (RetailCube) 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 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.
.png "Reference")
Deployment Configuration keys Tables and views Measures Calculated measures Key performance indicators Security |
.png "Resources")
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 N-1. |
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 N-1. |
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 N-1. |
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 N-1. |
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