En nuestro primer post de la serie se explicaban diferentes métodos para cargar una dimensión con atributos de Tipo 2, en el segundo post de la serie se explicaban los diferentes métodos para cargar una tabla de hechos a partir de una dimensión con atributos de Tipo 2 y en este tercer post vamos a comentar como cargar una dimensión con atributos de Tipo 2 usando una columna de tipo CHECKSUM.
Por norma general se dice que las tablas de dimensiones son tablas anchas y cortas, es decir, tablas con muchas columnas y pocas filas (esto es una generalización pero como siempre hay excepciones). Imaginar por ejemplo un escenario en el que tuviéramos una tabla de dimensión con muchas columnas, a la hora de comprobar contra el origen si alguna de esas columnas ha cambiado para hacer una actualización del registro, comprobar una por una se puede convertir en una tarea un poco tediosa, en estos casos puede ser una buena opción usar una columna de tipo CHECKSUM para saber comprobando una solo columna si ha habido cambios o no.
Vamos a explicar todo esto con un ejemplo para que se vea más claro, vamos a seguir con nuestro escenario de los post anteriores de las tiendas de comics. Vamos a añadir columnas a nuestra tabla de origen y de dimensión de las tiendas (no muchas porque es un ejemplo, pero imaginar que hubieran muchas más).
Estos son los script de creación de las tablas:
IF OBJECT_ID('DIM_TIENDAS_CHECKSUM') IS NOT NULL
DROP TABLE DIM_TIENDAS_CHECKSUM
CREATE TABLE DIM_TIENDAS_CHECKSUM
(
ID_TIENDA INT IDENTITY(1,1) PRIMARY KEY,
COD_TIENDA VARCHAR(4),
NOMBRE VARCHAR(100),
JEFE VARCHAR(100),
CIUDAD VARCHAR(100),
PROVINCIA VARCHAR(100),
PAIS VARCHAR(100),
DIRECCION VARCHAR(100),
CODIGO_POSTAL VARCHAR(5),
TELEFONO VARCHAR(10),
FAX VARCHAR(10),
EMAIL VARCHAR(100),
PAGINA_WEB VARCHAR(100),
TIPO1_CHECKSUM AS CHECKSUM(NOMBRE, TELEFONO, FAX, EMAIL, PAGINA_WEB),
TIPO2_CHECKSUM AS CHECKSUM(JEFE, CIUDAD, PROVINCIA, PAIS, DIRECCION, CODIGO_POSTAL),
FEC_INICIO INT,
FEC_FIN INT
)
IF OBJECT_ID('STG_TIENDAS_CHECKSUM') IS NOT NULL
DROP TABLE STG_TIENDAS_CHECKSUM
CREATE TABLE STG_TIENDAS_CHECKSUM
(
COD_TIENDA VARCHAR(4) PRIMARY KEY,
NOMBRE VARCHAR(100),
JEFE VARCHAR(100),
CIUDAD VARCHAR(100),
PROVINCIA VARCHAR(100),
PAIS VARCHAR(100),
DIRECCION VARCHAR(100),
CODIGO_POSTAL VARCHAR(5),
TELEFONO VARCHAR(10),
FAX VARCHAR(10),
EMAIL VARCHAR(100),
PAGINA_WEB VARCHAR(100),
TIPO1_CHECKSUM AS CHECKSUM(NOMBRE, TELEFONO, FAX, EMAIL, PAGINA_WEB),
TIPO2_CHECKSUM AS CHECKSUM(JEFE, CIUDAD, PROVINCIA, PAIS, DIRECCION, CODIGO_POSTAL)
)
IF OBJECT_ID('STG_TIENDAS_CHECKSUM_HISTORICO') IS NOT NULL
DROP TABLE STG_TIENDAS_CHECKSUM_HISTORICO
CREATE TABLE STG_TIENDAS_CHECKSUM_HISTORICO
(
COD_TIENDA VARCHAR(4),
NOMBRE VARCHAR(100),
JEFE VARCHAR(100),
CIUDAD VARCHAR(100),
PROVINCIA VARCHAR(100),
PAIS VARCHAR(100),
DIRECCION VARCHAR(100),
CODIGO_POSTAL VARCHAR(5),
TELEFONO VARCHAR(10),
FAX VARCHAR(10),
EMAIL VARCHAR(100),
PAGINA_WEB VARCHAR(100),
TIPO INT,
EXECUTION_ID UNIQUEIDENTIFIER,
FEC_EJECUCION DATETIME,
PRIMARY KEY (COD_TIENDA,FEC_EJECUCION)
)
Hemos añadido las columnas de CIUDAD, PROVINCIA, PAIS, DIRECCION, CODIGO_POSTAL, TELEFONO, FAX, EMAIL y PAGINA_WEB. Ahora nuestras columnas de Tipo 2 serán: JEFE, CIUDAD, PROVINCIA, PAIS, DIRECCION y CODIGO_POSTAL y nuestras columnas de Tipo 1 serán: NOMBRE, TELEFONO, FAX, EMAIL y PAGINA_WEB.
Como podéis observar hemos añadido 2 nuevas columnas más de tipo CHECKSUM una para las columnas con atributos de Tipo 1 y otro para los de Tipo 2, tanto en la tabla de origen de Staging como en la tabla de dimensión del DataWarehouse:
TIPO1_CHECKSUM AS CHECKSUM(NOMBRE, TELEFONO, FAX, EMAIL, PAGINA_WEB),
TIPO2_CHECKSUM AS CHECKSUM(JEFE, CIUDAD, PROVINCIA, PAIS, DIRECCION, CODIGO_POSTAL)
Lo que vamos a hacer en nuestro ejemplo será utilizar estas columnas para comprobar si ha habido cambios entre el origen y el destino en vez de hacer una comprobación para cada una de las columnas.
La estructura del paquete será la misma que el paquete que usamos en el post 1 (enlace del post 1) para cargar la dimensión usando el componente “LookUp” el único cambio que vamos a hacer será en el componente “Conditional Split” que es la parte en dónde se comprueba si el registro corresponde a una modificación de Tipo 1, Tipo 2 o si es una inserción.
Esta será la estructura de nuestro “Control Flow”, con una “Data Flow” para cargar la dimensión Tiendas y tres “SQL Task” que se encargarán de hacer los updates para los atributos de Tipo 1 y 2 y las inserciones:
Y esta es la estructura de nuestro “Data Flow” “Carga Dim Tiendas”:
Configuramos nuestro componente origen con la siguiente consulta, como vamos a utilizar el componente “LookUp” no será necesario que los datos vengan ordenados:
Configuramos nuestro componente “LookUp”, añadimos un WHERE FEC_FIN IS NULL para quedarnos solo con la última versión de la tienda:
Y ahora viene la parte diferente con respecto al paquete que creamos en nuestro primer post. En la configuración del componente “Conditional Split” en vez de comparar columna a columna ahora solo vamos a comparar la columnas de CHECKSUM para el Tipo 1 y Tipo 2.
El resto de componentes del “Data Flow” tienen la misma configuración que el paquete que creamos para el primer post de la serie. Tres “Derived column” para rellenar la columna TIPO, que será 1 para modificaciones de Tipo 1, 2 para modificaciones de Tipo 2 y 3 para inserciones, luego un “Union all” para unir todos los flujos, seguido de otro “Derived column” para rellenar nuestras columnas de auditado en la tabla de históricos y por último la inserción en nuestra tabla de histórico.
Ahora pasamos a los componentes “SQL Task” de nuestro “Control Flow” que se encargan de realizar los updates e inserciones en la tabla de dimensión.
La consulta para los “Updates Tipo 1” quedaría así:
UPDATE DIM SET DIM.NOMBRE=STG.NOMBRE, DIM.TELEFONO=STG.TELEFONO, DIM.FAX=STG.FAX, DIM.EMAIL=STG.EMAIL, DIM.PAGINA_WEB=STG.PAGINA_WEB FROM DIM_TIENDAS_CHECKSUM DIM INNER JOIN STG_TIENDAS_CHECKSUM_HISTORICO STG ON STG.COD_TIENDA=DIM.COD_TIENDA WHERE TIPO=1 AND EXECUTION_ID=?
Esta consulta actualiza en bloque todos los atributos de Tipo 1 de nuestra dimensión. La interrogación en la consulta es un parámetro que tenemos que pasarle, para ello abrimos el componente y vamos a la parte de “Parameter Mapping” pulsamos sobre “Add” y seleccionamos la variable de sistema “ExecutionInstanceGUID”.
La consulta para “Cerrar registro Tipo 2” sería la siguiente:
UPDATE DIM SET FEC_FIN=CONVERT(CHAR(8), GETDATE(), 112) FROM DIM_TIENDAS_CHECKSUM DIM INNER JOIN STG_TIENDAS_CHECKSUM_HISTORICO STG ON STG.COD_TIENDA=DIM.COD_TIENDA WHERE TIPO=2 AND FEC_FIN IS NULL AND EXECUTION_ID=?
Con esta consulta actualizamos la fecha de fin de los registros que tienen un atributo de Tipo 2 que han sufrido un cambio, con esto cerramos la vigencia del registro.
Y por último las “Inserciones”:
INSERT INTO DIM_TIENDAS_CHECKSUM SELECT COD_TIENDA, NOMBRE, JEFE, CIUDAD, PROVINCIA, PAIS, DIRECCION, CODIGO_POSTAL, TELEFONO, FAX, EMAIL, PAGINA_WEB, CONVERT(CHAR(8), GETDATE(), 112), NULL FROM STG_TIENDAS_CHECKSUM_HISTORICO WHERE TIPO IN (2,3) AND EXECUTION_ID=?
Insertamos tanto los registros nuevos (Tipo 3) como la nueva versión de los registros con atributos de Tipo 2 que han cambiado.
Y ya tendríamos nuestro paquete listo para usar 
Carga de datos de ejemplo
Vamos a realizar una carga con datos de ejemplo para ver si nuestro paquete efectivamente hace lo que nosotros queremos, para ello lo primero que vamos a hacer es insertar las tiendas que tendrá nuestra tabla de Staging y que queremos cargar en nuestra dimensión:
INSERT INTO STG_TIENDAS_CHECKSUM VALUES
('0001', 'COMICS WORLD', 'ESTHER', 'MADRID', 'MADRID', 'ESPAÑA', 'PASEO DE LA CASTELLANA, 100',
'28046', '916549871', '916549872', 'comicsworld@comicsworld.es', 'www.comicsworld.es'),
('0002', 'COMICS CITY', 'ANTONIO', 'BARCELONA', 'BARCELONA', 'ESPAÑA', 'LES RAMBLES, 72',
'08002', '939876543', '939876544', 'comicscity@comicscity.es', 'www.comicscity.es'),
('0003', 'COMICON', 'FELIPE', 'ELCHE', 'ALICANTE', 'ESPAÑA', 'CALLE REINA VICTORIA, 77',
'03201', '965462879', '965462874', 'comicon@comicon.es', 'www.comicon.es')
Ejecutamos nuestro paquete y vemos que efectivamente se insertan tres filas:
Nuestras tablas quedan de la siguiente manera:
En primera imagen tenemos nuestra tabla de histórico en la que podemos ver tres registros de Tipo 3, con lo cual se traduce en tres inserciones en nuestra tabla de dimensión como se puede ver en la segunda imagen.
He realizado un update en la fecha de inicio para que se vea mas claro después a la hora de insertar nuevos registros y hacer cambios.
Ahora vamos a insertar una nueva tienda y modificar las que ya teníamos en nuestra tabla de origen de Staging:
TRUNCATE TABLE STG_TIENDAS_CHECKSUM
GO
INSERT INTO STG_TIENDAS_CHECKSUM VALUES
('0001', 'COMICS WORLD', 'TERESA', 'MADRID', 'MADRID', 'ESPAÑA', 'PASEO DE LA CASTELLANA, 100',
'28046', '916549871', '916549872', 'comicsworld@comicsworld.es', 'www.comicsworld.es'),
('0002', 'COMICS CITY 2', 'ANTONIO', 'BARCELONA', 'BARCELONA', 'ESPAÑA', 'LES RAMBLES, 72',
'08002', '939876543', '939876544', 'comicscity@comicscity.es', 'www.comicscity.es'),
('0003', 'COMICON 2', 'OSCAR', 'ELCHE', 'ALICANTE', 'ESPAÑA', 'CALLE REINA VICTORIA, 77',
'03201', '965462879', '965462874', 'comicon@comicon.es', 'www.comicon.es'),
('0004', 'MUNDO MANGA', 'TATIANA', 'ELCHE', 'ALICANTE', 'ESPAÑA', 'CALLE CORREDERA, 1',
'03201', '965440707', '965440705', 'mundomanga@mundomanga.es', 'www.mundomanga.es')
Como podéis observar hemos añadido la tienda “0004”, hemos cambiado de Jefe a la tienda “0001” ahora es Teresa en vez de Esther (cambio en un atributo de Tipo 2), hemos cambiado el nombre de la tienda “0002” (cambio de Tipo 1) y en la tienda “0003” hemos cambiado tanto el Jefe como el Nombre de la tienda (cambio Tipo 1 y cambio Tipo 2). Cuando tenemos un registro que tiene ambos cambios, en nuestro caso, lo que hacemos en tratarlo como un cambio de Tipo 2, cerramos el registro e insertamos la nueva versión con todos los cambios, tanto de Tipo 1 como de Tipo 2.
Ahora volvemos a ejecutar nuestro paquete y vemos que efectivamente se ha producido una inserción, dos cambios de Tipo 2 y un cambio de Tipo 1:
Comprobamos nuestras tablas y vemos lo siguiente:
Vemos que en nuestra tabla de histórico (primera imagen) tenemos la tienda “0001” y “0003” con cambios de Tipo 2, la tienda “0002” con cambio de Tipo 1 y por último la tienda “0004” que es una inserción Tipo 3.
En nuestra tabla de dimensión (segunda imagen) tenemos para los cambios de Tipo 2 los registros con ID_TIENDA 1 y 3 cerrados con fecha de fin y sus nuevas versiones insertadas correspondientes a los ID_TIENDA 4 y 5 respectivamente, para la tienda “0002” no hemos generado nuevo ID, simplemente se ha actualizado el nombre de la tienda a “COMICS CITY 2” y tenemos la nueva tienda “0004” insertada.
Nuestro paquete ha funcionado como queríamos
Conclusión
A lo largo de este post hemos comentado como cargar una dimensión con atributos de Tipo 2 usando una columna de Tipo CHECKSUM, la siguiente pregunta sería, ¿Es mejor comparar columna a columna o es mejor usar una columna de tipo CHECKSUM?, la respuesta como siempre sería, depende.
Esta columna de CHECKSUM es útil cuando tenemos dimensiones con una gran cantidad de columnas y comparar una por una puede convertirse una tarea un poco tediosa, pero como siempre cada caso es un mundo y lo recomendable siempre es probar con las diferentes posibilidades que os hemos mostrado a lo largo de esta serie de post y elegir la opción que más se adapte y mejor rendimiento de un vuestro caso particular.
Podeis ver los anterior post de esta serie en los siguientes enlaces:
Espero que os sirva de ayuda.
Cualquier consulta podéis contactar a través de :
@CarminaBH
1 comment
EXCELENTE!