March 5, 2009 at 4:08 pm
Hello;I am working on a project where the product hierarchy changes with time. A product(level2) might have a different parent with time.(For example product "a1" belongs to parent "x1" for the first 6 months of the year and then belongs to parent "y1" for the rest of the 6 months). My productdimension table has pkey for the products and then parentpkey for the parents of these products.I want the product hierarchy to change with time. I saw that one can create SSIS packages for type2dimensions. What exactly does a SSIS package does?Does it insert into the dimension table the extraproduct rows? Does it also insert into the "fact table"?My solution was to update "productpkey" in the fact table depending on the time by a sql query.Also I want to insert into the product dimension table by sql query.Here is what I wanted to do:Now the fact table looks like this:
-------------Fact Table-------------
ProductPkey Time KPI
a1 3/3/2008 10
a1 7/7/2008 15
and I would like to change it to:
ProductPkey Time KPI
a1 3/3/2008 10
a2 ** 7/7/2008 15
product dimension table looks like this:
-------------Product Dimension Table-------------
ProductPkey ParentPkey
a1 x1
x1 z1
y1 w1
I would like to change it to:
ProductPkey ParentPkey
a1 x1
x1 z1
y1 w1
a2 ** y1
so actually productpkeys a1 and a2 are the same products.Is this what SSIS package doesalso? I prefer not to create a SSIS package but instead creating views that can be used as dimension tables. Thanks for the answers
March 6, 2009 at 7:13 am
Hi,
Looking at your example, you are not updating any value in ProductPkey, its basically a new row itself.
I guess what you were reffering to Type2 dimension is SCD (slowly changing dimension).
How SCD works -
You basically have 2 choices while using it.
If any change occurs in data already present in table
1. Either update the old values with new values, don't keep history.
2. Or If change occure, add a history flag to old data, insert updated data with flag current.
In both cases the new data gets added as new row, in case 2 with current flag.
Does this help?
John Smith
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply