How to Model Single-Table Parent-Child Relational Table in a Dimensional Schema
2 posters
Page 1 of 1
How to Model Single-Table Parent-Child Relational Table in a Dimensional Schema
In our 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:
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. Any suggestions are appreciated.
Bringing this data into to a star-schema model, I've designed the CompanySurety fact table (so far) as follows:
- Code:
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,
SuretyOverrideThresholdPercent decimal(5, 2) NULL
);
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. Any suggestions are appreciated.
Re: How to Model Single-Table Parent-Child Relational Table in a Dimensional Schema
The common approach is a hierarchy bridge table. Rather than explain it, google the term... there is a lot of stuff out there.
Similar topics
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» From Enterprise Models to Dimensional Models. Can a single Dimension table be referenced multiple times in a fact table?
» Advice on schema. Is the table in question dimensional or factual?
» Complexities of Relational Model and Simplicities of Dimensional Model
» Integrating new fact table which has one to many relationship with the main fact table in existing star schema
» From Enterprise Models to Dimensional Models. Can a single Dimension table be referenced multiple times in a fact table?
» Advice on schema. Is the table in question dimensional or factual?
» Complexities of Relational Model and Simplicities of Dimensional Model
» Integrating new fact table which has one to many relationship with the main fact table in existing star schema
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum