Dimensiones Lentamente Cambiantes

By bernabeu_dario on 17 Marzo, 2010 - 19:23
Business Intelligence

Las dimensiones lentamente cambiantes o SCD (Slowly Changing Dimensions) son dimensiones en las cuales sus datos tienden a modificarse a través del tiempo, ya sea de forma ocasional o constante, o implique a un solo registro o la tabla completa. Cuando ocurren estos cambios, se puede optar por seguir alguna de estas dos grandes opciones:

  • Registrar el historial de cambios.
  • Reemplazar los valores que sean necesarios.
Inicialmente Ralph Kimball planteó tres estrategias a seguir cuando se tratan las SCD: tipo 1, tipo 2 y tipo 3; pero a través de los años la comunidad de personas que se encargaba de modelar bases de datos profundizó las definiciones iniciales e incluyó varios tipos SCD más, por ejemplo: tipo 4 y tipo 6.

A continuación se detallará cada tipo de estrategia SCD:

  • SCD Tipo 1: Sobreescribir.
  • SCD Tipo 2: Añadir fila.
  • SCD Tipo 3: Añadir columna.
  • SCD Tipo 4: Tabla de Historia separada.
  • SCD Tipo 6: Híbrido.
De acuerdo a la naturaleza del cambio se debe seleccionar qué Tipo SCD se utilizará, en algunos casos resultará conveniente combinar varias técnicas.

Es importante señalar que si bien hay diferentes maneras de implementar cada técnica, es indispensable contar con claves subrogadas en las tablas de dimensiones para aplicar poder aplicar dichas técnicas.

Al aplicar las diferentes técnicas SCD, en muchos casos se deberá modificar la estructura de la tabla de dimensión con la que se este trabajando, por lo cual estas modificaciones son recomendables hacerlas al momento de modelar la tabla; aunque también puede hacerse una vez que ya se ha modelado y contiene datos, para lo cual al añadir por ejemplo una nueva columna se deberá especificar los valores por defecto que adoptarán los registros de la tabla.

NOTA: para todos los ejemplos a continuación, "id_Producto" es una clave subrogada que es clave principal de la tabla utilizada.

 

SCD Tipo 1: Sobreescribir

Este tipo es el más básico y sencillo de implementar, ya que si bien no guarda los cambios históricos, tampoco requiere ningún modelado especial y no necesita que se añadan nuevos registros a la tabla.

En este caso cuando un registro presente un cambio en alguno de los valores de sus campos, se debe proceder simplemente a actualizar el dato en cuestión, sobreescribiendo el antiguo. Para ejemplificar este caso, se tomará como referencia la siguiente tabla:

id_Producto Rubro Tipo Producto
1 Rubro 1 Tipo 1 Producto 1
Ahora, se supondrá que este producto ha cambiado de Rubro, y ahora a pasado a ser "Rubro 2", entonces se obtendrá lo siguiente:
id_Producto Rubro Tipo Producto
1 Rubro 2 Tipo 1 Producto 1
 
Usualmente este tipo es utilizado en casos en donde la información histórica no sea importante de mantener, tal como sucede cuando se debe modificar el valor de un registro porque tiene errores de ortografía. El ejemplo planteado es solo a fines prácticos, ya que con esta técnica, todos los movimientos realizados de "Producto 1", que antes pertenecían al "Rubro 1", ahora pasarán a ser del "Rubro 2", lo cual creará una gran inconsistencia en el DW.
 
 
SCD Tipo 2: Añadir fila

Esta estrategia requiere que se agreguen algunas columnas adicionales a la tabla de dimensión, para que almacenen el historial de cambios.

Las columnas que suelen agregarse son:

  • FechaInicio: fecha desde que entró en vigencia el registro actual. Por defecto suele utilizarse una fecha muy antigua, ejemplo: "01/01/1000".
  • FechaFin: fecha en la cual el registro actual dejó de estar en vigencia. Por defecto suele utilizarse una fecha muy futurista, ejemplo: "01/01/9999".
  • Versión: número secuencial que se incrementa cada nuevo cambio. Por defecto suele comenzar en "1".
  • Versión actual: especifica si el campo actual es el vigente. Este valor puede ser en caso de ser verdadero: "true" o "1"; y en caso de ser falso: "flase" o "0".
Entonces, cuando ocurra algún cambio en los valores de los registros, se añadirá una nueva fila y se deberá completar los datos referidos al historial de cambios.

Para ejemplificar este caso, se tomará como referencia la siguiente tabla:

id_Producto Rubro Tipo Producto
1 Rubro 1 Tipo 1 Producto 1
 
A continuación se añadirán las columnas que almacenarán el historial:
id_Producto Rubro Tipo Producto FechaInicio FechaFin Version VersionActual
1 Rubro 1 Tipo 1 Producto 1 01/01/1000 01/01/9999 1 true
 
Ahora, se supondrá que este producto ha cambiado de Rubro, y ahora a pasado a ser "Rubro 2", entonces se obtendrá lo siguiente:
id_Producto Rubro Tipo Producto FechaInicio FechaFin Version VersionActual
1 Rubro 1 Tipo 1 Producto 1 01/01/1000 06/11/2009 1 false
2 Rubro 2 Tipo 1 Producto 1 07/11/2009 01/01/9999 2 true
 
Como puede observarse, se lleva a cabo el siguiente proceso:
  • Se añade una nueva fila con su correspondiente clave subrogada ("id_Producto").
  • Se registra la modificación ("Rubro").
  • Se actualizan los valores de "FechaInicio" y "FechaFin", tanto de la fila nueva, como la antigua (la que presentó el cambio).
  • Se incrementa en uno el valor del campo "Version" que posee la fila antigua.
  • Se actualizan los valores de "VersionActual", tanto de la fila nueva, como la antigua; dejando a la fila nueva como el registro vigente (true).
Esta técnica permite guardar ilimitada información de cambios.
 
 

SCD Tipo 3: Añadir columna

Esta estrategia requiere que se agregue a la tabla de dimensión una columna adicional por cada columna cuyos valores se desea mantener un historial de cambios.

Para ejemplificar este caso, se tomará como referencia la siguiente tabla:

id_Producto Rubro Tipo Producto
1 Rubro 1 Tipo 1 Producto 1
 
A continuación se añadirá una columna para mantener el histórico de cambios sobre los datos de la columna "Rubro":
id_Producto Rubro RubroAnterior Tipo Producto
1 Rubro 1 - Tipo 1 Producto 1
 
Ahora, se supondrá que este producto ha cambiado de Rubro, y ahora a pasado a ser "Rubro 2", entonces se obtendrá lo siguiente:
id_Producto Rubro RubroAnterior Tipo Producto
1 Rubro 2 Rubro 1 Tipo 1 Producto 1
 
Como puede observarse, se lleva a cabo el siguiente proceso:
  • En la columna "RubroAnterior" se coloca el valor antiguo.
  • En la columna "Rubro" se coloca el nuevo valor vigente.
Esta técnica permite guardar una limitada información de cambios.
 
 

SCD Tipo 4: Tabla de Historia separada

Esta técnica se utiliza en combinación con alguna otra y su función básica es almacenar en una tabla adicional los detalles de cambios históricos realizados en una tabla de dimensión.

Esta tabla histórica indicará por ejemplo que tipo de operación se ha realizado (Insert, Update, Delete), sobre que campo y en que fecha. El objetivo de mantener esta tabla es el de contar con un detalle de todos los cambios, para luego analizarlos y poder tomar decisiones acerca de cuál técnica SCD podría aplicarse mejor. Por ejemplo, la siguiente tabla histórica registra los cambios de la tabla de dimensión "Productos", la cual supondremos emplea el SCD Tipo 2:

id_Producto Rubro_Cambio Tipo_Cambio Producto_Cambio FechaDeCambio
1 Insert - - 05/06/2000
2 Insert Insert - 25/10/2002
3 - Insert - 17/01/2005
4 - - Insert 18/02/2009
 
Tomando como ejemplo el primer registro de esta tabla, la información allí guardada indica lo siguiente:
  • El día "05/06/2000", el registro de la tabla de dimensión "Productos" con "id_Producto" igual a "1" sufrió un cambio de "Rubro", por lo cual se debío insertar ("Insert") una nueva fila con los valores vigentes.

 

 

