Carga de Slowly Changing Dimensions y tabla de Hechos con atributos de Tipo 2 (Parte 1 de 3)

Este post es el primero de una serie de 3 post que iremos publicando. A lo largo de estos post vamos a comentar las diferentes opciones que tenemos para cargar Slowly Changing Dimension (SCD) o dimensiones lentamente cambiantes si lo traducimos al castellano y como cargar nuestras tablas de Hechos a partir de estas dimensiones.

En este primer post hablaremos sobre como cargar una SCD Dimension usando los componentes Look Up y Merge Join y daremos algunas recomendaciones sobre cuando usar uno u otro, en el segundo post comentaremos como cargar la tabla de hechos para que cada registro de la tabla de hechos esté asociado al registro correcto de la dimensión y en el tercer post comentaremos como cargar una SCD Dimension usando una columna de CHECKSUM para detectar los cambios.

Introducción

Una SCD Dimension son dimensiones en las que sus atributos cambian a lo largo del tiempo, es estos post vamos a centrarnos en dimensiones con atributos de Tipo 1 y Tipo 2.

Para nuestros ejemplos vamos a utilizar una dimensión TIENDAS, que representa a tiendas de cómics, esta dimensión tendrá 2 atributos, uno de Tipo 1 que será el nombre de la tienda y otro de tipo 2 que será el jefe de la tienda.

¿Qué es Tipo 1 y Tipo 2?

• Tipo 1: Cuando tenemos un cambio en un atributo de Tipo 1 este cambio sobre escribe al valor anterior y no guardamos historia del valor que tenía este atributo en el pasado.

En este ejemplo vemos que al modificar el nombre de la tienda “0001” simplemente sobre escribimos el valor y perdemos la historia del valor anterior.

• Tipo 2: Cuando tenemos un cambio en un atributo de Tipo 2 lo que hacemos en cerrar el registro anterior usando una fecha de fin y se inserta un nuevo registro con el nuevo valor del atributo.

En este ejemplo vemos que al modificar el jefe de la tienda lo que hacemos es cerrar el registro antiguo poniendo una fecha de fin e insertando uno nuevo con un ID_TIENDA diferente, pero manteniendo el COD_TIENDA. En este ejemplo el atributo ID_TIENDA representa la SK, surrogate key o clave subrogada que es una clave artificial que nos creamos nosotros para identificar de forma única cada registro de la dimensión, lo normal para este atributo es que sea un auto numérico de tipo INT o BIGINT dependiendo del tamaño de nuestra dimensión. El atributo COD_TIENDA representa la BK, business key o clave de negocio, esta clave es la clave que usa el negocio para identificar sus tiendas, otros ejemplo podrían ser en el caso de clientes los DNIs o en el caso de recibos los códigos de barras, etc… Con estos atributos de Tipo 2 podemos tener una historia de todos los valores que ha ido teniendo dicho atributo a lo largo del tiempo.

Carga de la Dimensión usando componente “Slowly Changing Dimension”

Este componente ofrece un wizard para hacer la carga de una SCD Dimension. Como ejemplo didáctico para empezar y ver como hace la carga de la dimensión es interesante, pero este componente tiene varios problemas. El primero es un problema de rendimiento porque utiliza componentes “OLE DB Command” para hacer updates, estos componentes hacen los updates fila a fila en vez de por bloques lo que provoca que el rendimiento sea malo, y otro problema que tienen es que si haces cualquier cambio para adaptarlo a tu ejemplo concreto si vuelves a hacer el wizard todos los cambios manuales efectuados se pierden. Por lo tanto no recomiendo el uso de este componente para realizar la carga de una SCD Dimension.

Carga de la Dimensión usando componente Look Up

Vamos a crear un paquete para hacer la carga de una SCD Dimension usando el componente “Loop Up”.

Este es el script de creación de tablas que vamos a usar para este ejemplo:

IF OBJECT_ID('STG_TIENDAS') IS NOT NULL
  DROP TABLE STG_TIENDAS
