Sales Cube Data Dictionary

November 2018 Version 2.1.3

Copyright Notice

This documentation and the described software are copyrighted, 2018, by Kyrylo Kostiukov. All rights reserved. Kyrylo Kostiukov reserves the right to make improvements to the products described in this manual at any time without notice.

No part of this User Guide may be reproduced, copied, translated or transmitted, in any form or by any means without prior written permission of Kyrylo Kostiukov. Information provided in this User Guide intended to be accurate and reliable However, Kyrylo Kostiukov assumes no responsibility for its use; nor for any infringements of rights of third parties witch may result from its use.

PREFACE

This document extends the description of the features and behavior of the Software covered in Software Requirements Specification document. It includes a variety of elements that attempts to define the intended functionality required to satisfy different users of the Software. Functionality described here might be covered by Support Services Term and Conditions agreement between consumer of the software and the software provider.

The document covers description of the dataset that will be delivered to Power BI Service and will be available there for the user to create reports and dashboards or make ad-hoc analysis of the data on top of this dataset.

Content

Tables and columns
Relationships
Granularity of the data
Completeness
Slowly Changing Dimensions (SCD)
Example: Updating the Products and Categories data
Personal Information
Non-additive Measures
Usage of Parent Items
Limitations

Tables and columns

This section lists all visible to user Tables and Columns which are part of Sales Cube Dataset.

Products

Table: Products

Product Properties

Column	Description
Product Id	Magento Product ID
Product Name	Product Name
Attribute Set Id	Attribute Set ID
Attribute Set Name	Attribute Set Name
Product Type	Product Type
Sku	Product SKU
Product Date	Product Creation Date
Average Price	Average product Price across all placed orders
Average Price Incl Tax	Average product Price across all placed orders with Tax Included

Parent Products

Table: ParentProducts

Parent Product Properties

Column	Description
Product Id	Magento Product ID
Parent Product Name	Product Name
Attribute Set Id	Attribute Set ID
Attribute Set Name	Attribute Set Name
Product Type	Product Type
Sku	Product SKU
Product Date	Product Creation Date
Average Complex Price	Average Price across all placed orders associated with Parent Product
Average Complex Price Incl Tax	Average Price across all placed orders associated with Parent Product with Tax Included

Customers

Table: Customers

Customer Properties

Column	Description
Group Id	Customer Group ID
Group Code	Customer Group Code
Gender Name	Gender
Age	Age of the Customer when the order was made
Customers Count	Quantity of customers (only customers who made an order count)

Stores

Table: Stores

Website, Store, Store Group properties

Column	Description
Website Id	Magento Website ID
Store Group Id	Magento Store Group ID
Store Id	Magento Store ID (Store View)
Website Name	Website Name
Store Group Name	Store Group Name
Store Name	Store (Store View) Name
Store Code	Store (Store View) Code
Website Code	Website Code

Payment Methods

Table: PaymentMethods

Payment Method properties

Column	Description
Method Code	Payment Method Code
Method Name	Payment Method Name

Shipping Addresses

Table: ShippingAddresses

Shipping address data used as shipping address during the order.

Column	Description
Country Id	Magento Country ID.
Country Iso2 Code	Country ISO2 code.
Country Iso3 Code	Country ISO3 code.
Region Name	Region Name as it is defined in Magento Geo-Dictionary.
Region Code	Region Code from Magento Geo-Dictionary.
Region Default Name	Default Region Name as it is defined in Magento Geo-Dictionary.
City	City.
Postcode	Postal Code.
Region Country	Country associated with a Region as it is defined in Magento Geo-Dictionary.

Billing Addresses

Table: BillingAddresses

Billing address data used as shipping address during the order.

Column	Description
Country Id	Magento Country ID.
Country Iso2 Code	Country ISO2 code.
Country Iso3 Code	Country ISO3 code.
Region Name	Region Name as it is defined in Magento Geo-Dictionary.
Region Code	Region Code from Magento Geo-Dictionary.
Region Default Name	Default Region Name as it is defined in Magento Geo-Dictionary.
City	City.
Postcode	Postal Code.
Region Country	Country associated with a Region as it is defined in Magento Geo-Dictionary.

Attribute Options

Table: AttributeOptions

Product Attribute Options

Column	Description
Attribute Id	Product Attribute ID
Attribute Code	Product Attribute Code
Attribute Name	Product Attribute Name
Attribute Group Id	Product Attribute Group ID
Attribute Group Name	Product Attribute Group Name
Value	Product Attribute Option Name

Order Date

Table: OrderDate

Order Creation Date

Column	Description
Full Date	Full Calendar Date
Year	Year
Quarter	Quarter as a number from 1 to 4
Month	Month as a number from 1 to 12
Month Name	Month Name: "January", "February", ...
Week	Week of Year.
Day Of Year	Day of Year.
Day Of Week	Day of Week as a number from 1 to 7. Number 1 corresponds to Monday.
Day Of Month	Day of Month.

Order Time

Table: OrderTime

Order Creation Time

Column	Description
Hour	Hour as number from 0 to 23
Minute	Minute, a number from 0 to 59
Second	Second, a number from 0 to 59
Second Of Day	Second, a number from 0 to 59

Rules

Table: Rules

Sales Rules and their properties.

Column	Description
Rule Id	Magento Sales Rule ID.
Rule Name	Sales Rule Name.
From Date	The date from which Sales Rule becomes active.
To Date	The date when Sales Rule becomes inactive.
Promotion Type	Type of promotion.
Action	Describes the action that will be performed over the price amount if the Sales Rule is applied.
Free Shipping	Indicates if this Sales Rule will provide free shipping or not. May also contain description of the condition under which the shipping will be applied.
Is Shipping Discount	Indicates if this Sales Rule will provide discount to the product price or to the shipping costs.
Discount Fixed	Contains the fixed discount amount in the currency of the Website that will be applied by this Sales Rule. Only contains non-zero value if the Sales Rule is configured to apply fixed amount.
Discount Percent	Contains the fixed discount amount in the currency of the Website that will be applied by this Sales Rule. Only contains non-zero value if the Sales Rule is configured to apply fixed amount. The values is formatted as Percent: multiplied by 100 and % added.
Discount Qty	Corresponds to "Maximum Qty Discount is Applied to" field in Magento price rules properties page.
Discount Step	Corresponds to "Discount Qty Step (Buy X)" field in Magento price rules properties page.
Final Rule	Corresponds to "Stop Further Rules Processing" field in Magento price rules properties page.

Metadata

Table: Metadata

Table that contains data telling how actual the data is.

Column	Description
Update Time	Date when data was updated. Latest date correspond to date of last data update.
Export Time	Date when data was exported. Latest date correspond to date of last data export.

Tax Rates

Table: TaxRates

Properties of Tax Rates calculated for the complete Order.

Column	Description
Tax Rate Name	Tax Rate Name.
Tax Rate Code	Tax Rate Code.
Percent	Percent value for the Tax Rate. The values is formatted as Percent: multiplied by 100 and % added.

Item Tax Rates

Table: ItemTaxRates

Properties of Tax Rates calculated for Order Items.

Column	Description
Tax Rate Name	Tax Rate Name.
Tax Rate Code	Tax Rate Code.
Percent	Percent value for the Tax Rate. The values is formatted as Percent: multiplied by 100 and % added.

Shipping Methods

Table: ShippingMethods

Shipping Method properties.

Column	Description
Shipping Method	Shipping Method Name.
Carrier Code	Shipping Carrier Code.
Method Code	Shipping Method Code.
Description	Description for the Shipping Method.

Items

Table: Items

Order Item

Column	Description
Has Parent	Indicates if this order item is a part of Parent Product Item
Order Item Id	Magento Order Item ID
Parent Item Id	Order Item ID associated with Parent Product
Discount Percent	Average discount percent if some was applied to the Order Item. The values is formatted as Percent: multiplied by 100 and % added.
Order Items Count	Quantity of Order Items - sum of all time each product was ordered

Parent Items

Table: ParentItems

Order Item associated with Parent Product

Column	Description
Order Item Id	Magento Order Item ID associated with Parent Product
Weight	Average Weight specified for the Parent Product
Row Weight	Average Row Weight specified for the Parent Product

Shipment Items

Table: ShipmentItems

Shipment Item Properties

Column	Description
Shipment Item Id	Magento Shipment Item ID
Weight	Weight of the Shipment Item

Invoice Items

Table: InvoiceItems

Invoice Item Properties

Column	Description
Invoice Item Id	Magento Invoice Item ID
Price	Average Product Price Invoiced
Price Incl Tax	Average Product Price Invoiced with Tax Included
Cost	Cost recorded to Invoiced Product

Parent Invoice Items

Table: ParentInvoiceItems

Invoice Item Properties associated with Parent Product

Column	Description
Invoice Item Id	Invoice Item ID associated with Parent Product

Orders

Table: Orders

Order Properties

Column	Description
Order Time	Order Creation Time
Order Date	Order Creation Date
Order Id	Magento Order ID
Order Number	Magento Order Number
Customer Id	Magento Customer ID
Email Hash	Encrypted Hash of Customer's email
Email Domain	Domain Name part of Customer's email
Calc State Name	State Name Column. The value is calculated dynamically and cannot be used as normal column. It might used to show the state only for single order, but not for aggregating across the states. The column is also used in formulas for filtering some measures by order state.
Orders Count	Quantity of Orders.

