How to Define/Implement Type 1 SCD in SSIS using Slowly Changing Dimension Transformation

By
roopesh.valluru
-
113
13153

Friends,

Let us discuss about “How to define Type 1 SCD in SSIS using Slowly Changing Dimension Transformation” in this post. As most of us know that there are many types of SCDS available, here in this post we will cover only SCD Type 1. Before jumping into the demonstration, first let us know what this SCD Type 1 says –

In Type 1 SCD we will overwrite the existing data with the new data. Lets take a small example here. Lets say I have data like given below in my existing table.

Emp Key Name Country
1 Roopesh India

Let’s say the employee is not moved to USA from India. Now, the data that is present in the database table will be directly replaced with the new data and we will have the following data in table.

Emp Key Name Country
1 Roopesh USA

The major disadvantage of using this method is HISTORY will be lost. If we implement this methodology then we can’t track the history of a particular employee. So, this is not at all suggestible in the case where HISTORY needs to be maintained.

This is the easiest way to implement of all th SCD types available. So, Type 1 slowly changing dimension should be used when it is NOT NECESSARY for the data warehouse to keep track of historical changes. I hope you got some useful info regarding SCD type 1 and now let’s jump into SCD Transformation.

I have created two tables one Test_Stage(Which I am using as Staging table) and another one is Test(Which I am using as Main table) with the following syntax.

Create table Test_Stage
(
Emp_Id int,
Emp_Name Varchar(100),
Country Varchar(100)
)

Create table Test
(
Emp_Id int,
Emp_Name Varchar(100),
Country Varchar(100)
)

Now I inserted some records into my staging table i.e Test_Stage and PFB the scripts used –

Insert into test_stage Values(1,’Roopesh’,’India’);
Insert into test_stage Values(2,’Lokesh’,’Pakistan’);
Insert into test_stage Values(3,’Vinay’,’USA’);
Insert into test_stage Values(4,’Rakesh’,’China’);
Insert into test_stage Values(5,’Venki’,’Japan’);

Now Source data is ready and PFB the steps you have to follow to use Slowly Changing Dimension Transformation.

  • Open SSIS Package and drag a dataFlow Task from toolbox to control Flow Pane as shown below.
  • Either double click or Right click on Data Flow Task and select EDIT as shown below.
  • One the Data Flow pane is opened then drag and drop OLEDB SOURCE from “Data Flow Sources” as shown below.
  • Now select EDIT by Right clicking on OLE DB Source and provide the Source details. remember that you have to provide Staging table connection details here.
  • Select columns tab and check whether columns are coming correctly or not.

  • Now drag and drop Slowly Changing Dimension Transformation in to the Data Flow Pane and provide the connection between OLE DB Source and SCD Transformation.
  • Now right click on the SCD Transformation and click on EDIT menu.
  • Select the dimension table(in our case it is “Test”) and Keys under “Select Dimension Table and Keys” page as shown below.

  • In the above screenshot, the Business Key is the column based on which we will filter the Newly added records from the existing one. In simple this is the column which we use to look up.
  • After selecting Next, Under “Slowly Changing Dimension Columns” select the columns that MAY change and select Change Type as “Changing Attribute” as we are dealing with Type 1 SCD here. Once the selection is done the click on NEXT.

  • Check the option “Change all the matching records including the outdated records…….” if you wish to have that functionality and click Next.

  • Click Next and finally click on Finish by leaving the default options as is.
  • Once you click on Finish, SSIS server creates all the functionality that is required to implement SCD Type 1 based on the information provided by us. It will add Data flow tasks for Inserting new records and updating existing records as shown below.

  • Now if I run the package then it will check for newly added records and inserts those records and updates the already existing records if there is any change in data. As there is NO RECORDS in TEST table in this run it will load all the records under New Insert as shown below.

  • PFB the Screenshot of the data tables output before running the package and After running it. You can see data got loaded in to our table “TEST”.

  • Now I updated the existing records in staging table using the following queries and once I run the package again we can see data getting updated into our main table.

