Skip to content

Data Model

Entity Mapping

Entity Source Description Count
CONTACT SAP Clients, prospects, suspects, etc.
PRODUCT SAP
INVOICE SAP
INVOICE_LINE SAP
EMPLOYEE SAP Sales Rep, Internal Sales,
CAMPAIGNS Promotions
SALES_ACTIVITY SAP Visits, calls
TARGET SAP Sales target
POTENTIAL Excel the potential for client \(k\) for product \(i\)

TODO: get rough estimates for counts

QUESTION: are the SALES_ACTIVITIES really available from SAP?
QUESTION: where will the CAMPAIGNS be coming from?
QUESTION: are the TARGETS really coming from SAP?

Analytical Data Model (ADM, aka "Star Schema")

erDiagram
    FactInvoiceLine {
        id InvoiceNumber 
        dte InvoiceDate 
        dte OrderDate 
        EUR UnitCost 
        nbr Quantity 
        EUR GrossPrice 
        EUR CostPrice 
        EUR Commission
    }

    FactPotential {
        EUR Potential
    }

    FactTarget {
        EUR Target
    }

    FactSalesActivity {

    }

    FactCampaign {

    }

    DimSalesActivityType {
        code TypeCode "Visit, Call, etc."
    }

    DimDivision {
        id DivisionID
        txt DivisionName
    }


    DimContact {
        id ClientNumber
        code Stage "Suspect, Prospect, Client"
        txt Name
        code ZipCode
        txt City
    }

    DimProduct {
        code ProductNumber
        txt ProductName
        txt ProductCategory
        code ProductDivision

    }

    DimDate {
        dte Date
        kw CalendarWeek
    }

    DimEmployee {
        id EmployeeNumber
        code Role "Sales Rep, Internal"
        txt Name
    }

    DimCampaignType {
        code CampaignType "Promotion, Ad"
        EUR budget
    }

    FactInvoiceLine }o--|| DimContact: refs
    FactInvoiceLine }o--|| DimProduct: refs
    FactInvoiceLine }o--|| DimDate: OrderDate
    FactInvoiceLine }o--|| DimEmployee: refs
    FactInvoiceLine }o--|| DimDivision: refs
    FactPotential }o--|| DimProduct: refs
    FactPotential }o--|| DimContact: Client
    FactTarget }o--|| DimEmployee: refs
    FactTarget }o--|| DimDate: Year
    FactSalesActivity }o--|| DimDate: Year
    FactSalesActivity }o--o| DimSalesActivityType: Year
    FactSalesActivity }o--o| DimEmployee: refs
    FactCampaign }o--o| DimProduct: refs
    FactCampaign }o--|| DimCampaignType: refs