Data Model

Legend

Value (left)	Value (right)	Meaning
|o	o|	Zero or one
||	||	Exactly one
}o	o{	Zero or more (no upper limit)
}|	|{	One or more (no upper limit)

Diagram

TODO: Get actual types from data sources, and adjust entity types if necessary.

erDiagram
    FactCustomerInvoiceLine }o--|| DimDate: InvoiceDate
    FactCustomerInvoiceLine }o--|| DimCustomer: CustomerID
    FactCustomerInvoiceLine }o--|| DimChannel: ChannelCode 
    FactCustomerInvoiceLine }o--|| DimShipmentMethod: ShipmentCode 
    FactCustomerInvoiceLine }o--|| DimProduct: ProductId 
    FactCustomerInvoiceLine {
        string OrderNumber
        int Quantity
        money InvoiceLineAmount
        date OrderDate
        money InvoiceAmount
        money SalesPrice
        money CostPrice
        money SalesConditions
        percentage BrandConditionsSupplier
        percentage BrandConditionsBuyingGroup
        percentage TargetBrandConditionsSupplier
        percentage TargetBrandConditionsBuyingGroup
        money ShipmentRevenueOrder
        money Commission
        money HandlingFees
        money StorageFees
        money ShipmentCostOrder
    }
    DimCustomer {
        int_pk CustomerID
        string CustomerName
        string CustomerRegion
        code CustomerCountry
    }
    DimDate {
        date Date
    }
    DimChannel {
        code_pk ChannelCode
    }
    DimShipmentMethod {
        code_pk ShipmentMethodCode
        string ShipmentName
        string ShipmentProvider
    }
    DimProduct {
        int_pk ProductId
        string ProductName
        string BrandName
        int BrandId
        string SupplierCategory
        code ProductSize
        string ProductGroupLevel1Name
        string ProductGroupLevel1Id
        string ProductGroupLevel2Name
        string ProductGroupLevel2Id
        string ProductGroupLevel3Name
        string ProductGroupLevel3Id
    }

Notes

• All other sales costs are stored in FactCustomerInvoiceLine, namely
  • Costs that occur per order line (e.g. Commission)
  • Costs that occur per order (e.g. Transport_II, pro rata)
  • Costs that occur per product (e.g. Storage, pro rata of the quantites compared to quantity that month)
• The CostPrice and the sales conditions (SC, TC, TBC) are also stored int FactCustomerInvoiceLine
• The ShipmentCost (to be paid by the client) is stored redundantly in FactCustomerInvoiceLine

Product

• SupplierCategory: This is a code combination from \(ProductGroupLevel1Name\) and \(BrandName\). We need it e.g. for the Brand Conditions.

SalesCostType

• T_I
• Storage