SCD Tipo 6: Híbrido

Esta técnica combina las SCD Tipo 1, 2 y 3.

Se denomina SCD Tipo "6", simplemente porque: 6 = 1 + 2 +3.

slowly changing dimensions
scd
eglubi
dimensiones lentamente cambiantes

Subido por Hugo González Olaya. (no verificado) el 16 Octubre, 2010 - 08:27

-- Dimensión Cliente
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Comisiones].[FNX_Cliente]') AND type in (N'U'))
BEGIN
CREATE TABLE [Comisiones].[FNX_Cliente](
[IdSKCliente] [int] IDENTITY (1, 1) NOT NULL,
[IdCliente] [varchar](12) NOT NULL, -- IdCliente = Cedula
[Nombre] [varchar](80) NULL,
[IdCategoria] [varchar](4) NULL,
[Estrato] [varchar](3) NULL,
[TelefonoAvisar] [varchar] (10) NULL,
[FInicio] [date] NOT NULL CONSTRAINT [DF_FNX_Cliente_FInicio] DEFAULT (GetDate()),
[FFin] [date] NULL,
[Version] [smallint] NOT NULL,
[EsVersionActual] [bit] NOT NULL CONSTRAINT [DF_FNX_Cliente_EsVersionActual] DEFAULT (1),
) ON [fgDimensiones_dat];
End

IF Not EXISTS (SELECT name FROM sys.indexes WHERE name = N'PK_FNX_Cliente')
Begin
ALTER TABLE [Comisiones].[FNX_Cliente]
ADD CONSTRAINT [PK_FNX_Cliente] PRIMARY KEY CLUSTERED ( [IdSKCliente] ASC )
ON [fgDimensiones_dat];
End
GO

IF Not EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_FNX_Cliente_1')
Begin
CREATE NONCLUSTERED INDEX [IX_FNX_Cliente_1] ON [Comisiones].[FNX_Cliente]
( [IdCliente] ASC ) ON [fgDimensiones_ind]
End
GO

IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[Comisiones].[CK_FNX_Cliente_FFinFInicio]') AND parent_object_id = OBJECT_ID(N'[Comisiones].[FNX_Cliente]'))
ALTER TABLE [Comisiones].[FNX_Cliente] WITH CHECK ADD CONSTRAINT [CK_FNX_Cliente_FFinFInicio] CHECK ([FFin] >= [FInicio])
GO
IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[Comisiones].[CK_FNX_Cliente_FFinFInicio]') AND parent_object_id = OBJECT_ID(N'[Comisiones].[FNX_Cliente]'))
ALTER TABLE [Comisiones].[FNX_Cliente] CHECK CONSTRAINT [CK_FNX_Cliente_FFinFInicio]
GO

DROP TABLE [Comisiones].[tmpFNX_Cliente]
TRUNCATE TABLE [Comisiones].[FNX_Cliente]
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Comisiones].[tmpFNX_Cliente]') AND type in (N'U'))
BEGIN
CREATE TABLE [Comisiones].[tmpFNX_Cliente](
[IdSKCliente] [int] IDENTITY (1, 1) NOT NULL,
[IdCliente] [varchar](12) NOT NULL, -- IdCliente = Cedula
[ClienteNombre] [varchar](80) NULL,
[IdClienteCategoria] [varchar](4) NULL,
[ClienteEstrato] [varchar](3) NULL,
[TelefonoAvisar] [varchar] (10) NULL,

) ON [fgDimensiones_dat];
End

IF Not EXISTS (SELECT name FROM sys.indexes WHERE name = N'PK_tmpFNX_Cliente')
Begin
ALTER TABLE [Comisiones].[tmpFNX_Cliente]
ADD CONSTRAINT [PK_tmpFNX_Cliente] PRIMARY KEY CLUSTERED ( [IdSKCliente] ASC )
ON [fgDimensiones_dat];
End
GO

