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

By
roopesh.valluru
-
278
30714

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

278 COMMENTS

  3. Mental Retardation Mental retardation can appear as emotional immaturity.
    View specials coming from first-rate pharmacies where you can vardenafil levitra directory that lists online pharmacy websites?
    This is also called ‘medical termination’ of pregnancy, and is different from ‘surgical termination’.

  12. Старые срубные дома — это не только след о прошлом, но и особенные архитектурные постройки, которые нуждаются в тщательном обслуживании. Ремонт таких домов требует особом подходе, так как оберегание первоначальных элементов конструкции и использование природных материалов служат важной задачей. https://fish-industry.ru/novosti/3819-effektivnyy-remont-kommunikaciy-v-zagorodnom-dome.html – Замена сгнивших опор, поддержание фундамента, реставрация оконных рам и кровли — все эти процессы должны выполняться с вниманием к особенностей брусового строительства, чтобы поддержать привлекательность и продолжительность службы дома.

    Увеличение загородному дому — это ещё одна основная задача, с которой сталкиваются владельцы компактных строений. Чтобы расширить жилое пространство, можно добавить веранду, террасу или даже полноценную дополнительную комнату. При этом нужно учитывать баланс между новыми конструкциями и оригинальной частью строения. Внедрение технологичных ремонтных материалов позволит оптимизировать теплоизоляцию и оптимизировать издержки на отопление, что особенно актуально в условиях летнего проживания.

    Кроме того, необходимо принять к сведению на важность согласования подобных действий с местными властями. Иногда расширение обязывает выдачи документов и разработки проектной документации. Важно придерживаться строительные требования и правила, чтобы снизить возможных сложностей в будущем. Инженеры смогут помочь подготовить проект, который будет учитывать не только индивидуальным пожеланиям, но и всем юридическим и строительным пунктам.

    Таким образом, ремонт и экспансия летнего дома — это не просто шаги модернизации здания, но и способ сделать его удобным и удобным для проживания. Сохранение устоев деревянного строительства и применение актуальных технологий помогут обновить дом новый внешний вид, не сохраняя его уникального очарования.

  219. vibracion de motor
    Aparatos de ajuste: fundamental para el operación uniforme y eficiente de las maquinarias.

    En el ámbito de la tecnología avanzada, donde la productividad y la fiabilidad del dispositivo son de suma relevancia, los sistemas de balanceo cumplen un papel esencial. Estos sistemas dedicados están desarrollados para equilibrar y fijar elementos móviles, ya sea en herramientas manufacturera, transportes de desplazamiento o incluso en equipos domésticos.

    Para los técnicos en conservación de sistemas y los técnicos, utilizar con dispositivos de balanceo es fundamental para promover el rendimiento suave y confiable de cualquier sistema dinámico. Gracias a estas opciones modernas avanzadas, es posible limitar sustancialmente las sacudidas, el ruido y la tensión sobre los cojinetes, extendiendo la duración de elementos valiosos.

    Igualmente significativo es el tarea que cumplen los sistemas de balanceo en la servicio al cliente. El asistencia profesional y el mantenimiento permanente aplicando estos sistemas facilitan ofrecer soluciones de alta calidad, incrementando la satisfacción de los usuarios.

    Para los propietarios de negocios, la aporte en unidades de calibración y detectores puede ser clave para mejorar la efectividad y rendimiento de sus dispositivos. Esto es particularmente trascendental para los empresarios que gestionan modestas y pequeñas organizaciones, donde cada aspecto importa.

    Por otro lado, los equipos de calibración tienen una vasta aplicación en el ámbito de la fiabilidad y el supervisión de nivel. Posibilitan detectar probables defectos, evitando reparaciones caras y averías a los sistemas. Más aún, los indicadores obtenidos de estos aparatos pueden utilizarse para mejorar sistemas y mejorar la visibilidad en plataformas de exploración.

    Las zonas de utilización de los equipos de equilibrado incluyen variadas sectores, desde la fabricación de vehículos de dos ruedas hasta el control ambiental. No afecta si se trata de enormes elaboraciones productivas o modestos talleres domésticos, los aparatos de equilibrado son necesarios para asegurar un desempeño eficiente y sin presencia de detenciones.

  220. Vulkan vegas casino no deposit free spins bonus codes according to the number of players searching for it, online casinos adopt them too. Two MGM Resorts properties in Mississippi offer on-site BetMGM mobile wagering, and the casino with the same name is a chance to live the good life. The Vulkan Vegas welcome package is exceptionally WOW. This multi-tiered bonus is structured to generously reward the initial deposits of new members, potentially including additional free spins on popular slots. The welcome bonus is substantial and comes with player-friendly terms and conditions, making it an attractive proposition for newcomers. Play top online casinos with generous welcome and no deposit bonuses If the conditions are met, player receives a 100% 150% 200% match deposit bonus + 100 free spins in Elven Princesses (Evoplay Entertainment) slot. Maximum amount of bonus is €2,000.
    https://cabine027.com.br/2025/03/14/jetx-estrategias-para-o-jogo-do-foguete-aquele-alcancar-arame-online/
    • PENN Entertainment debuts new online blackjack game on Hollywood Casino: Major gambling operator Penn Entertainment has launched a new online blackjack game on its Hollywood Casino site in New Jersey. You can email the site owner to let them know you were blocked. Please include what you were doing when this page came up and the Cloudflare Ray ID found at the bottom of this page. Weiss Casino, a crypto casino established in 2023 and licensed under Curacao, offers a unique gaming experience with its tokenized rakeback system. The casino provides various incentives, including a generous welcome package of 350% Bonus up to 7,000 USDT + 260 Free Spins + No KYC, along with a multi-tiered loyalty program spanning 30 levels. Players can enjoy daily and weekly cashback rewards of up to 40%, facilitated by the casino’s own token, WEFT.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

sixty five − fifty five =