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.

Reference

Deployment

Configuration keys

Tables and views

Measures

Calculated measures

Key performance indicators

Security

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