Loading...

Sales Cube Data Dictionary

November 2018 Version 2.1.3

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

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.

Categories

Table: Categories

Category properties. Includes mapping across different category levels.

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.

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.