Data Model

Entity Table

Entity	System	Description	Count
CLIENT	BC		3700
ARTICLE	BC		1000, plus 2000 historic
PRICE_LIST	BC
OFFER	BC
ORDER	BC
INVOICE	BC		~15k INVOICE_LINES/mth
CHANNEL	BC

ER Diagrams

Order

erDiagram



    CLIENT {
        txt Name
        txt MainChannel
        txt SubChannel
        txt Chain
    }

    OFFER {
        date Date
        money Amount
        list Status "Draft, Sent, Rejected, Accepted"
    }


    ORDER {
        date DeliverDate
        money TotalAmount
        cplx PaymentTerms
        list status "Draft, Issued, Signed, Delivered"
    }

    ORDER-LINE {
        int Quantity
        money Price
    }

    INVOICE {
        txt Number
        eur TotalAmount
        dte InvoiceDate
        dte PaymentDate
    }

    ARTICLE {
        txt Name
        money ListPrice
    }

    CLIENT ||--o{ ORDER: places
    ORDER o|--|o OFFER: refs
    CLIENT ||--o{ OFFER: receives
    ORDER-LINE }|--|| ORDER: has
    ARTICLE o|--o{ ORDER-LINE: refs
    ORDER |o--o{ INVOICE: refs
    INVOICE |o--o| CLIENT: for

NOTE: MainChannel <- SubChannel <- Chain is modeled using BC dimensions, but it's de-facto a hierarchy.

Analytical Data Model (aka "Star Schema")

Invoices

erDiagram
    FactInvoiceLine {
        id InvoiceNumber "Invoice"
        txt ExternalReference "Order"
        ref OrderNumber "Order"
        dte InvoiceDate "Invoice"
        dte OrderDate "Order"
        dte RequestedDeliveryDate "Order"

        EUR UnitCost "InvoiceLine"
        nbr IL_Quantity "InvoiceLine"
        EUR IL_GrossPrice "InvoiceLine"
        pct IL_LineDiscount "InvoiceLine"
        EUR IL_CostPrice "InvoiceLine"

        EUR I_Discount "Invoice"
        EUR I_Total "Invoice, incl. VAT"

        pct EstimatedCustomerRebate "Customer, not in BC"
    }

    FactPriceFromPriceList {
        EUR RecommendedSalesPriceInclVAT
        EUR BBS
        pct VAT
        pct Rebate
        EUR InvoicePrice
        pct RemiseAssortiment
        pct RemiseMarqueEtPresentation
        etc etc
    }

    DimPriceGroup {
        code Code "e.g. ANATU"
    }

    DimClient {
        id ClientNumber
        txt Name
        txt MainChannel
        txt SubChannel
        txt Chain
        txt Sector "Geo sector acc. to pharma industry standard definitions"
        det Address "Street, ZIP, etc.)"
        txt CustomerDiscountGroup
        txt CustomerPricingGroup

    }


    DimArticle {
        id ArticleCode "8710103873624"
        txt Ref "SCF033/27"
        txt Name "Natural biberon 260ml DUO"
        txt Brand "Babykid"
        txt MainCategory "Feeding, Comforting, Security"
        txt SubCategory "e.g. Pacifier Air"
        txt Model "e.g. 0-6"
        txt SubstitutionGroup
        txt ArticleDiscountGroup "?"
        txt ArticlePricingGroup "?"
    }


    DimDate {
        dte Date
        kw CalendarWeek
    }

    DimSalesPerson {
        id EmployeeNumber
        txt Name
    }


    DimClient ||--o{ FactInvoiceLine: refs
    DimClient ||--o{ FactInvoiceLine: refs
    DimArticle ||--o{ FactInvoiceLine: refs
    DimDate ||--o{ FactInvoiceLine: refs
    DimSalesPerson ||--o{ FactInvoiceLine: refs
    DimPriceGroup ||--o{ FactPriceFromPriceList: refs
    DimArticle ||--o{ FactPriceFromPriceList: refs
    DimDate ||--o{ FactPriceFromPriceList: validAsOf
    DimClient ||--o{ FactPriceFromPriceList: refs

TODO: DimArticle.SubstitutionGroup This must be modeled, in one of the following ways:

1. with a self-referencing column in DWH, where we derive the group from the path (note: if done with backward-referencing chain from C → B → A, then this is a tree/hierarchy).

2. by explicitly defining a substitution group instead of a chain