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

View previous topic View next topic Go down

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

Post  asilverstein on 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

View user profile http://unifieddigital.com

Back to top Go down

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

Post  ngalemmo on 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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

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

Post  asilverstein on Mon Sep 16, 2013 11:23 pm

Thank you.

asilverstein

Posts : 2
Join date : 2013-09-16

View user profile http://unifieddigital.com

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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