INSERT INTO [Comisiones].[tmpFNX_Cliente]([IdCliente],[ClienteNombre],[IdClienteCategoria],[ClienteEstrato],[TelefonoAvisar])
VALUES(1, 'C1', '1', '1', '000');
INSERT INTO [Comisiones].[tmpFNX_Cliente]([IdCliente],[ClienteNombre],[IdClienteCategoria],[ClienteEstrato],[TelefonoAvisar])
VALUES(2, 'C2', '2', '2', '000');
INSERT INTO [Comisiones].[tmpFNX_Cliente]([IdCliente],[ClienteNombre],[IdClienteCategoria],[ClienteEstrato],[TelefonoAvisar])
VALUES(3, 'C3', '3', '3', '000');

EXECUTE [Comisiones].[spETL_ActualizarFNX_Cliente] 0
Select * From [Comisiones].[tmpFNX_Cliente]
Select * From [Comisiones].[FNX_Cliente]
/*
--INSERT INTO [Comisiones].[tmpFNX_Cliente]([IdCliente],[ClienteNombre],[IdClienteCategoria],[ClienteEstrato],[TelefonoAvisar])
--VALUES(1, 'C1_2', '1_2', '1_2', '000');

--2
UPDATE [Comisiones].[tmpFNX_Cliente] SET
[ClienteNombre] = 'C1_2'
,[IdClienteCategoria] = 'C1_2'
,[ClienteEstrato] = '1_2'
WHERE [IdCliente] = 1;
EXECUTE [Comisiones].[spETL_ActualizarFNX_Cliente] 0
Select * From [Comisiones].[FNX_Cliente]

--3
UPDATE [Comisiones].[tmpFNX_Cliente] SET
[ClienteNombre] = 'C1_3'
,[IdClienteCategoria] = 'C1_3'
,[ClienteEstrato] = '1_3'
WHERE [IdCliente] = 1;
EXECUTE [Comisiones].[spETL_ActualizarFNX_Cliente] 0
Select * From [Comisiones].[FNX_Cliente]

--4
INSERT INTO [Comisiones].[tmpFNX_Cliente]([IdCliente],[ClienteNombre],[IdClienteCategoria],[ClienteEstrato],[TelefonoAvisar])
VALUES(4, 'C4', '4', '4', '000');
UPDATE [Comisiones].[tmpFNX_Cliente] SET
[ClienteNombre] = 'C1_4'
,[IdClienteCategoria] = 'C1_4'
,[ClienteEstrato] = '1_4'
WHERE [IdCliente] = 1;
EXECUTE [Comisiones].[spETL_ActualizarFNX_Cliente] 0
Select * From [Comisiones].[FNX_Cliente]

--5
UPDATE [Comisiones].[FNX_Cliente] SET
[FFin] = GetDate()
,[EsVersionActual] = 0
WHERE [IdCliente] = 1;
UPDATE [Comisiones].[FNX_Cliente] SET
[FFin] = Null
,[EsVersionActual] = 1
WHERE [IdSKCliente] = 4;
Select * From [Comisiones].[FNX_Cliente] order by [IdCliente];
EXECUTE [Comisiones].[spETL_ActualizarFNX_Cliente] 0
Select * From [Comisiones].[FNX_Cliente] order by [IdCliente];

--6
UPDATE [Comisiones].[tmpFNX_Cliente] SET
[ClienteNombre] = 'C1_5'
,[IdClienteCategoria] = 'C1_5'
,[ClienteEstrato] = '1_5'
WHERE [IdCliente] = 1;
EXECUTE [Comisiones].[spETL_ActualizarFNX_Cliente] 0
Select * From [Comisiones].[FNX_Cliente] order by [IdCliente];
*/

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Comisiones].[spETL_ActualizarFNX_Cliente]') AND type in (N'P', N'PC'))
DROP PROCEDURE [Comisiones].[spETL_ActualizarFNX_Cliente]
GO
CREATE procedure [Comisiones].[spETL_ActualizarFNX_Cliente]
@idLog int
as
/* ============================================================================================
Propósito: Actualiza los datos de la dimensión Clientes con los datos provenientes del proceso ETL.
Creado por: Hugo González Olaya
Fecha creación: 2010-10-15
Actualizado: 2010-10-15

Parametros:
@idLog: Código de ejecución

Ejemplo:
EXECUTE [Comisiones].[spETL_ActualizarFNX_Cliente] 0
Select * From [Comisiones].[FNX_Cliente]
============================================================================================ */
begin
set nocount on