CREATE TABLE STG_TIENDAS
(
  COD_TIENDA VARCHAR(4) PRIMARY KEY,
  NOMBRE VARCHAR(100),
  JEFE VARCHAR(100)
)
 
IF OBJECT_ID('STG_TIENDAS_HISTORICO') IS NOT NULL
  DROP TABLE STG_TIENDAS_HISTORICO
CREATE TABLE STG_TIENDAS_HISTORICO
(
  COD_TIENDA VARCHAR(4),
  NOMBRE VARCHAR(100),
  JEFE VARCHAR(100),
  TIPO INT,
  EXECUTION_ID UNIQUEIDENTIFIER,
  FEC_EJECUCION DATETIME,
  PRIMARY KEY (COD_TIENDA,FEC_EJECUCION)
)
 
IF OBJECT_ID('DIM_TIENDAS') IS NOT NULL
  DROP TABLE DIM_TIENDAS
CREATE TABLE DIM_TIENDAS
(
  ID_TIENDA INT IDENTITY(1,1) PRIMARY KEY,
  COD_TIENDA VARCHAR(4),
  NOMBRE VARCHAR(100),
  JEFE VARCHAR(100),
  FEC_INICIO INT,
  FEC_FIN INT
)

Tenemos 3 tablas, una tabla STG_TIENDA que representa el origen de nuestra dimensión, nuestra tabla de origen tiendas tendrá como columnas el código de la tienda, que representa a la clave de negocio, el nombre de la tienda, que será un atributo de Tipo 1 del que no queremos tener historia y el nombre del jefe de la tienda, que será un atributo de Tipo 2 porque en este caso si queremos saber qué persona ha sido el jefe de cada tienda a lo largo el tiempo para que al cargar nuestra tabla de hechos de ventas y queramos ver las ventas que ha tenido cada jefe no perdamos esa historia al hacer un cambio. También tenemos la tabla DIM_TIENDA que representa dimensión en nuestro DataWarehouse y una tabla histórica (STG_TIENDAS_HISTORICO) para guardar el log de todos los cambios, que nos servirá de ayuda para cargar nuestra dimensión.

Nuestro paquete de carga tendrá la siguiente estructura:

Primero tenemos un Data Flow en el que haremos la carga de nuestra tabla de histórico con los cambios a aplicar y luego updates para los atributos de Tipo 1 y para cerrar los registros con cambios en los atributos de Tipo 2 seguido de las inserciones en nuestra tabla de dimensión Tiendas.

La estructura de nuestro Data Flow es la siguiente:

Primero configuramos nuestro componente “OLE DB Source” con el nombre “Stg Tiendas” para que utilice la siguiente consulta:

SELECT COD_TIENDA,NOMBRE,JEFE
FROM STG_TIENDAS

Lo siguiente será configurar nuestro componente “Look Up” en el apartado de “General” usaremos “Full cache”, “OLE DB connection manager” y vamos a seleccionar “Ignore failure”.

En la parte de “Connection” seleccionamos “Use results of an SQL query” e introducimos la consulta sobre nuestra dimensión. Añadido un WHERE FEC_FIN IS NULL para leer la última versión de cada tienda.

Y en la parte de “Columns” hacemos el mapeo de columnas entre nuestra tabla de Staging y nuestra dimensión.

Después configuramos nuestro componente “Conditional Split” para separar nuestras filas según tengan un cambio en un atributo de Tipo 2, Tipo 1 o si es una inserción.

En nuestro ejemplo los insertados serán aquellos que vengan con el código tienda de nuestra tabla de dimensión a NULL, esto quiere decir que no existen en nuestra dimensión y que habrá que insertarlos. Los modificados de Tipo 2 serán aquellos que tengan el jefe diferente y los modificados de Tipo 1 serán aquellos que tengas el nombre de la tienda diferente. En este caso es importante el orden entre los modificados de Tipo 2 y Tipo 1, porque en los registros que tengan modificaciones en ambos atributos los consideraremos de Tipo 2, porque insertaremos una nueva fila con todos los cambios. Esto lo veremos de forma más clara más adelante con un ejemplo.

Lo siguiente en nuestro Data Flow son tres componentes “Derived Column” que añadirán la columna TIPO según el tipo de registro, en nuestro ejemplo será 1 para registros con cambios de Tipo 1, 2 para registros con cambios de Tipo 2 y 3 para insertados.

Este es un ejemplo de como quería el “Derived Column” de Tipo 1, habría que hacer lo mismo con los otros pero cambiando el número en “Expression”:

Luego tenemos un “Union All” para unir todos los flujos de datos en uno solo y de nuevo otro “Derived Column” con las columnas de auditado que serán la fecha de ejecución y el execution id, el execution id es un identificador que identifica cada ejecución del paquete de forma única, esto nos servirá saber qué cambios se han producido en qué ejecución. Estas columnas también se podrían añadir en los “Derived Column” anteriores que crean la columna TIPO, pero a modo didáctico he preferido ponerlo por separado para que quede más claro que hace cada componente.

Y por último tenemos nuestro componente “OLE DB Destination” que tendrá como tabla destino nuestra tabla de histórico STG_TIENDAS_HISTORICO.

Ahora pasamos a las consultas que contienen nuestros “SQL Task” en nuestro Control Flow que se encargan de aplicar los updates e inserts en la dimensión.

El “SQL Task” con el nombre “Updates Tipo 1” contiene la siguiente consulta:

UPDATE DIM
SET DIM.NOMBRE=STG.NOMBRE
FROM DIM_TIENDAS DIM
INNER JOIN STG_TIENDAS_HISTORICO STG
ON STG.COD_TIENDA=DIM.COD_TIENDA
WHERE TIPO=1 AND EXECUTION_ID=?

Con esto conseguimos hacer un update en bloque en vez de utilizar un componente “OLE DB Command” que realiza los updates fila a fila haciendo que el rendimiento sea mucho peor. 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”.

En el “SQL Task” con el nombre “Cerrar registro Tipo 2” ponemos la siguiente consulta:

UPDATE DIM
SET FEC_FIN=CONVERT(CHAR(8), GETDATE(), 112)
FROM DIM_TIENDAS DIM
INNER JOIN STG_TIENDAS_HISTORICO STG
ON STG.COD_TIENDA=DIM.COD_TIENDA
WHERE TIPO=2 AND FEC_FIN IS NULL AND EXECUTION_ID=?

También tenemos que parametrizar la variable de sistema “ExecutionInstanceGUID” como en el caso anterior. Esta consulta lo que hace es poner fecha de fin a los registros de Tipo 2 que han sufrido cambios.

Y por último tenemos el “SQL Task” con el nombre de “Inserciones” que tiene esta consulta:

INSERT INTO DIM_TIENDAS
SELECT COD_TIENDA,
NOMBRE,
JEFE,
CONVERT(CHAR(8), GETDATE(), 112),
NULL
FROM STG_TIENDAS_HISTORICO
WHERE TIPO IN (2,3) AND EXECUTION_ID=?

También tenemos que parametrizar la variable de sistema “ExecutionInstanceGUID” como en el caso anterior. Esta consulta hace las inserciones de los registros nuevos y de las nuevas versiones de los registros de Tipo 2 que han sufrido modificaciones.

Y con esto ya tendríamos nuestro paquete listo

Carga de la Dimensión usando componente Merge Join

Este paquete va a ser igual que el anterior con la única diferencia de que cambiamos el componente “Look Up” por el componente “Merge Join” y añadimos un componente “OLE DB Source” que leerá de la dimensión Tiendas para hacer el cruce.

Para poder hacer el cruce con un componente “Merge Join” nuestros orígenes deben de estar ordenados y para esto tenemos 2 opciones:

• Ordenar en el origen poniendo un ORDER BY en la consulta de origen en el componente “OLE DB Source”.
• Ordenar usando un componente SORT.

