INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Slowly Changing Dimension Transform Won't Update

Slowly Changing Dimension Transform Won't Update

(OP)
I used the following from a CSV to test the SCD. I thought it would recognize the LocationIDs and update the records where necessary. But it did not. It only inserts new records.

There was a lot of nulls in the original and this set also has changes but the changes are not committed. Also notice that when i add a new location, both are added even though the LocationIDs are the same.




LocationID,Locations,Address,City,State,Zip,Phone,Country,Region
9,Pluto Disney,5000 Out this World,PlanetRock,PL,85338,(902) 504-1747,US,SolarSystem
1,Disney Lend,159 Mickey Mouse Road,Orlando,FL,58741,(201) 345-1234,US,North
2,Disney Werld,98532 Donald Duck Boulevard,Los Angelos,SA,75523,(601) 375-1345,US,South
3,Disney Pleyground,449 Smoke Mountain Lane,Atlanta,GA,24747,(804) 375-1126,US,East
4,Cajun Desney,Jazz Land Avenue,New Orleans,LA,88888,(904) 325-1237,US,West
5,Wild West Desney,Magic Kingdom Street,Somewhere West,CO,21543,(804) 346-1274,US,Northwest
3,Disney Super Playground,449 Smoke Mountain Lane,Atlanta,GA,24747,(864) 375-1526,US,East
4,Cajen Disney,Jazz Land Avenue,New Orleans,LA,88888,(904) 525-1237,US,West
6,Winter Disney,0 Ice Land Avenue,New Orleans,LA,85588,(900) 507-1297,US,North
2,Disney World,98532 Donald Duck Boulevard,Los Angelos,CA,75523,(671) 375-1345,US,South
7,Desert Disney,100 Melting Pot Way,Phoenix,AZ,85338,(902) 504-1747,US,Southwest
9,Plutian Disney,5000 Out this World,PlanetRock,PL,85338,(902) 504-1747,US,SolarSystem
10,Martian Disney,3000 Rover Drive,RedRock,M,85338,(902) 504-1747,US,SolarSystem

Here are the pictures from my SCD Wizard




This is where I map all my incoming attributes to the Database attributes.




All most all the data is historical but NO UPDATES




For the next one I've tried different values, It doesn't make a difference which one i pick or if i deselect them all.




I've kept this the same (never changed)




I've enabled and disabled this one. No Results




The finished Screen

RE: Slowly Changing Dimension Transform Won't Update

(OP)
Ok I figured it out. I took some thinking through it.


If "Fail the transformation if it detects changes in fixed attributes" is selected as it is below, then the whole package will fail. If you deselect it, the package will run, but if the SCD transform detects changes to the fixed attribute, it will allow all the changes go through except where it detects changes in the Fixed attributes. SO WHAT THIS MEANS, it does not ERROR OUT or completely cancel the package the way it does when checked. But it STILL DOESN'T IGNORE or allow the other changes to take effect if that row has a changed fixed attribute.




The problem is that book I have suggested using a Derived column to create a DateCreated column with a GetDate() function in the Expression column of the Derived Column transform to determine when the column was originally created. The author then suggested that this column should be set as fixed (even though it wasn't actually fixed since it will always enter the SCD with a current date.) The SCD will detect that the DateCreated column's value is different from the one in the database and so all those rows will fail to update because of that one change.

SO it was not my fault! The book mislead me and costed me hours too. smile

RE: Slowly Changing Dimension Transform Won't Update

I notice you are using the supplied SCD from Microsoft - don't - its slow and and problematic.

One good alternative (and free) is http://dimensionmergescd.codeplex.com/ this being one of the most commonly used - others exist

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close