update test_stage set Country = ‘USA’ where Emp_Id = 1

  • Once I run the package you can see the package showing “1 rows” under “Changing Attributes Updates Output” section as shown below.

  • Now if you run the queries you can see the data updated in TEST table.

That’s it guys .. Now we implemented SCD Type 1 using Slowly Changing Dimension Transformation. This is as simple as this. Hopw you understood the concept. Happy Coding !!

Regards,

Roopesh Babu V

 

RELATED ARTICLESMORE FROM AUTHOR

113 COMMENTS

  1. I really like your blog.. very nice colors & theme. Did you design this website yourself or did you hire someone to do it for you?
    “밤의전쟁” Please reply as I’m looking to create my own blog and would
    like to find out where u got this from. cheers

  2. over the counter ed medication [url=http://cheapdr.top/#]pumps for ed[/url] ed treatment drugs

  5. best over the counter ed pills [url=http://cheapdr.top/#]male erection pills[/url] prescription meds without the prescriptions

  25. viagra naturale [url=https://viasenzaricetta.com/#]pillole per erezioni fortissime[/url] viagra generico in farmacia costo

  28. reputable mexican pharmacies online [url=http://mexicopharm.pro/#]mexican mail order pharmacies[/url] mexico pharmacies prescription drugs

  30. reputable indian pharmacies [url=https://indiapharm.pro/#]india pharmacy[/url] indian pharmacies safe

  33. cheap canadian pharmacy [url=https://canadapharm.pro/#]the canadian drugstore[/url] ed meds online canada

  35. buying prescription drugs in mexico online [url=https://mexicopharm.pro/#]medication from mexico pharmacy[/url] best online pharmacies in mexico

  38. online shopping pharmacy india [url=http://indiapharm.pro/#]india pharmacy[/url] top online pharmacy india

  66. best 10 online pharmacies [url=http://pillswithoutprescription.pro/#]canadian online pharmacy no prescription[/url] canadian pharmacy usa

  73. Titles that offer a fresh, new-to-the market feature, functionality or form factor, offering the player a new experience. 4073 Meghan Beeler Ct Machine Dimensions: This machine is 44 inches high and the top candle will add an additional 4 to 6 inches. The width is 22 inches and 20 inches deep. The weight is approximately 250 pounds. In 1998, IGT introduced Triple Play Draw Poker through its partnership with Action Gaming; and the Elvis themed slot machine. The new S2000 spinning reel slot machine system was brought to market in 1999. When are you going?- we’ll be Downtown in two weeks , so I could scout around for you- if you’re going sooner, you can scout it out for me! Munsters symbols are wild in The Munsters slots. If you get one Munsters symbol, that payline’s payout is multiplied five time. If you get two Munsters symbols, the payout is multiplied by a factor of 25. That’s a pretty good deal for a simple wild symbol.
    https://huzzaz.com/collection/watchlottomaxdr
    Casino Player Publishing In the News ⋆ Video Slots Online ⋆ (LIVE SLOT PLAY) BELLAGIO FOUNTAINS ⋆ Video Slots Online ⋆ COSMOPOLITAN • The Slot Cats • Join The Slot Cats for this Cat Crawl livestream! Weekly Livestreams • Taco Cat Tuesdays @ 6:00pm Pacific • Friday Cat Crawl @ 8:00pm Pacific Most modern video slots have four or five reels, which can go up to seven. All slots offered at reputable casinos online are, in actuality, video slots. Bonus games make slots more exciting and allow those who play slots to possibly earn extra credits and decent cash prizes by completing a series of new tasks and challenges. From The Pages Of: But, since both games are played on machines, there are both similarities and differences that they share. The similarities are enough to get players who lost on slots interested in video poker, but also, the slots are often attracting those who want to play a machine-based game without having to come up with a strategy.

  79. п»їlegitimate online pharmacies india [url=https://indianpharmacy.pro/#]online shopping pharmacy india[/url] Online medicine home delivery

  82. Now that you have some web design inspiration from the best construction company websites, here’s a step-by-step breakdown of how to make a Wix website. Wix’s powerful infrastructure, advanced functionalities, marketing tools and fully-managed security provide a completely customizable platform to create a website that emulates your brand: Other builders say they offer free services, but these are usually limited. With Site 123, you will get more features than your average builder. In just three moves, you can now have a website for your construction, building, or architecture firm ready to hit the streets of the online space. Site123 also follows all the latest trends and regulations to ensure a seamless experience. With a large and diverse force of skilled craft professionals, Sundt specializes in self-performing core scopes of work in the construction process. This means agile problem-solving and fewer surprises as we manage critical-path schedule, safety and quality control for the entire project.
    http://www.doncloud.vip/archives/20804
    monkeybusinessimages Getty Images We’re sharing our best tips for saving money on a home renovation. “Don’t spend too much money on over-improvements,” says Artur Muller, founder and CEO of Amluxe Realty in Lighthouse Point, Florida. “Create a maximum budget for renovations, which will keep your spending in line with comparable upgrades on the market.” Remodeling the kitchens and bathrooms brings in the highest resale value when selling your home. An inexpensive home renovation can bring a fresh look and feel to any space. As summer approaches and the world returns to life, bringing a sense of newness and change into your home can make a positive impact on mood and lifestyle without breaking the bank. From general interior to bathrooms, kitchens and beyond, there’s always something that can be done to help make a house feel more like a home.

  84. HOME » News » Los Angeles Lakers at San Antonio Spurs: Match Prediction, Betting Odds and How to Watch In this case, a $187 bet on the Lakers would earn you a $100 profit if they won the game. A $20 bet on the Lakers would net you $10.70, whereas a $20 bet on OKC would net you $32.20. It tells you that the sportsbooks think the Lakers have a much better chance of winning the game, and they have priced the NBA game lines accordingly. As for the moneyline, the Lakers have -275 odds to pick up the win, while the Spurs are listed at +223 to claim victory. These two offers combined can net new bettors with a large amount of bonus funds in the Codfish State. Add in the easy-to-use WynnBET Massachusetts app and you can add even more bonus funds to your collective bankroll. Start by claiming the WynnBET promo code and redeeming your bonus offer in Massachusetts.
    https://johnathanwzax629529.creacionblog.com/19375801/best-correct-score-tips-site
    We give you the correct score and fixed matches tips in our application Free Tips For 23.12.2022 Copyright © 2014. All Rights Reserved. VERIFIED SITE FOR SELLING FIXED MATCHES Free correct score predictions for today. Get the most up-to-date and accurate football correct score, as well as reliable predictions. Powered by WordPress and WordPress Theme created with Artisteer. Hot Fixed Matches, Welcome to the best betting platform, Lewandowski Bet Fixed Matches We are a group of bettors who are solely focused on football betting, and we work with a number of professional bettors from all over the world. Our main purpose is to provide our subscribers with precise fixed tips. To offer a complete service, our experts are not ignoring those overnight leagues but are offering you great tips for those leagues as well. Most of those matches are also available to watch via live stream. Below you will find our daily overnight selections. Always gamble responsibly; see the bottom of the page for more information on this.

  85. Merkur mag seinen Fokus zwar auf klassische und minimalistische Spiele legen, scheut aber trotzdem keine Mühen, den Wünschen moderner Kunden gerecht zu werden. Entsprechend können Merkur Spiele selbstverständlich auch mobil auf Smartphones und Tablets gespielt werden, und das sogar auf verschiedenen Wegen. Die mobilen Versionen sind über die optimierten Plattformen von Online Casinos verfügbar oder aber auch direkt über die Merkur App, die für Android- und iOS-Geräte verfügbar ist. Auch über die App können Sie Merkur online kostenlos spielen ohne Anmeldung. Hab schon in fast allen Online Casinos gespielt und Mr Green war mirt Abstand das beste.  “mit Informationen von onlinecasinosdeutschland.de” Heute lasse ich mir kein Angebot des online Casinos entgehen. Und bin auch vom Service begeistert. Die Auszahlungen im Mr Green Casino dauern nie länger als 24 Stunden. Meist geht es sogar noch schneller. Vor allem wenn man bereits länger Mitglied und der Account verifiziert ist.
    http://luckji.com/bbs/board.php?bo_table=free&wr_id=35577
    Das Online Poker Angebot ist so vielfältig wie das Spiel selbst. Beliebt und weit verbreitet sind beispielsweise Texas Hold’em, Omaha Hold’em, Stud Poker mit fünf oder sieben Karten, das klassische Five Card Draw Poker oder Varianten mit Joker. Live Hold’em wird mit echtem Croupier gespielt, meist in der Variante Casino Hold’em, bei der die Spieler sich nur gegen den Dealer behaupten, nicht aber gegeneinander. Mit dem € 70 + 10 Warm-up wurde gestern, den 13. Mai, im Grand Casino Aš das… Die CAPT Big Four verbindet vier Pokerturniere Europas. Die Casinos Seefeld, Bregenz, Velden und Baden bieten dabei jeweils eine CAPT Turnierwoche. An mindestens sieben Tagen wird volles Pokerprogramm geboten – der 4-tägige Texas Hold’em Main Event mit einem Buy-in von 2.000 Euro wartet mit mindestens 200 Teilnehmerinnen und Teilnehmern und 50.000 Turnierjetons Startdotation.

  86. online pharmacies in usa [url=https://fastdeliverypill.com/#]drugs without a doctor s prescription[/url] compare prescription drug prices

  99. discount prescription drugs [url=https://fastdeliverypill.com/#]drugs without a doctor s prescription[/url] my canadian drug store

  100. top 10 pharmacies in india [url=http://pharmacyindia.pro/#]indian pharmacy online[/url] india pharmacy mail order

  103. In the race of technology and manpower, we can say that both are at an equal level and both are equally important. Without manpower, technology cannot be improved and without technology, the world cannot improve. So yes, technology is useful but cannot fully replace manpower. Aayushi Kabra Technology refers to innovative ways of doing work through various smart means. The advancement of technology has played an important role in the development of human civilization. It has helped in improving the productivity of individuals and businesses. “Life Before the Internet and After,” StudyMoose, 05-Apr-2016. . Available: studymoose life-before-the-internet-and-after-essay. A A One of the negative effects of technology on children is that it may affect their academic success. As we mentioned at the very beginning, the reason for such problems is the extreme (unconscious) use of technology, not the technology itself.
    http://www.etechkorea.com/bbs/board.php?bo_table=free&wr_id=31646
    Team up with the essay writing industry’s finest writers and fulfill your academic goals easier as if you’ve been doing all the homework on your own. Also, get better grades and enjoy your student life to the maximum with Custom Essay Writer! Our writers are certified and have degrees in their specialized fields. Most have worked as writers before and have published material. With an extensive list of skilled professionals, to choose from, our experts are excited to help you. You get an opportunity to receive the best results and not to have to edit the paper yourself and miss your deadline. Students are also people who have variety of educational and personal tasks on daily basis. Some students work to pay for their studies and living, while others are involved in caring for their families or communities. That leads to a situation when students lack time to do additional research that is required in many written assignments. That is one of the reasons students look for custom writers to do the research and write custom papers.

  109. п»їlegitimate online pharmacies india [url=http://pharmacyindia.pro/#]online pharmacy india[/url] india online pharmacy

LEAVE A REPLY

Please enter your comment!
Please enter your name here

− 2 = 1