Lo más recomendable siempre es ordenar en el origen, ya que el componente “Sort” es un componente bloqueante, es decir, tiene que esperar a recibir todas las filas para poder empezar a ordenar, esto hace que el rendimiento disminuya, también va guardando todas las filas en memoria, por lo que si tenemos tablas con muchos millones de filas hay que llevar cuidado para no quedarnos sin memoria RAM.

En nuestro ejemplo vamos a optar por la opción de ordenar en el origen, por lo tanto las consultas que usaremos en nuestros componentes “OLE DB Source” serán, para el “Stg Tiendas”:

SELECT COD_TIENDA,
NOMBRE,
JEFE
FROM STG_TIENDAS
ORDER BY COD_TIENDA

Y para el “Dim Tiendas”:

SELECT COD_TIENDA,
NOMBRE,
JEFE
FROM DIM_TIENDAS
WHERE FEC_FIN IS NULL
ORDER BY COD_TIENDA

Ahora viene una parte importante, no solo basta con poner ORDER BY en la consulta, también tenemos que configurar las opciones avanzadas del componente para indicarle que los datos vienen ordenados de origen. Para ello pulsamos botón derecho sobre el componente “OLE DB Source” y vamos a “Show Advanced Editor…”

En la pestaña de “Input and Output Properties” seleccionamos “OLE DB Source Output” y en la propiedad “IsSorted” la ponemos a True.

Ahora tenemos que seleccionar las columnas por las que viene ordenado el origen y poner el orden en cada una de las columnas. En nuestro caso, nuestro origen viene solo ordenado por la columna COD_TIENDA, para ello desplegamos nuestro “OLE DB Source Output” –> “Output Columns” –> COD_TIENDA y en la propiedad “SortKeyPosition” ponemos un 1, si tuviéramos más columnas, a la siguiente columna le pondríamos un 2 y así sucesivamente.

Tenemos que realizar esta operación para ambos componentes “OLE DB Source”.

Al unir los componentes origen con el “Merge Join” tenemos que seleccionar “Stg Tiendas” como la parte Left del join.

Al abrir el componente “Merge Join” mapeamos por COD_TIENDA como se muestra a continuación:

Y ya tendríamos nuestro paquete listo

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 VALUES
('0001', 'COMICS WORLD', 'ESTHER'),
('0002', 'COMICS CITY', 'ANTONIO'),
('0003', 'COMICON', 'FELIPE')

Ejecutamos nuestro paquete y vemos que efectivamente se insertan 3 filas:

Hacemos una select sobre nuestra dimensión y sobre nuestra tabla de histórico y obtenemos lo siguiente:

Vemos que en nuestro histórico tenemos 3 registros de Tipo 3 (Inserciones) y que efectivamente se han insertado 3 registros en nuestra dimensión de Tiendas.

En este ejemplo he hecho un par de updates después de cargar la dimensión sobre las fechas de las tablas de dimensión y del histórico para que se vea mas claro a la hora de realizar nuestra segunda ejecución que tendrá cambios en nuestro origen.

Estos son los updates utilizados:

UPDATE STG_TIENDAS_HISTORICO SET FEC_EJECUCION='20140501'
UPDATE DIM_TIENDAS SET FEC_INICIO=20140501

Ahora truncamos y volvemos a carga nuestra tabla de Staging para simular algunos cambios en nuestro origen. Vamos a insertar una nueva tienda, vamos a cambiar el jefe para nuestra tienda “0001” (cambio Tipo 2), vamos a cambiar el nombre de la tienda en la tienda “0002” (cambio Tipo 1) y vamos a cambiar el nombre y el jefe de nuestra tienda “0003” (cambio Tipo 2 y Tipo 1). Como hemos comentado anteriormente, cuando tenemos a la vez cambio Tipo 1 y Tipo 2 vamos a considerarlo como Tipo 2 y la nueva fila insertada con la nueva versión tendrá todos los cambios reflejados.

TRUNCATE TABLE STG_TIENDAS
GO

INSERT INTO STG_TIENDAS VALUES
('0001', 'COMICS WORLD', 'TERESA'),
('0002', 'COMICS CITY 2', 'ANTONIO'),
('0003', 'COMICON 2', 'OSCAR'),
('0004', 'MUNDO MANGA', 'TATIANA')

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:

Hacemos una select sobre nuestra dimensión y sobre nuestra tabla de histórico y obtenemos lo siguiente:

Vemos que en nuestro histórico tenemos dos cambios de Tipo 2, un cambio de Tipo 1 y una inserción de Tipo 3. Y en nuestra tabla de dimensión vemos que se ha insertado una nueva tienda con el ID 6, la tienda “0002” que tenía un cambio de Tipo 1 se ha actualizado su nombre y para las tiendas “0001” y “0003” se han generado nuevos registros con nuevos IDs 4 y 5 respectivamente y se han cerrado los registros antiguos (1 y 3) poniendo una fecha de fin.

Conclusión

En este post os hemos mostrado como cargar una dimensión usando como alternativas un componente “Look Up” o un componente “Merge Join”, cuál de los 2 componentes usar depende de diferentes factores.

Lo recomendable es usar siempre que se pueda el componente Look Up salvo en los casos en los que no sea posible, las ventajas que tiene el componente Look Up con respecto al Merge Join es que cachea los datos en memoria y que no hace falta que el origen venga ordenado, por ello el rendimiento es mejor.

¿En qué casos no es recomendable o no se puede usar Look Up?

• Cuando queremos todos los registros que hacen match y no solo el primero. El componente Look Up hace match con el primer resultado que encuentra, por lo tanto si queremos todos los casos en los que hace match y no solo el primero que encuentre necesitaremos usar el componente Merge Join.
• Cuando tenemos que hacer un match por un rango de fechas. Existe la opción de utilizar el Look Up como “Partial cache” y en la parte de “Advanced” marcar “Modify the SQL statement” y poner la consulta parametrizada por fecha (esto se explicará con un ejemplo en nuestro siguiente post en el que cargaremos la tabla de Hechos usando nuestra dimensión de Tiendas). El problema de usar caché parcial es que toda la ventaja que tiene el Look Up de pre cachear los datos en memoria la perdemos y consultará a la base de datos para cada petición, por ello el rendimiento no será bueno. En este caso es recomendable usar el componente Merge Join ayudado de un “Conditional Split” para seleccionar el registro que entra dentro del rango de fechas (un ejemplo de cómo hacer esto lo veremos en el siguiente post).
• Cuando tenemos que hacer un Join de sólo una vez para por ejemplo una carga de históricos con gran cantidad de filas. Es estos casos en mejor el uso de Merge Join porque utiliza una aproximación de transferencia continua de datos o streaming en vez de tomarse su tiempo pre cacheando los datos en memoria, además está aproximación en streaming está mejorada en SSIS 2012 ya que ahora el Merge Join evita que uno los orígenes obtenga mas buffers cuando una de la fuentes es mucho más rápida que la otra. Aquí hay que tener en cuenta que un componente origen no termina hasta que ha leído todos los datos, entonces si los datos de entrada tienen una pequeña cantidad de filas y se está uniendo con un data set de mucho mayor tamaño a lo mejor el uso del Merge Join no sería lo ideal, un Look Up con caché parcial tiene a tener mejor rendimiento en este tipo de escenario.

Estas conclusiones son solo recomendaciones, siempre hay excepciones y cada caso y escenario es diferente, por eso lo recomendable probar, probar y probar hasta encontrar la solución que mejor se adapte a nuestro caso.

Puedes continuar con la segunda parte de este post en este enlace.

Puedes continuar con la tercera parte de este post en este enlace.

Adjunto en el post los materiales utilizados, si tenéis cualquier duda o consulta podéis contactar en:

cbernabeu@solidq.com

@CarminaBH

Espero que os sirva de ayuda