Can someone help me in knowing how can we store history in supertype , subtypes .

View previous topic View next topic Go down

Can someone help me in knowing how can we store history in supertype , subtypes .

Post  rajwin.kau on Tue Aug 25, 2015 12:53 pm

Can someone help me in knowing how can we store history in supertype , subtypes .


I have supertype with product num as unique identifier and subtype with product num as unique identifier ofcourse with other attributes that are different from supertype . I want to implement scd2 if any column changes in supertype or subtype .Can someone guide me on this ?

rajwin.kau

Posts : 4
Join date : 2015-08-25

View user profile

Back to top Go down

Re: Can someone help me in knowing how can we store history in supertype , subtypes .

Post  ngalemmo on Tue Aug 25, 2015 2:18 pm

By definition, the primary key for a supertype is also the primary key for it's subtypes. It would seem to me, the only way you can ensure sanity amongst users is to keep the keys in sync and create rows in the supertype and applicable subtypes when a change occurs anywhere for that member. Think of it as one row broken up into pieces.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Can someone help me in knowing how can we store history in supertype , subtypes .

Post  rajwin.kau on Tue Aug 25, 2015 3:38 pm

Lets say i make product ID and row eff date as primary in supertype and subtype as well i think i will be able to correlate split up records .



rajwin.kau

Posts : 4
Join date : 2015-08-25

View user profile

Back to top Go down

Re: Can someone help me in knowing how can we store history in supertype , subtypes .

Post  ngalemmo on Tue Aug 25, 2015 4:31 pm

That is not how type 2 works.

What you are creating is a table with a compound primary key... a type 1 key and a timestamp.

Using compound keys between facts and dimensions is not considered best practice.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Can someone help me in knowing how can we store history in supertype , subtypes .

Post  rajwin.kau on Wed Aug 26, 2015 12:42 am

Thanks for replying , the only way to solve this is than make product id and row eff as unique keys and generate a surrogate key in supertype and propagate this in subtype as foriegn key . Similarly subtype will have system generated surrogate key as primary key whereas product id and row eff date as unique keys.

Can u please give me an example of why composite t key in fact table will not be a good idea .

I may be wrong in what i came up with ,please advise if there is better way to implement this .

rajwin.kau

Posts : 4
Join date : 2015-08-25

View user profile

Back to top Go down

Re: Can someone help me in knowing how can we store history in supertype , subtypes .

Post  BoxesAndLines on Wed Aug 26, 2015 10:24 am

Combine your super and sub type tables into one table. There is no reason to model this way in a dimensional model.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Can someone help me in knowing how can we store history in supertype , subtypes .

Post  ngalemmo on Wed Aug 26, 2015 4:39 pm

Super/Sub types are a physical implementation of what is logically a single row. In a dimensional model, you either implement it as a single table, as B&L suggests, or as separate tables as you suggest.

If you implement separate tables in a dimensional model, you implement each table as separate dimensions with coordinated primary keys. For type 2, that means a new row in one table results in new rows in the other related tables with the same key.

A third option is to carry FK's to the super type and each subtype in the fact (which basically means you don't implement super/sub types).

There are a lot of other ways to do it, but if you wish to implement a dimensional model (as opposed to a relational model) those are your choices.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Can someone help me in knowing how can we store history in supertype , subtypes .

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