Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

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

2 posters

Go down

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

Post  asilverstein Mon Sep 16, 2013 9:45 pm

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:

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
);
The question is, how do I model this parent-child relationship in a proper dimensional way (really, how to model any single-table 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. Any suggestions are appreciated.

asilverstein

Posts : 2
Join date : 2013-09-16

http://unifieddigital.com

Back to top Go down

How to Model Single-Table Parent-Child Relational Table in a Dimensional Schema Empty Re: How to Model Single-Table Parent-Child Relational Table in a Dimensional Schema

Post  ngalemmo Mon Sep 16, 2013 10:18 pm

The common approach is a hierarchy bridge table. Rather than explain it, google the term... there is a lot of stuff out there.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

How to Model Single-Table Parent-Child Relational Table in a Dimensional Schema Empty Re: How to Model Single-Table Parent-Child Relational Table in a Dimensional Schema

Post  asilverstein Mon Sep 16, 2013 11:23 pm

Thank you.

asilverstein

Posts : 2
Join date : 2013-09-16

http://unifieddigital.com

Back to top Go down

How to Model Single-Table Parent-Child Relational Table in a Dimensional Schema Empty Re: How to Model Single-Table Parent-Child Relational Table in a Dimensional Schema

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum