Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cengogs
Starting Member
1 Post |
 Posted - 2009-03-05 : 18:01:01
|
| 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 |
|
|
andre.wihardjo
Starting Member
4 Posts |
Posted - 2009-03-17 : 07:48:06
|
| Hi,In dimensional modeling, there are three types of slowly changing dimension (SCD). First SCD is updating the attribute data so this SCD do not keep the old data (no history). For example, A is live in Sydney and today A move to Jakarta. Then A’s city_address now is Jakarta, you cannot see where is A stay before in Jakarta?Second SCD is creating new row if there is a changing in attribute data. For example, A is live in Sydney and today A move to Jakarta. Then A will have two row data.Customer Key Customer Id Customer Name City Start End100 A10 James Beijing 1970-01-01 NULL101 JS43 Dinos Berlin 2001-07-23 NULL102 RS01 Andre Sydney 1998-08-08 NULL103 PR3G Lim Hongkong 1967-04-30 NULLToday is 2009-03-17 and Andre was move to Jakarta from Sydney.End column for customer Andre should update to today date - 1. In addition, create new row for customer Andre.Customer Key Customer Id Customer Name City Start End100 A10 James Beijing 1970-01-01 NULL101 JS43 Dinos Berlin 2001-07-23 NULL102 RS01 Andre Sydney 1998-08-08 2009-03-16103 PR3G Lim Hongkong 1967-04-30 NULL104 RS01 Andre Jakarta 2009-03-17 NULLThe update data keep the original Id (Natural Key = Customer Id) and update the END column so there is no changes in transaction table. You should use Start and End column to join with transaction table besides using id and transaction date.Third SCD is creating new column (attribute) value if there is a change in attribute data. For example, A is live in Sydney and today A move to Jakarta. Then A will have one row data but two columns of city.Customer Key Customer Id Customer Name Old City New City100 A10 James Beijing Beijing101 JS43 Dinos Berlin Berlin102 RS01 Andre Sydney Sydney103 PR3G Lim Hongkong HongkongCustomer Key Customer Id Customer Name Old City New City100 A10 James Beijing Beijing101 JS43 Dinos Berlin Berlin102 RS01 Andre Sydney Jakarta103 PR3G Lim Hongkong HongkongFor your situation, you can use SCD type 2 (Second SCD) to keep the historical data and this solution was enable in SSIS project.Warm regards,Andre Wihardjo |
 |
|
|
|
|
|
type2 dimension general question