Home Forums Data Warehousing Strategies and Ideas Beginner - Splitting a source table into Dimension and Fact tables RE: Beginner - Splitting a source table into Dimension and Fact tables

  • In my opinion, the first design you've had wasn't far off. I would do the following:

    PAYMENT

    PAYMENT_ID (INT, PK)

    CLIENT_ID (INT foreign key to client table)

    ACCOUNT_ID (INT)

    DATE_OF_PAYMENT (DATETIME)

    INVOICE_ID (INT)

    PAYMENT_TYPE_ID (INT)

    AMT_PAID (DECIMAL)

    PAYMENT_TYPE

    PAYMENT_TYPE_ID (INT, PK)

    PAYMENT_TYPE_NAME (VARCHAR)

    ...

    (Assuming that you already have a dimension for date, account and client)

    Add a dimension for payment type. Your fact table will have multiple entries per invoice...once for each payment type. Doing it that way will allow you to analyze the data by both invoice and payment type.