¿Te has preguntado cómo gestionar el historial de tus datos de manera fácil y eficiente? Sigue leyendo para conocer una breve introducción a conceptos clave sobre las dimensiones lentamente cambiantes (SCD). Aprenderás a manejar la carga y transformación de datos mientras mantienes un historial completo, optimizando así tus análisis. Además, descubrirás cómo hacerlo de forma sencilla utilizando procedimientos almacenados.
¡Comenzamos!
En el ámbito del análisis de datos, mantener un histórico es esencial para comprender el comportamiento de los datos a lo largo del tiempo. En nuestros proyectos, siempre trabajaremos con dimensiones que pueden sufrir cambios y que necesitamos controlar para reflejarlos en nuestros informes. Esto nos ayuda a realizar un análisis con certeza.
Este post no tiene como objetivo profundizar sobre los conocimientos sobre las dimensiones lentamente cambiantes. Para ello os voy a dejar algunos posts que os podrán ayudar:
¿Qué son las Dimensiones Lentamente Cambiantes (SCD)?
Las dimensiones lentamente cambiantes, conocidas por su acrónimo SCD (Slowly Changing Dimension), son aquellas dimensiones que pueden sufrir cambios a lo largo del tiempo y sin demasiada frecuencia. Un ejemplo típico es la categoría de un artículo.
Para facilitar la lectura, utilizaremos el acrónimo SCD durante el resto del artículo.
En este post, aplicaremos la estrategia SCD Tipo 2 con el objetivo de mantener un histórico de los cambios en los datos. Este enfoque es ideal para analizar cómo evolucionan los datos.
¿Por qué utilizar SCD Tipo 2 en un Data Warehouse?
El SCD Tipo 2 es especialmente útil cuando queremos analizar datos y asegurarnos de que los cambios en los atributos de las dimensiones no sobrescriban los valores anteriores. Implementar esta estrategia en nuestro Data Warehouse nos ayuda en los siguientes aspectos:
- Análisis de tendencias históricas: Mantener un registro de los datos históricos nos permite analizar cómo han cambiado a lo largo del tiempo.
- Toma de decisiones: Conocer cuándo y cómo nos permite una mejor toma de decisiones basada en la evolución de los datos.
- Cumplimiento de normativa: En algunas empresas, exigen mantener un registro histórico para las auditorías.
Microsoft Fabric: La plataforma para gestionar tus SCD Tipo 2
Hoy en día, todo el mundo habla de Microsoft Fabric, y no es para menos. Es una plataforma de análisis y gestión de datos que combina las principales herramientas como Power BI, Azure Data Lake, Synapse Analytics y otros servicios, todo desde un único sitio. Por ello, lo hemos elegido como foco de este artículo, con el objetivo de explotar su potencial y ver sus múltiples aplicaciones en proyectos cotidianos. Para más información, os dejo el siguiente enlace:
¿Cómo implementar SCD Tipo 2 en nuestro proyecto de Fabric?
Vamos a suponer que ya tenemos asimilados los conceptos de SCD y Fabric y estamos listos para ponernos manos a la obra en el siguiente ejemplo. A continuación, resumimos las principales tareas que vamos a abordar en este artículo:
1. Crear un Lakehouse
2. Crear un Data Warehouse
3. Crear un flujo de datos Gen2
4. Crear un pipeline (canalización de datos)
5. Crear los procedimientos almacenados
Entiendo que puede que no controlemos bien esos conceptos en Fabric. No os preocupéis, os puedo dejar aquí un curso muy recomendado donde aprenderás todo lo necesario para trabajar con Fabric:
Implementacion de un datalake con el curso de Microsoft Fabric
¡Después de esto ya estamos listos! ¡Manos a la obra! 😊
Tablas de Origen de Ejemplo
Para esta práctica utilizaremos dos ficheros Excel como origen de datos.
El primer fichero es ‘customer’, donde guardaremos los datos de clientes con los siguientes campos:
Continuamos con ‘sales’, donde tendremos registrado los datos de las ventas.
Creación de Lakehouse y flujo de datos Gen2
Para continuar con la práctica será necesario crear un nuevo Lakehouse dentro de nuestra área de trabajo. En caso de querer usar un Lakehouse existente, podemos saltarnos este paso.
Una vez que lo esté creado, tendremos dos directorios: las tablas y los ficheros.
Como puedes ver, en tablas tenemos ‘sales’ y ‘customer’, que son los nombres de mis ficheros origen. A continuación veréis cómo podéis cargarlo.
Una vez que tenemos creado nuestro Lakehouse, vemos cómo podemos cargar nuestros ficheros origen.
Para cargar distintos orígenes de datos tenemos varias formas. Entre ellas esta como flujo de datos Gen2. Vamos a nuestra área de trabajo y añadimos un nuevo elemento, que en nuestro caso es Flujo de datos.
Cuando tenemos los dos ficheros cargados, podremos aplicar transformaciones como por ejemplo descartar nulos o campos vacíos o que los campos de fecha los modifique a tipo de fecha. Podremos realizar todas las transformaciones necesarias para que nuestros datos esten preparado para pasar a staging.
Después debemos seleccionar un destino de datos donde le indicaremos el Lakehouse que hemos creado y publicamos. Con esto ya tenemos nuestro flujo de datos.
Lo veremos de la siguiente forma una vez cargado el flujo de datos:
Crear Data Warehouse con Esquema en Estrella
Para este ejemplo de Data Warehouse, hemos querido replicarlo lo más parecido a un proyecto real donde trabajaremos con un modelo en estrella con sus dimensiones y su tabla de hechos.
Codigo Para Crear Customer:
Para este ejemplo de Data Warehouse hemos querido replicarlo lo más parecido a un proyecto real donde trabajaremos con un modelo en estrella con sus dimensiones y su tabla de hechos.
Codigo Para Crear Sales:
En la tabla de hechos sales, tiene el campo CustomerKey como clave subrogada que la relaciona con la dimensión Customer.
Crear procedimiento almacenado para actualizar dimensión Customer.
En este script lo que vamos a verificar es la detección de cambios en la tabla de staging de Customer. En este caso solo tendremos en cuenta los cambios del campo City. Si encuentra el cambio, modifica el registro anterior el campo toDate e insertar el nuevo registro:
CREATE PROC [ETL].[UpsertDimCustomer]
AS
BEGIN
–inicializamos la fecha inicial para la primera carga
Declare @FirstLoadDate DATE
Set @FirstLoadDate=‘2024-01-01’
Declare @MaxCustomerKey INT
–guardamos en una variable la ultima clave la tabla de customer
–actualizamos el campo ToDate(hasta) con la fecha actual solo de los registros con cambios en la ciudad
–esos cambios tiene que estar reflejados en la tabla de Customer que se carga en el LH
update WH_SCD_Type2.Dim.Customer
set ToDate=Convert(Date,dateadd(day,-1,GetDate()))
where CustomerKey in
(
select
dm.CustomerKey
from
Formacion_SCD_Type2.dbo.customer stg
left outer join WH_SCD_Type2.Dim.Customer dm
on
stg.CustomerID=dm.CustomerID
and dm.ToDate is null
where
stg.City<>dm.City
)
— Obtenemos el maximos de la clave
select @MaxCustomerKey=isnull(max(CustomerKey),0) from WH_SCD_Type2.Dim.Customer
— insert
insert into WH_SCD_Type2.Dim.Customer(CustomerID,Fullname,City,CustomerKey,FromDate)
select
stg.CustomerID,
stg.Name,
stg.City,
ROW_NUMBER() OVER(ORDER BY stg.CustomerID ASC)+@MaxCustomerKey AS CustomerKey, –Genera un numero a partir de CustomerID en ordes asce y le suma el valor de MaxCustomerKey
case
@MaxCustomerKey when 0 then @FirstLoadDate else Convert(Date,GetDate()) –Si MaxCustomerKey vale 0 le asigna la variable FirstLoadDate si no, la fecha actual
end as FromDate
from
Formacion_SCD_Type2.dbo.customer stg
left outer join WH_SCD_Type2.Dim.Customer dm
on
stg.CustomerID=dm.CustomerID and dm.ToDate is null
where dm.CustomerKey is null
END
GO
Crear procedimiento almacenado para insertar nuevos registros en Sales:
En este paso, se inserta los nuevos registros teniendo en cuenta la actualización de la dimensión de Customer y la última fecha de la actualización del registro en customer:
CREATE PROC [ETL].[InsertNewFactSalesRecords]
AS
BEGIN
declare @LastETLDate Date
–guarda la ultima fecha de actualizacion de la tabla de hechos
select @LastETLDate=max(OrderDate) from WH_SCD_Type2.Fact.Sales
–insertamos los nuevos registros con la nueva customerKey de aquellos registros donde la fecha este entre la ultima carga hasta el infinito y donde OrderDate sea menor de la ultima fecha gurada
insert into WH_SCD_Type2.Fact.Sales(CustomerKey,OrderNumber,SalesAmount,OrderDate)
select
dim.CustomerKey,
fact.OrderNumber,
fact.SalesAmount,
CONVERT(DATE, fact.OrderDate, 103)
from
Formacion_SCD_Type2.dbo.sales fact
inner join WH_SCD_Type2.Dim.Customer dim
on
fact.Customer=dim.CustomerID
and CONVERT(DATE, fact.OrderDate, 103) between dim.FromDate and isnull(dim.ToDate,‘9999-12-31’)
where CONVERT(DATE, fact.OrderDate, 103)>isnull(@LastETLDate,’1900-01-01′)
END
GO
Una vez que tenemos listo las dimensiones, hechos y procedimientos, vamos al modelo de nuestro Data Warehouse y vamos a relacionar “Customer” con “Sales”:
Creación de ETL Pipeline
La última parte sería crear nuestro ETL. Para ello, vamos a crear un canalizador que orquestará los tres pasos:
1. Carga de origen
2. Procedimiento de actualización de “customer”
3. Procedimiento de ingesta de “sales”
Prueba Final
Ya tenemos nuestro flujo de datos desde el origen hasta Data Warehouse, donde facilitamos unos ficheros en origen, aplicamos una transformación y finalmente tenemos dos procedimientos almacenados que verificarán los cambios desde origen e insertarán los nuevos registros.
Ya solo nos queda probar que todo funciona correctamente. El primer lanzamiento de la ETL nos cargará todos los datos inserción, ya que la tabla esta vacía.
Debemos tener en cuenta que los ficheros que subimos con el flujo de datos Gen2 se quedan guardados en el sharepoint de la cuenta suscrita a Fabric.
Aquí deberemos hacer los cambios.
Vamos a modificar los dos ficheros tanto “Customer” como “Sales”.
Las tablas en el Data Warehouse tienen los siguientes datos:
“Customer”:
“Sales”:
Vamos a aplicar los siguientes cambios:
Hemos modificado la ciudad para Ana Delgado, que pasa de Granada a Linares y hemos añadido un cliente nuevo que es Jose Luis Martínez.
Añadimos 2 registros nuevos a la tabla de ventas.
Prueba Final
Tras ejecutar la ETL nuevamente, observamos que:
- Se ha actualizado el antiguo registro de Ana Delgado y se ha añadido un nuevo registro con la ciudad actualizada.
- Los nuevos registros de ventas se han insertado correctamente en la tabla ‘Sales’.
Dimension Customer
Tabla de hechos Sales
Con estos pasos hemos implementado exitosamente las dimensiones lentamente cambiantes Tipo 2 en Microsoft Fabric, permitiendo mantener un histórico de los cambios y realizar análisis más precisos y completos de nuestros datos a lo largo del tiempo.
Conclusión
Espero que, con este artículo, hayan quedado claro los conceptos básicos de las dimensiones lentamente cambiantes SCD, en especial la de tipo 2 y cómo podemos implementarlo en nuestro Data Warehouse en Fabric. Este enfoque es clave para el análisis de tendencias y la toma de decisiones basada en datos pasados.
Será útil para hacer seguimiento de la evolución de los datos teniendo en cuenta el valor que nos aportan, evitando la sobreescritura de la información.
Esto es vital para mejorar la calidad de los informes permitiendo un análisis histórico más preciso dentro de un entorno gestionado con herramientas eficientes que ofrece Fabric 😉
Juan Daniel Bocio Montero
Data & AI Specialist
