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

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

3 posters

Go down

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

Post  rajwin.kau 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

Back to top Go down

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

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

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

http://aginity.com

Back to top Go down

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

Post  rajwin.kau 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

Back to top Go down

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

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

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

http://aginity.com

Back to top Go down

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

Post  rajwin.kau 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

Back to top Go down

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

Post  BoxesAndLines 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.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

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

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

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

http://aginity.com

Back to top Go down

Can someone help me in knowing how can we store history in supertype , subtypes . Empty 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

Back to top

- Similar topics

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