Refund Items

Table: RefundItems

Credit Memo Item Properties

Column	Description
Creditmemo Item Id	Magento Credit Memo Item ID
Price	Average Product Price Refunded
Price Incl Tax	Average Product Price Refunded with Tax included
Cost	Cost recorded to Refunded Product

Parent Refund Items

Table: ParentRefundItems

Credit Memo Item Properties associated with Parent Product

Column	Description
Creditmemo Item Id	Credit Memo Item ID associated with Parent Product

Shipments

Table: Shipments

Shipment Properties

Column	Description
Shipment Id	Magento Shipment ID
Shipment Number	Magento Shipment Number
Shipment Time	Shipment Creation Time
Shipment Date	Shipment Creation Date
Total Weight	Average Shipment Weight

Invoices

Table: Invoices

Invoice Properties

Column	Description
Invoice Id	Magento Invoice ID
Invoice Number	Magento Invoice Number
Invoice Time	Invoice Creation Time
Invoice Date	Invoice Creation Date

Payments

Table: Payments

Payment Properties

Column	Description
Payment Id	Magento Payment ID
Shipping Captured	Shipping Captured Amount recorded with the Payment
Amount Paid	Amount Paid recorded with the Payment
Amount Authorized	Amount Authorized recorded with the Payment
Amount Paid Online	Amount Paid Online recorded with the Payment
Amount Refunded Online	Amount Refunded Online recorded with the Payment
Shipping Amount	Shipping Amount recorded with the Payment
Amount Ordered	Ordered Amount recorded with the Payment
Shipping Refunded	Refunded Shipping Amount recorded with the Payment
Amount Refunded	Refunded Amount recorded with the Payment
Amount Canceled	Canceled Amount recorded with the Payment

Refunds

Table: Refunds

Credit Memo Properties

Column	Description
Creditmemo Id	Magento Credit Memo ID
Creditmemo Number	Magento Credit Memo Number
Adjustment Positive	Adjustment Positive Amount recorded with the Credit Memo
Adjustment Negative	Adjustment Negative Amount recorded with the Credit Memo
Credit Memo Date	Credit Memo Creation Date
Credit Memo Time	Credit Memo Creation Time

Magento Measures

Table: MagentoMeasures

Includes values that replicate logic of some of the Magento measures. Most of the measures are defined on the level of Orders.

Column	Description
Mage Orders	Corresponds to "Orders" column in Orders Report in Magento. May include canceled orders.
Mage Orders (no canceled)	Corresponds to "Orders" column in Orders Report in Magento. Excludes canceled orders as in some versions of Magento.
Mage Invoiced Orders (no canceled)	Corresponds to "Invoiced Orders" column in Orders Report in Magento. Excludes canceled orders.
Mage Sales Items	Corresponds to "Sales Items" column in Orders Report in Magento
Mage Items	Corresponds to "Items" column in Orders Report in Magento
Mage Sales Total	Corresponds to "Sales Total" column in Orders Report in Magento
Mage Lifetime Sales	Measure that corresponds to "Lifetime" sales value displayed on Magento Overview Dashboard
Mage Revenue	Corresponds to "Revenue" column in Orders Report in Magento
Mage Profit	Corresponds to "Profit" column in Orders Report in Magento
Mage Invoiced	Corresponds to "Invoiced" column in Orders Report in Magento. May include canceled orders. Used in some Magento versions.
Mage Invoiced (no canceled)	Corresponds to "Invoiced" column in Orders Report in Magento. Excludes canceled orders. Used in some Magento versions.
Mage Paid	Corresponds to "Paid" column in Orders Report in Magento. May include canceled orders. Used in some Magento versions.
Mage Paid (no canceled)	Corresponds to "Paid" column in Orders Report in Magento. Excludes canceled orders. Used in some Magento versions.
Mage Not Paid (no canceled)	Corresponds to "Not Paid" column in Orders Report in Magento. Excludes canceled orders.
Mage Refunded	Corresponds to "Not Paid" column in Orders Report in Magento. Excludes canceled orders.
Mage Sales Tax	Corresponds to "Sales Tax" column in Orders Report in Magento.
Mage Tax	Corresponds to "Tax" column in Orders Report in Magento.
Mage Tax Amount (no canceled)	Corresponds to "Tax Amount" column in Tax Report in Magento.
Mage Sales Shipping	Corresponds to "Sales Shipping" column in Orders Report in Magento.
Mage Shipping	Corresponds to "Shipping" column in Orders Report in Magento.
Mage Sales Discount	Corresponds to "Sales Discount" column in Orders Report in Magento.
Mage Discount	Corresponds to "Discount" column in Orders Report in Magento.
Mage Canceled	Corresponds to "Canceled" column in Orders Report in Magento.

