How to Model Parent-Child Relational Table in a Dimensional Schema

Question

In the source relational database, there is a table called CompanySurety, with INT IDENTITY PK SuretyId which has a self-referencing ExtendsSuretyId FK column pointing to a parent SuretyId row.

Bringing this data into to a star-schema model, I've designed the CompanySurety fact table (so far) as follows:

CREATE TABLE fact_company_surety
(
    SuretyCompanyDimId INT NOT NULL,
    SuretyCoversCompanyAccountDimId INT NOT NULL,
    SuretyReplenishPaymentCompanyAccountDimId INT NOT NULL,

    -- ExtendsSuretyId -- TODO: ?????

    SuretyAmountChangeDateDimId int NOT NULL,
    SuretyEffectiveFromDateDimId int NOT NULL,
    SuretyEffectiveThruDateDimId int NOT NULL,
    SuretyExcludeCalcDateDimId int NOT NULL,
    SuretyHoldingCompanyDimId INT NOT NULL,
    SuretyLastRRQDateDimId int NOT NULL,
    SuretyMethodDimId INT NOT NULL,
    SuretyReplenishmentTypeDimId INT NOT NULL,  
    SuretyTypeDimId INT NOT NULL,

    SuretyAccountNumberDD nvarchar(100) NOT NULL,
    SuretyAmount money NULL,    
    SuretyBalance money NULL,
    SuretyDeletedFlag bit NOT NULL, 
    SuretyOverrideThresholdPercent decimal(5, 2) NULL
);

The question is, how do I model this parent-child relationship in a proper dimensional way (really, how to model any parent-child hierarchy in a dimensional model)?

I've considered duplicating all the dimension and fact columns to represent the parent fact (since, at present, the hierarchy is only one level deep). But this seems wrong to me.

Always the parent-child relations must be done in Dimension table, just use Adwenture works sample database and view Dimensions... there are Customer dimension by country-> City and others.... — Justin, Sep 16 '13 at 20:15

score 0 · Answer 1 · answered Nov 21 '13 at 01:03

If you know that there is a limit on the number of levels on the table, then you create columns Level1Surety, Level2Surety, Level3Surety, Level14Surety... right up to the number of levels you require. Then I always create a column called LevelLeafSurety. This always contains the leaf level.

If one member only has, say, 2 levels, you need to 'extend' the levels down to the leaf.

Post back if that doesn't make sense, and give me some indication of whether there is a practical limit on the number of levels (4, 10, 100?)

How to Model Parent-Child Relational Table in a Dimensional Schema

1 Answers1