-- Adicionar filas duplicadas a tabla de inconsistencia
;WITH DuplicadosCTE([IdCliente], NumeroFila)
AS
(
SELECT [IdCliente], ROW_NUMBER() OVER (PARTITION BY [IdCliente] ORDER BY [IdCliente])
From [Comisiones].[tmpFNX_Cliente]
)
INSERT INTO [Comisiones].[ETLInconsistencia]([IdProceso], [Tabla], [Estado], [CampoIncon], [ValorIncon], [Campo1], [Valor1], [Descripcion])
Select @idLog, 'FNX_Cliente', 'A', 'IdCliente', [IdCliente], 'IdCliente', [IdCliente], 'Fila duplicada borrada'
FROM DuplicadosCTE
WHERE NumeroFila > 1

-- Borar filas duplicadas
;WITH DuplicadosCTE([IdCliente], NumeroFila)
AS
(
SELECT [IdCliente], ROW_NUMBER() OVER (PARTITION BY [IdCliente] ORDER BY [IdCliente])
From [Comisiones].[tmpFNX_Cliente]
)
DELETE
FROM DuplicadosCTE
WHERE NumeroFila > 1

/*
Tabla para insertar SCD Tipo 2
Este es un cambio al MERGE para evitar filas repetidas con versión superior y
También por que MERGE actualza bien el número de versión y cierre de vigencia cuando la versión actual es
la última, de lo contrario, adiciona filas cuando es una versión intermedia
*/

DECLARE @SCD_Clientes TABLE(
[Accion] [varchar](20) NULL,
[IdSKCliente] [int] NULL,
[IdCliente] [varchar](12) NOT NULL,
[ClienteNombre] [varchar](80) NULL,
[IdClienteCategoria] [varchar](4) NULL,
[ClienteEstrato] [varchar] (10) NULL,
[TelefonoAvisar] [varchar] (10) NULL,
[FFin] [date] NULL,
[Version] [smallint] NULL,
[VersionAnterior] [smallint] NULL,
[EsVersionActual] [bit] NULL,
[Insertado] [bit] NULL
);

-- Insertar cambio SCD tipo 2 en tabla de dimensiones
--INSERT INTO [Comisiones].[FNX_Cliente] ( [IdCliente], [Nombre], [IdCategoria], [Estrato], [TelefonoAvisar],
-- [FFin], [Version], [EsVersionActual])
--SELECT M.[IdCliente], M.[ClienteNombre], M.[IdClienteCategoria], M.[ClienteEstrato], M.[TelefonoAvisar],
-- NULL, M.[VersionNueva], 1

-- Insertar cambio SCD tipo 2 en tabla temporal
INSERT INTO @SCD_Clientes ([Accion], [IdSKCliente], [IdCliente], [ClienteNombre], [IdClienteCategoria],
[ClienteEstrato], [TelefonoAvisar], [FFin], [Version], [VersionAnterior], [EsVersionActual])
SELECT M.[Accion], M.[IdSKCliente], M.[IdCliente], M.[ClienteNombre], M.[IdClienteCategoria],
M.[ClienteEstrato], M.[TelefonoAvisar], M.[FFin], M.[VersionNueva], M.[VersionAnterior], 1
FROM
( MERGE [Comisiones].[FNX_Cliente] As C
USING (SELECT DISTINCT T.[IdCliente], T.[ClienteNombre], T.[IdClienteCategoria], T.[ClienteEstrato], T.[TelefonoAvisar]
FROM [Comisiones].[tmpFNX_Cliente] As T -- [Comisiones].[tmpFNX_PedidoDetalle_PedidosInstalados]
) AS Origen
ON (C.[IdCliente] = Origen.[IdCliente])

WHEN NOT MATCHED BY TARGET THEN
-- Insertar nuevos valores
INSERT ([IdCliente], [Nombre], [IdCategoria], [Estrato], [TelefonoAvisar], [FInicio], [FFin], [Version], [EsVersionActual])
VALUES ([IdCliente], [ClienteNombre], [IdClienteCategoria], [ClienteEstrato], [TelefonoAvisar], GETDATE(), Null, 1, 1)

WHEN MATCHED AND C.[EsVersionActual] = 1 AND
(Origen.[ClienteNombre] <> C.[Nombre] OR Origen.[IdClienteCategoria] <> C.[IdCategoria] OR
Origen.[ClienteEstrato] <> C.[Estrato] OR Origen.[TelefonoAvisar] <> C.[TelefonoAvisar]) THEN
-- Cerrar vigencia por adición de nueva versión
UPDATE SET [FFin] = GETDATE(), [EsVersionActual] = 0

OUTPUT $Action As [Accion], Inserted.[IdSKCliente], Origen.[IdCliente], Origen.[ClienteNombre],
Origen.[IdClienteCategoria], Origen.[ClienteEstrato],
Origen.[TelefonoAvisar], Deleted.[FFin], Deleted.[Version] + 1 As [VersionNueva], Deleted.[Version] As [VersionAnterior]
) AS M;
--WHERE M.[Accion] = 'UPDATE'; -- Usar filtro cuando inserta en tabla de dimensiones