Ordered Measures

Table: OrderedMeasures

Measures that correspond to initially ordered amounts. These measures are not influenced by refunds or cancellations.

Column	Description
Total Cost	Total cost recorded for the ordered product item. The cost is taken from product properties and stored with the order items. Defined on the level of order items.
Grand Total	Total amount initially ordered. Corresponds to Grand Total values displayed on Order Details in Magento. Defined on the level of orders.
Total Discount	Sum of all discounts in the Order. Corresponds to Discount values displayed on Order Details in Magento. Defined on the level of orders.
Total Shipping	Sum of all Shipping charges in the Order. Corresponds to Shipping & Handling values displayed on Order Details in Magento. Defined on the level of orders.
Total Tax	Sum of all Tax charges in the Order, including charges not visible to the consumer. Is not displayed in Order Details in Magento. Defined on the level of orders.
Total Adjustments	Equals to difference between Grand Total and sum of values (Ordered Row Total + Total Discount + Total Shipping + Total Tax). This value is usually equal to 0 in default Magento installation. Value not equal to 0 may be caused by some error or custom implementation that adds some extra charges. Is not displayed in Order Details in Magento. Defined on the level of orders.
Total Net Amount	The most basic measure obtained by multiplying Product Price by Quantity ordered. Does not include any extra charges. Corresponds to Subtotal values displayed on Order Item Rows on Order Details in Magento. Defined on the level of order items.
Ordered Row Total	Equals to Total Net Amount + Total Row Tax + Total Row Discount. Corresponds to Row Total values displayed on Order Details in Magento. Defined on the level of order items.
Total Row Discount	Discount applied to order items. Corresponds to Discount Amount values displayed in Order Items Rows on Order Details in Magento. Defined on the level of order items.
Total Order Discount	Part of the discount amount that applies to complete order only. Is equal to Total Discount excluding Total Row Discount. On the Order Details page can be calculated as difference between Total Discount and sum of Discount Amounts from Order Items Rows. Defined on the level of orders.
Total Row Tax	Tax applied to order items, including tax hidden from consumers. The value is not displayed on Order Details in Magento. Defined on the level of order items.
Total Discount Tax Compensation	Part of the tax amount that is hidden from consumers. The value is not displayed on Order Details in Magento. Defined on the level of orders.
Total Consumer Tax	Total tax amount excluding the tax hidden from consumers. Corresponds to Tax values displayed on Order Details in Magento. Defined on the level of orders.
Total Row Discount Tax Compensation	Part of the tax amount that is hidden from consumers, only includes the part calculated on order items rows. The value is not displayed on Order Details in Magento. Defined on the level of order items.
Total Order Discount Tax Compensation	Part of the tax amount that is hidden from consumers, only includes the part calculated on top of the complete order. Is equal to Total Discount Tax Compensation minus Total Row Discount Tax Compensation. The value is not displayed on Order Details in Magento. Defined on the level of order items.
Total Row Consumer Tax	Tax applied to order items and visible to consumer. Corresponds to Tax Amount values displayed in Order Items Rows on Order Details in Magento. Defined on the level of order items.
Total Order Tax	Tax amount values, only includes the part calculated on top of the complete order. Is equal to Total Tax excluding Total Row Tax. The value is not displayed on Order Details in Magento. Defined on the level of orders.

Invoice Measures

Table: InvoiceMeasures

Measures that include invoiced amounts.

