SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


My Slow Changing Dimension Transform is NOT WORKING


My Slow Changing Dimension Transform is NOT WORKING

Author
Message
Jacob Pressures
Jacob Pressures
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1119 Visits: 410
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, but won't update them.

I'm using Visual Studio 2010 and SQL Server 2012 with Win Authentication (I assume its not a permissions issue because it doesn't seem to be acknowledging the changes to the historical data at all if you look at the pic of the executed package.) I also have Windows 7 Home Premium.

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


Jacob Pressures
Jacob Pressures
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1119 Visits: 410
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. :-)


Honny
Honny
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 107
Transform it self says that Slowly Changing Dimension so my Opinion is not to us SCD in SQL server 2012 use lookup and merge statement which is available from sql server 2008 ,by Sql it self will do both insert and update as scd works and gives best performance so never try to use SCD transformtaion.

My opinion is use look up for match and un match records and merge statement in sql ..
AS scd works row by row operation...


Try in net. you different examples for replacement of SCD transformation.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search