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 |
|
dirklx
Starting Member
13 Posts |
 Posted - 2007-11-14 : 04:27:16
|
| The following question might sound a bit stupid but I'm not a database expert so hopefully nobody minds me asking it.Here's what I did:1. I created an SSIS package that is supposed to import new data into my data warehouse as it becomes available.2. Since I need to maintain some of the history I use the Slow Moving Dimensions part (set the history flag on input fields) but run into an error condition while running the package. The message basically says that I'm about to create a duplicate record which is not allowed.Original Table1:PK1 field1PK2 field2PK3 field3 field4 field5-----------Now I enhanced it like this:Extended Table1:PK1 field1PK2 field2PK3 field3 field4 field5 CreateDate (new) NewDate (new) ActiveFlag (new)_____________________Now on some records the package is supposed to archive history by populating the (new) fields. In order to keep the record unique (primary key constraint) thought, do I need to make the (new) fields primary keys as well?So I guess I'm struggling with a more basic concept;)I would appreciate if somebody could shed some light on this.Thanks in advance.Dirk |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2007-11-14 : 04:47:43
|
| if it's telling you that you're about to create a duplicate it means that those 3 fields you've defined as primary key have not changed and would therefore be repeated by the insert. If your new fields are there purely to maintain a history like a valid_from_date, Valid_to_date and current_record_flag, then i wouldn't expect them to really be part of the PK. they simply indicate that something somewhere in the record has changed, right? So that leaves the question 'what has changed?'. I'd look again at the other fields in your table. what is it about the record that defines it as unique? apparently it is not just the 3 records you originally thoughtEm |
 |
|
|
dirklx
Starting Member
13 Posts |
Posted - 2007-11-14 : 08:52:49
|
quote:
Originally posted by elancaster
if it's telling you that you're about to create a duplicate it means that those 3 fields you've defined as primary key have not changed and would therefore be repeated by the insert. If your new fields are there purely to maintain a history like a valid_from_date, Valid_to_date and current_record_flag, then i wouldn't expect them to really be part of the PK. they simply indicate that something somewhere in the record has changed, right? So that leaves the question 'what has changed?'. I'd look again at the other fields in your table. what is it about the record that defines it as unique? apparently it is not just the 3 records you originally thoughtEm
My real table has 4 fields that make up for the primary key. the row to be imported should not create a dup just because I want to keep a history record of a related field. Maybe I'm not clear but if I have 4 fields that make up for the PK and 1 field that has history, then wouldn't the new record be:pk1+pk2+pk3+pk4 --- +OLD value hist1 --- time stamppk1+pk2+pk3+pk4 --- +NEW value --- time stampNow, without time stamp being part of the PK SSIS will always complain, or am I completely missing the point?Dirk |
|
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2007-11-14 : 09:09:05
|
| yes, adding the timestamp would make it unique so could be part of the PK, however what i'm trying to say is that it isn't necessarily a good PK (explaining it badly maybe though). If this is a dimension what are you storing in your fact table?more 'traditionally' you would have a surrogate key on the dimension i.e. a new ID on every row insert, and the history would be maintained by something that 'never changed' for that record (like your 4 columns or perhaps another id) combined with the timestamp etc. it's then the surrogate key you would store on the fact table.make sense?Em |
|
|
|
|
|
|
|
|
Slow Moving Dimension and History Time Stamp