Column	Description
Cost Invoiced	Cost recorded for the invoiced product item. The cost is taken from product properties and stored with the invoice items. Defined on the level of order items.
Total Invoiced	Total invoiced amount. Corresponds to Grand Total values displayed on Invoice Details in Magento. Defined on the level of orders.
Not Invoiced	Total amount that was ordered but not invoiced. Equal to Grand Total minus Total Invoiced. Defined on the level of orders.
Discount Invoiced	Sum of all discounts in the Invoice. Corresponds to Discount values displayed on Invoice Details in Magento. Defined on the level of orders.
Not Invoiced Discount	Sum of all discounts that were ordered but not invoiced. Equals to Total Discount minus Discount Invoiced. Defined on the level of orders.
Shipping Invoiced	Sum of all Shipping charges in the Invoice. Corresponds to Shipping & Handling values displayed on Invoice Details in Magento. Defined on the level of orders.
Not Invoiced Shipping	Sum of all shipping charges that were ordered but not invoiced. Equals to Total Shipping minus Shipping Invoiced. Defined on the level of orders.
Tax Invoiced	Sum of all Tax charges in the Invoice, including charges not visible to the consumer. Is not displayed in Magento. Defined on the level of orders.
Not Invoiced Tax	Sum of all Tax charges that were ordered but not invoiced. Equals to Total Tax minus Tax Invoiced. Defined on the level of orders.
Net Invoiced	The measure obtained by multiplying Product Price by Quantity invoiced. Does not include any extra charges. Corresponds to Subtotal values displayed on Invoice Rows on Invoice Details in Magento. Defined on the level of order items.
Invoiced Adjustments	Equals to difference between Total Invoiced and sum of values (Discount Invoiced + Shipping Invoiced + Tax Invoiced + Net Invoiced). This value is usually equal to 0 in default Magento installation. Value not equal to 0 may be caused by some error or custom implementation that adds some extra charges. Is not displayed in in Magento. Defined on the level of orders.
Not Invoiced Adjustments	Equals to difference between Total Adjustments and Invoiced Adjustments. This value is usually equal to 0 in default Magento installation. Value not equal to 0 may be caused by some error or custom implementation that adds some extra charges. Is not displayed in in Magento. Defined on the level of orders.
Discount Tax Compensation Invoiced	Part of the invoiced tax amount that is hidden from consumers. The value is not displayed on Invoice Details in Magento. Defined on the level of orders.
Consumer Tax Invoiced	Tax amount invoiced excluding the tax hidden from consumers. Corresponds to Tax values displayed on Order Details in Magento. Defined on the level of orders.
Row Tax Invoiced	Tax applied to invoice rows, including tax hidden from consumers. The value is not displayed on Invoice Details in Magento. Defined on the level of order items.
Order Tax Invoiced	Tax amount values, only includes the part calculated on top of the complete order. Is equal to Tax Invoiced excluding Row Tax Invoiced. The value is not displayed on Invoice Details in Magento. Defined on the level of orders.
Row Discount Invoiced	Discount applied to Invoice Rows. Corresponds to Discount Amount values displayed in Invoice Rows on Invoice Details in Magento. Defined on the level of order items.
Order Discount Invoiced	Part of the discount amount that applies on top of the complete invoice. Is equal to Discount Invoiced excluding Row Discount Invoiced. On the Invoice Details page can be calculated as difference between Total Discount and sum of Discount Amounts from Invoice Rows. Defined on the level of orders.
Row Discount Tax Compensation Invoiced	Part of the tax amount that is hidden from Consumers, only includes the part calculated on the level of Invoice Rows. The value is not displayed on Invoice Details in Magento. Defined on the level of order items.
Order Discount Tax Compensation Invoiced	Part of the tax amount that is hidden from Consumers, only includes the part calculated on top of the complete order. Is equal to Discount Tax Compensation Invoiced minus Row Discount Tax Compensation Invoiced. The value is not displayed on Invoice Details in Magento. Defined on the level of order items.
Row Consumer Tax Invoiced	Tax applied to Invoice Rows and visible to Consumer. Corresponds to Tax Amount values displayed in Invoice Rows on Invoice Details in Magento. Defined on the level of order items.
Row Total Invoiced	Equals to Net Invoiced + Row Tax Invoiced + Row Discount Invoiced. Corresponds to Row Total values displayed on Invoice Details in Magento. Defined on the level of order items.

Refunded Measures

Table: RefundedMeasures

Measures that include refunded amounts.

Column	Description
Cost Refunded	Cost recorded for the refunded Product Item. The cost is taken from product properties and stored with the refunded items. Defined on the level of order items.
Total Refunded	Total refunded amount. Corresponds to Grand Total values displayed on Credit Memo Details in Magento. Defined on the level of orders.
Discount Refunded	Sum of all discounts in the Invoice. Corresponds to Discount values displayed on Invoice Details in Magento. Defined on the level of orders.
Shipping Refunded	Sum of all Shipping charges in the Invoice. Corresponds to Shipping & Handling values displayed on Invoice Details in Magento. Defined on the level of orders.
Tax Refunded	Sum of all Tax charges in the Invoice, including charges not visible to the consumer. Is not displayed in Magento. Defined on the level of orders.
Refunded Adjustments	Equals to difference between Total Refunded and sum of values (Discount Refunded + Shipping Refunded + Tax Refunded + Net Refunded + Recorded Adjustments). Note, that Recorded Adjustments are also excluded from the Total Refunded amount, thus making this measure to have expected value to be 0(zero). This value is usually equal to 0 in default Magento installation. Value not equal to 0 may be caused by some error or custom implementation that adds some extra charges. Is not displayed in in Magento. Defined on the level of orders.
Recorded Adjustments	Adjustments that where applied to refunded amount during Credit Memo creation.
Row Tax Refunded	Tax applied to invoice rows, including tax hidden from consumers. The value is not displayed on Invoice Details in Magento. Defined on the level of order items.
Order Tax Refunded	Tax amount values, only includes the part calculated on top of the complete order. Is equal to Tax Invoiced excluding Row Tax Invoiced. The value is not displayed on Invoice Details in Magento. Defined on the level of orders.
Row Discount Refunded	Discount applied to Invoice Rows. Corresponds to Discount Amount values displayed in Invoice Rows on Invoice Details in Magento. Defined on the level of order items.
Order Discount Refunded	Part of the discount amount that applies on top of the complete invoice. Is equal to Discount Invoiced excluding Row Discount Invoiced. On the Invoice Details page can be calculated as difference between Total Discount and sum of Discount Amounts from Invoice Rows. Defined on the level of orders.
Discount Tax Compensation Refunded	Part of the invoiced tax amount that is hidden from consumers. The value is not displayed on Invoice Details in Magento. Defined on the level of orders.
Consumer Tax Refunded	Tax amount invoiced excluding the tax hidden from consumers. Corresponds to Tax values displayed on Order Details in Magento. Defined on the level of orders.
Row Discount Tax Compensation Refunded	Part of the tax amount that is hidden from Consumers, only includes the part calculated on the level of Invoice Rows. The value is not displayed on Invoice Details in Magento. Defined on the level of order items.
Order Discount Tax Compensation Refunded	Part of the tax amount that is hidden from Consumers, only includes the part calculated on top of the complete order. Is equal to Discount Tax Compensation Invoiced minus Row Discount Tax Compensation Invoiced. The value is not displayed on Invoice Details in Magento. Defined on the level of order items.
Row Consumer Tax Refunded	Tax applied to Invoice Rows and visible to Consumer. Corresponds to Tax Amount values displayed in Invoice Rows on Invoice Details in Magento. Defined on the level of order items.
Net Refunded	The measure obtained by multiplying Product Price by Quantity invoiced. Does not include any extra charges. Corresponds to Subtotal values displayed on Invoice Rows on Invoice Details in Magento. Defined on the level of order items.
Row Total Refunded	Equals to Net Invoiced + Row Tax Invoiced + Row Discount Invoiced. Corresponds to Row Total values displayed on Invoice Details in Magento. Defined on the level of order items.

Canceled Measures

Table: CanceledMeasures

Measures that includes canceled amounts.

Column	Description
Total Canceled	Total amount initially ordered. Corresponds to Grand Total values displayed on Order Details in Magento. Defined on the level of orders.
Discount Canceled	Sum of all discounts in the Order. Corresponds to Discount values displayed on Order Details in Magento. Defined on the level of orders.
Consumer Tax Canceled	Total tax amount excluding the tax hidden from consumers. Corresponds to Tax values displayed on Order Details in Magento. Defined on the level of orders.
Shipping Canceled	Sum of all Shipping charges in the Order. Corresponds to Shipping & Handling values displayed on Order Details in Magento. Defined on the level of orders.
Net Canceled	The most basic measure obtained by multiplying Product Price by Quantity ordered. Does not include any extra charges. Corresponds to Subtotal values displayed on Order Item Rows on Order Details in Magento. Defined on the level of order items.
Canceled Adjustments	Equals to difference between Grand Total and sum of values (Ordered Row Total + Total Discount + Total Shipping + Total Tax). This value is usually equal to 0 in default Magento installation. Value not equal to 0 may be caused by some error or custom implementation that adds some extra charges. Is not displayed in Order Details in Magento. Defined on the level of orders.

Sales Measures

Table: SalesMeasures

Measures that are calculated as difference between invoiced and refunded amounts.

Column	Description
Sales Total	Difference between Total Invoiced and Total Refunded
Net Sales	Difference between Net Invoiced and Net Refunded
Sales Discount	Difference between Discount Invoiced and Discount Refunded
Sales Tax	Difference between Tax Invoiced and Tax Refunded
Sales Shipping	Difference between Shipping Invoiced and Shipping Refunded
Sales Adjustments	Difference between Invoiced Adjustments and Refunded Adjustments
Sales Consumer Tax	Difference between Consumer Tax Invoiced and Consumer Tax Refunded
Sales Discount Tax Compensation	Difference between Discount Tax Compensation Invoiced and Discount Tax Compensation Refunded
Sales Row Discount Tax Compensation	Difference between Row Discount Tax Compensation Invoiced and Row Discount Tax Compensation Refunded
Sales Order Discount Tax Compensation	Difference between Order Discount Tax Compensation Invoiced and Order Discount Tax Compensation Refunded
Sales Row Consumer Tax	Difference between Row Consumer Tax Invoiced and Row Consumer Tax Refunded
Sales Order Tax	Difference between Order Tax Invoiced and Order Tax Refunded
Sales Row Total	Difference between Row Total Invoiced and Row Total Refunded
Sales Row Discount	Difference between Row Discount Invoiced and Row Discount Refunded
Sales Order Discount	Difference between Order Discount Invoiced and Order Discount Refunded

