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

CREATE TABLE HECHOS_VENTAS
(
  ID_VENTA INT IDENTITY(1,1) PRIMARY KEY,
  FEC_VENTA INT,
  ID_TIENDA INT,
  COD_TIENDA VARCHAR(4),
  IMP_VENTA NUMERIC(8,2)
)
 
IF OBJECT_ID('STG_VENTAS') IS NOT NULL
  DROP TABLE STG_VENTAS
CREATE TABLE STG_VENTAS
(
  FEC_VENTA INT,
  COD_TIENDA VARCHAR(4),
  IMP_VENTA NUMERIC(8,2),
  PRIMARY KEY (FEC_VENTA, COD_TIENDA)
)

La tabla HECHOS_VENTAS representa nuestra tabla de hechos final y la tabla STG_VENTAS es nuestra tabla de origen de ventas.

Esta tabla de origen tendrá las ventas de cada día y tienda.

En la tabla de hechos el ID_VENTA será nuestra clave subrogada para identificar cada venta de forma única, el ID_TIENDA representa la clave subrogada de nuestra dimensión tiendas y el COD_TIENDA representa la clave de negocio. La columna de COD_TIENDA no sería necesaria en nuestra tabla de hechos ya que con el ID_TIENDA podemos obtener este valor cruzando con la dimensión, pero lo he añadido en este ejemplo de modo didáctico para que se vea de forma más clara y de un vistazo.

Carga de la Dimensión usando componente Look Up con Partial Cache

El paquete de carga tendrá la siguiente estructura:

Tenemos nuestro origen que será nuestra tabla de Staging de ventas, luego el componente “Look Up” con la dimensión de Tiendas para hacer asignar el ID_TIENDA correcto según el COD_TIENDA que nos viene de nuestra tabla origen y por último volcado de datos a nuestra tabla final de hechos.

Nuestro componente “OLE DB Source” tendrá una consulta sencilla contra nuestra tabla de origen, ya que vamos a utilizar el componente “Look Up” para hacer el cruce no hace falta que el origen venga ordenado.

Ahora pasamos a configurar nuestro componente “Look Up”. En la parte de “General” seleccionamos “Partial cache”.

Luego pasamos al apartado de “Connection”, seleccionamos “Use results of an SQL query” y escribimos una select sencilla para leer nuestra tabla de dimensión.

En el apartado de “Columns” mapeamos las columnas correspondientes como se ve en la siguiente imagen.

Y ahora pasamos a la parte interesante. En “Advanced” es donde vamos a escribir nuestra consulta parametrizada por fecha para que haga match con le registro correcto según su fecha de vigencia. Para ello marcamos “Modify the SQL statement” y escribimos la siguiente consulta:

SELECT ID_TIENDA, COD_TIENDA, FEC_INICIO, FEC_FIN
FROM DIM_TIENDAS
WHERE COD_TIENDA = ?
AND FEC_INICIO <= ?
AND (
FEC_FIN IS NULL OR FEC_FIN > ?
)

Y ahora pulsamos sobre “Parameters…” para mapear los campos de fecha:

El Parameter0 corresponde al COD_TIENDA y los dos siguientes se corresponden con la fecha de venta. Con esta consulta lo que logramos es obtener aquellas tiendas que tiene una fecha de venta mayor o igual que la fecha de inicio y menor que la fecha de fin o que la fecha de venta sea mayor o igual que la fecha de inicio y que la fecha de fin sea NULL.

Ahora configuramos nuestro “OLE DB Destination” para que apunte a nuestra tabla de hechos y ya tendríamos nuestro paquete terminado

Carga de la Dimensión usando componente Merge Join

Ahora vamos a pasar a crear nuestro paquete de carga usando un componente “Merge Join” ayudado de un “Conditional Split” para seleccionar el registro que se encuentra en el rango de fecha que deseamos.

Esta será la estructura de nuestro “Data Flow”:

Tenemos dos orígenes, uno será nuestra tabla de Staging de ventas y el otro nuestra dimensión Tiendas, después tenemos el componente “Merge Join” para relacionar cada venta con su tienda, el componente “Merge Join” hace pasar por el flujo todas las filas que hacen match, a diferencia del componente “Look Up”, que se queda solo con el primer resultado que hace match, por ello nos ayudamos del componente “Conditional Split” para quedarnos solo con el registro que se encuentra dentro del rango de fechas de todos los registros que hacen match.

El componente “Merge Join” requiere que los orígenes estén ordenados, la ordenación la podemos hacer usando dos métodos:

• 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 origen “Stg Ventas”:

SELECT COD_TIENDA, IMP_VENTA, FEC_VENTA
FROM STG_VENTAS
ORDER BY COD_TIENDA

Para el origen “Dim Tiendas”:

SELECT ID_TIENDA, COD_TIENDA, FEC_INICIO, FEC_FIN
FROM DIM_TIENDAS
ORDER BY COD_TIENDA

Ahora viene una parte importante, como se comenta en el post anterior, no solo basta con poner ORDER BY en la consulta, también tenemos que configurar las opciones avanzadas del componente origen 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 Ventas” como la parte Left del join y configurar el componente como aparece en la siguiente imagen.

Como se puede observar el cruce se hace por COD_TIENDA, y seleccionamos todas las demás columnas para incorporarlas al flujo.

Ahora vamos a configurar el componente “Conditional Split” para ponerle las condiciones necesarias para que seleccione el registro de la tienda que corresponde a cada venta, teniendo en cuenta la fecha de la venta y la fecha en la que la tienda está vigente.

Con esta condición obtenemos solo aquellas tiendas que tiene una fecha de venta mayor o igual que la fecha de inicio y menor que la fecha de fin o que la fecha de venta sea mayor o igual que la fecha de inicio y que la fecha de fin sea NULL, la condición es igual que la utilizada en nuestro componente “Look Up” pero adaptada al lenguaje del “Conditional Split”.

Ahora configuramos nuestro destino para que apunte a nuestra tabla de HECHOS_VENTAS y ya tendremos nuestro paquete terminado  🙂

Carga de datos de ejemplo

Ahora vamos a cargar datos de ejemplo para probar el correcto funcionamiento de nuestros paquetes. Para este ejemplo vamos a utilizar el paquete con el componente “Merge Join”, pero los resultados son los mismos con los dos.

Lo primero será cargar nuestra tabla de origen de Staging de ventas con algunas ventas de ejemplo:

INSERT INTO STG_VENTAS VALUES
(20140701, '0001', 2500),
(20140701, '0002', 3000),
(20140701, '0003', 1000),
 
(20140901, '0001', 2400),
(20140901, '0002', 3100),
(20140901, '0003', 1200)

Si has seguido los pasos del post anterior deberías tener una dimensión de Tiendas con los siguientes datos:

Ahora ejecutamos nuestro paquete y obtenemos el siguiente resultado:

Como se puede observar, si miramos el ejemplo de la tienda “0001”, se ve que cuando la venta se produce el Julio, el ID_TIENDA que se le asigna es el 1 porque es el registro que estaba vigente para esa fecha, pero cuando la venta se produce en Septiembre el ID_TIENDA que se le asigna es el 4. Podemos ver que nuestro paquete funciona como queríamos y que asigna el ID_TIENDA correcto según la fecha de venta de nuestro hecho.

De esta manera si por ejemplo quisiéramos ver las ventas que ha tenido ESTHER veríamos que son 2500 pertenecientes a Julio y las 2400 restantes de Septiembre pertenecerían a TERESA. ¿Qué pasaría si hubiéramos asignado mal el ID_TIENDA y siempre asignáramos la última versión?, es decir, si a todas la ventas de la tienda “0001” le asignáramos el ID_TIENDA 4, entonces si analizamos las ventas por jefe, nos aparecería que todas las ventas de la tienda “0001” son de TERESA, produciendo un resultado incorrecto, ya que sabemos que para el mes de Julio la persona encargada de la tienda era ESTHER.

Conclusión

En este post os hemos mostrado como cargar una tabla de hechos usando como alternativas un componente “Look Up” con Partial Caché o un componente “Merge Join” con “Conditional Split”, ¿Cuál de las dos versiones usamos y cuándo?

Mi recomendación es usar la versión del “Merge Join” cuando tenemos un número de filas considerable, ya que el “Look Up” en este modo, la caché empieza vacía y cuando llega una nueva fila, el componente comprueba si tiene este valor en la caché, si no lo encuentra, entonces hace una consulta a la base de datos para obtenerlo y a partir de ahí lo guarda en la caché para la próxima vez, por este motivo, con este modo se harán más consultas a la base de datos que usando un “Merge Join”, y el rendimiento será peor cuantas más filas tengamos.

Es recomendable usar el “Look Up” con caché parcial, cuando estamos procesando una cantidad pequeña de filas o cuando tenemos una tabla de referencia con muchas filas.

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 el siguiente post de la serie en este enlace y con el post anterior en este enlace.

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

@CarminaBH

Espero que os sirva de ayuda