-- Actualizar bandera
UPDATE M SET
[Insertado] = 1
FROM @SCD_Clientes M LEFT JOIN [Comisiones].[FNX_Cliente] C ON M.[IdCliente] = C.[IdCliente] AND M.[ClienteNombre] = C.[Nombre] AND
M.[IdClienteCategoria] = C.[IdCategoria] AND M.[ClienteEstrato] = C.[Estrato] AND M.[TelefonoAvisar] = C.[TelefonoAvisar]
WHERE C.[IdCliente] IS NULL AND M.Accion = 'UPDATE' --'INSERT';

-- Insertar cambio SCD tipo 2 en tabla de dimension
INSERT INTO [Comisiones].[FNX_Cliente] ([IdCliente], [Nombre], [IdCategoria], [Estrato], [TelefonoAvisar],
[FInicio], [FFin], [Version], [EsVersionActual])
SELECT M.[IdCliente], M.[ClienteNombre], M.[IdClienteCategoria], M.[ClienteEstrato], M.[TelefonoAvisar],
GETDATE(), Null, (SELECT MAX(CMax.[Version]) + 1 FROM [Comisiones].[FNX_Cliente] CMax
WHERE CMax.[IdCliente] = M.[IdCliente]), 1
FROM @SCD_Clientes M LEFT JOIN [Comisiones].[FNX_Cliente] C ON M.[IdCliente] = C.[IdCliente] AND M.[ClienteNombre] = C.[Nombre] AND
M.[IdClienteCategoria] = C.[IdCategoria] AND M.[ClienteEstrato] = C.[Estrato] AND M.[TelefonoAvisar] = C.[TelefonoAvisar]
WHERE C.[IdCliente] IS NULL AND M.Accion = 'UPDATE' --'INSERT'
ORDER BY M.[IdCliente];

-- Ajustar vigencia modificada en el MERGE
UPDATE C SET
[FFin] = M.[FFin]
,[EsVersionActual] = 1
FROM @SCD_Clientes M INNER JOIN [Comisiones].[FNX_Cliente] C ON M.[IdSKCliente] = C.[IdSKCliente]
WHERE M.[VersionAnterior] = C.[Version] AND M.[Insertado] <> 1;

UPDATE C SET
[FFin] = M.[FFin]
,[EsVersionActual] = 1
FROM @SCD_Clientes M INNER JOIN [Comisiones].[FNX_Cliente] C ON M.[IdSKCliente] = C.[IdSKCliente]
WHERE M.[VersionAnterior] = C.[Version] AND M.[Insertado] IS NULL
AND (M.[ClienteNombre] = C.[Nombre] OR M.[IdClienteCategoria] <> C.[IdCategoria] OR
M.[ClienteEstrato] <> C.[Estrato] OR M.[TelefonoAvisar] <> C.[TelefonoAvisar]);

set nocount off
End
GO