Summary Measures

Table: SummaryMeasures

Some measures that summarise values by comparing values from different process areas.

Column	Description
Grand Total Not Processed	Equals to Grand Total excluding Total Canceled and Total Invoiced. May correspond to amount that was not completely processed: it was neither invoiced nor canceled.
Paid	Equals to sum of Paid amount taken from Magento Payments.
Unpaid	Difference between Total Invoiced and Paid amounts. First value is taken from Invoices, second - from Payments

Quantity Measures Table

Table: QuantityMeasures

Measures that describe product quantities.

Column	Description
Quantity Ordered	Quantity of Product Items that were ordered
Quantity Canceled	Quantity of Product Items that were canceled
Quantity Invoiced	Quantity of Product Items that were canceled
Quantity Shipped	Quantity of Shipped Product Items
Quantity Refunded	Quantity of Refunded Product Items
Quantity Not Invoiced	Quantity of Product Items that were ordered, but not invoiced
Sales Quantity	Quantity of Product Items that were invoiced and not refunded
Complex Quantity Ordered	Quantity of Parent Product Items that were ordered
Complex Quantity Canceled	Quantity of Parent Product Items that were canceled
Complex Quantity Invoiced	Quantity of Parent Product Items that were invoiced
Complex Quantity Shipped	Quantity of Parent Product Items that were shipped
Complex Quantity Refunded	Quantity of Parent Product Items that were refunded
Complex Quantity Not Invoiced	Quantity of Parent Product Items that were ordered but not invoiced
Complex Sales Quantity	Quantity of Parent Product Items that were invoiced and not refunded

Relationships

All of the listed tables are related to each other through sales data, except Metadata table. Any report and visualization should include one or more measures related to sales data: orders, invoices, refunds.

Granularity of the data

Not all measures are defined on the same level of granularity. Some measures are defined on the level of Order, some – on the level of Order Items. The information about the granularity is provided with the measures descriptions. Therefore, not all combinations of measures and dimensions will work as expected.

Below is the list of tables and their granularity. In most of the cases it is desired that granularity of the used table and granularity of the measures would match.

In case if the granularity will not match, Power BI will show the values corresponding to higher granularity. Thus, measures which are defined on the level of Order will display measure for complete Order even if they will be displayed in the rows corresponding to Order Items.

If the Granularity of the Table is Orders, it will work also with measures that work across Order Items, because measure across Order Items might be easily aggregated to the level of an Order.

Table	Can be used with Measures defined on the level of Order Items	Can be used with Measures defined on the level of Orders
Products	Yes	Results double counting*
Parent Products	Yes	Results double counting*
Customers	Yes	Yes
Stores	Yes	Yes
Payment Methods	Yes	Yes
Shipping Addresses	Yes	Yes
Billing Addresses	Yes	Yes
Categories	Yes	Results double counting*
Attribute Options	Yes	Results double counting*
Order Date	Yes	Yes
Order Time	Yes	Yes
Rules	Yes	Yes
Metadata	No	No
Tax Rates	Yes	Yes
Item Tax Rates	Yes	Results double counting*
Shipping Methods	Yes	Results double counting*
Items	Yes	Results double counting*
Parent Order Items	Yes	Results double counting*
Shipment Items	Yes	Results double counting*
Invoice Items	Yes	Results double counting*
Parent Invoice Items	Yes	Results double counting*
Orders	Yes	Yes
Refund Items	Yes	Results double counting*
Parent Refund Items	Yes	Results double counting*
Shipments	Yes	Yes, in rare cases will result in double counting**
Invoices	Yes	Yes, in rare cases will result in double counting**
Payments	Yes	Yes, in rare cases will result in double counting**
Refunds	Yes	Yes, in rare cases will result in double counting**

* Double counting in Power BI is handled intelligently. It does not produce single cells where the same amount counted trice. But it may happen that two rows will present the same measure values where each value includes amounts coming from the same source.

** There are also cases where granularity goes beyond the Orders and Order Items. Those are the cases where the grain of the data is Invoices, Credit Memos, Invoice Items, Parent Order Items and so on. To keep it simple, those cases are not covered yet. The double counting on those cases may be seen in case if an Order has more than one Invoice, Credit Memo or Payments.

In most of the cases, the correctly formulated business question should trigger the right usage of measures and dimensions (tables).

Completeness

The cube contains only data related to the orders. It is important to understand that the listed tables will not include full list of possible values, e.g. Products table does not include all products from the store.

Only data related to the orders inside the cube will be delivered to Power BI. For example, if only data for the last year and for store #1 are delivered , only products that where ordered in store #1 in the last year will be listed in Power BI.

This is true for all of the tables in Sales Cube Dataset.

Slowly Changing Dimensions (SCD)

slowly changing dimensions: keeping SLA would populate the data very quickly and would mean complex solution. Describe how product names, category names, links between product and categories will be updated.

Slowly changing dimensions is a complex concept in data-warehousing methodologies.

The goal of the SCD techniques is to provide the expected results in terms of their historical development.

Typical example is changes in customer address or modification of product names. Those values use different SCD technique. If the customer will change its address while relocating from US to Canada, his old orders will still be assigned to US. But if the product name will be modified, it will not produce additional product in the data and all orders will related to single product with new product name.

This is sometimes impossible to find appropriate solution because the expected behavior might depend on the nature of the modification which is often is missing. For example: customer address might be changed due to a mistake in the address line, not because of the relocation of the customer.

Sales Cube Dataset does not come with complex SCD techniques because usually this would result in quick growth of the amount of data and could result in reaching space limitations.

But it is still important to understand some of the concepts and limitations related to SCD in Sales Cube Dataset.

Example: Updating the Products and Categories data

Once the product and category properties will be delivered to Power BI, they will not be changed. Even if the name of the product will change in Magento, Power BI may still keep the old name.

Periodically it is needed to update such data to its actual state. For this purpose a Clean Up of the data should be scheduled to be performed. Typically this might be done once per month.

This relates to all data related to products and categories:

Product properties and attributes
Product categories
Relationships between products and categories

Sometimes such data might be partially updated though. This might happen if new category or new attribute value will be assigned to a product, or if the product will be assigned to another category. This might result in the situations where product will become assigned to both categories in the Dataset: to the old one and to the new category. Such “partially outdated” data will be in the Dataset till the next Clean Up.

The Clean Up will erase all of the data in Sales Cube and replace it with fresh data.

Personal Information

It is important for the companies to understand how personal information is treated. Because the data will be delivered to Power BI in non-aggregated state, it might result in customer data delivered to Microsoft. And sometimes this might require the consent of the customers.

For that reason, it is useful to separately list all customer-related data delivered with Sales Cube Dataset to Power BI Service. For the same reason the amount of personal data is limited in the Sales Cube. This includes:

Customer ID assigned to each Order;
Customer gender and age;
Customer Address Information: Country, Region, City, Postal Code;
Customer Email Information: email hash (not readable, not convertible to full email address), email domain (the part of the email after “@”).

Non-additive Measures

Sometimes invalid result might be shown in the report in those cases when non-additive measures are not handled appropriately. Example would be sum of product prices. Proper aggregation for product prices would be an average or median price.

Here is the list of non-additive measures in Sales Cube Dataset:

Products, Average Price
Products, Average Price Incl Tax
ParentProducts, Average Complex Price
ParentProducts, Average Complex Price Incl Tax
InvoiceItems, Price
InvoiceItems, Price Incl Tax
RefundItems, Price
RefundItems, Price Incl Tax

Usage of Parent Items

Parent Products or sometimes referred as Complex Products are Magento products that have type “configurable” or “bundle”.

Those product types are presented in the Sales Cube Dataset Magento with additional tables that support their analysis: ParentItems, ParentInvoiceItems, ParentRefundItems and so on.

There are also measures that would describe Parent Products: Complex Quantity Ordered, Complex Quantity Cancelled and so on.

Parent Products information might be useful is when you want to analyze the group of simple products as a whole.

Limitations

Category mapping (for drill down implementation) available only until sub-category on level 4.

Column	Description
Category Id	Magento Category ID
Category Name	Magento Category Name. The column hols all available categories.
Root Category	Contains the Root Category Names.
Sub Category 1	First Level Sub Category. The Category Name of the category which is direct child of its Root Category.
Sub Category 2	Second Level Sub Category. The Category Name of the category which is direct child of its First Level Sub Category.
Sub Category 3	Third Level Sub Category. The Category Name of the category which is direct child of its Second Level Sub Category.
Sub Category 4	4-th Level Sub Category. The Category Name of the category which is direct child of its Third Level Sub Category.
Root Category Id	Category ID of the Root Category.
Sub Category 1 Id	Category ID of the First Level Sub Category.
Sub Category 2 Id	Category ID of the Second Level Sub Category.
Sub Category 3 Id	Category ID of the Third Level Sub Category.
Sub Category 4 Id	Category ID of the 4-th Level Sub Category.
Parent Id	Category ID of the Parent Category. Or "0"(zero) for Root Category.
Path	Path to the category in format [Root Category ID]/[Sub Category 1 ID]/.../[Parent ID]/[Category ID].
Position	Position of the category in the list of categories. This column is used to order list of categories.
Level	Level Index. "0"(zero) for Root Category, "1" for First Level Sub Category and so on.