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 |
|
sqlguru_di
Starting Member
2 Posts |
 Posted - 2011-11-10 : 06:58:20
|
| Hi, I have a source A (values-1,2,3) and i am doing look-up with three dimension tables 1(values-1,1a),table 2(values-2,2a),table 3(values-3,3a) and inserting 1a,2a,3a into fact table(destination).source-look-up-destination1 1 1a2 1a 2a3 2 3a. 2a. 3. 3aSo in my destination i have 3 columns(all primary keys) with different values.columns-1a 2a 3avalues -10 11 12But here my dimension table 2 is type 2dimension(which maintains history)by updating old record with N and new record with Y. So i want my fact table also to maintain history for just 2a column, It should update the old record in fact table as N and insert new record for 2a column as 15 & repeat the same values for 1a & 3a and say Y, like below.columns-1a 2a 3avalues -10 11 12 Nvalues -10 15 12 YIs it possible to do with SQL code or SSIS?Thanks,Dilip |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-10 : 07:13:55
|
| It's possible but not normally done.You could add a reversal to the fact table and add a new row for the new value.The point of a fact table is that it contains keys and additive measures - The same row with a negative measure amount will sum to 0.It also holds the state at the time of the entry so should point to the dimension entry at that time. If it needs to get the current state of the dimension then you can get that from the dimension table when querying.Could update all the fact entries limked to the dimension group - but that would be another key on the fact table probably - otherwise you would have a type 1 dimension.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
sqlguru_di
Starting Member
2 Posts |
Posted - 2011-11-10 : 08:20:56
|
| Hi I have done type 1 to the fact table and i am able to update the fact table when the dimension table updates to 'N', but i am not able to insert the new record with 'Y' when the dimension table 2 gets the new record. Thanks,Dilip |
|
|
|
|
|
|
|
|
how to insert historical data into a fact table