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)

Data model

We will have 2 fact tables.

A. FactAmount
B. FactProjectAmount

A. FactAmount

Data comes from SAGA BOB 50 mainly. In DimAccount we will have the AccountCode, AccountGroupCodes, AccountMainGroupCodes... We will not need all the codes but it is not a lot of extra work to take the full ledger. Also this would make it easier to work on a P&L or a more detailed cash planning later on.

B. FactProjectAmount

Project data comes from Bouwsoft mainly. In DimProject we will have all the information (PM, client, address...) of the projects.

Notes

Multiple companies

We will need a DimCompany to switch between multiple companies within Vensol.

CostType

We will need a Dim(Cost)Type to know if an invoice line is OA, MAT, P...

ProjectAmount Schema

erDiagram

    FactProjectAmount {
        int_pk DWH_ID
        decimal Amount
    }
    FactProjectAmount }|--|| DimDate: Date
    FactProjectAmount }|--|| DimProject: Project
    FactProjectAmount }|--|| DimProjectAmountType: Type
    FactProjectAmount }o--|| DimOffer: Offer

    DimProjectAmountType {
        int_pk TypeID
        string TypeName
        string LocalTypeName
        string TypeGroupName
    }

    DimDate {
        date Date "StandardDateTable"
    }

    DimProject {
        int_pk Project_ID
        string ProjectName
        string ProjectManager
        string ProjectAddress
        string ProjectNumber
        string ProjectType
        date OfferDate
        date RequestDate
        date StartDate
        string ProjectStatus
        string ClientName
        string ClientCity
        etc etc
    }

    DimOffer {
        int_pk OfferNumber
        bool IsApproved
        bool IsMainOffer
    }

Accounting Schema

erDiagram

    FactAmount {
        int_pk DWH_ID
        decimal Amount
    }

    FactAmount }|--|| DimCalendar: Date
    FactAmount }|--|| DimAccount: Account
    FactAmount }|--|| DimCompany: Company
    FactAmount }|--|| DimCounterparty: Counterparty


    FactCashPosition {
        ccy Amount
    }

    FactCashPosition }|--|| DimCurrency: Currency
    FactCashPosition }|--|| DimBankAccount: BankAccount
    FactCashPosition }|--|| DimCalendar: Date
    FactCashPosition }|--|| DimCompany: Company

    DimAccount {
        string_pk AccountCode "400000"
        string AccountName "Clients"
        code AccountType "Posting"
        string AccountCategory "Balance Sheet, Income Statement"
        int AccountClassCode "4"
        string AccountClassName "Expenses"
        int AccountMainGroupCode "40"
        string AccountMainGroupName "Handelsvorderingen"
        int AccountGroupCode "400"
        string AccountGroupName
        string Reporting1 "Direct Costs"
        string Reporting2 "Sales Costs"
        string Reporting3 "Material"

    }

    DimCalendar {
        date Date "StandardDateTable"
    }

    DimCompany {
        code_pk CompanyCode
        string CompanyName
        string CompanyTitle
    }

    DimCounterparty {
        code_pk CounterpartyCode
        string CounterpartyName
        code CustomerType
        code SupplierType
        string City


    }