Tratamiento de Dimensiones Lentamente Cambiantes (SCD) con PDI.

Antes de continuar con la construcción de los procesos ETL para la dimensión producto, vamos a ver la forma de gestionar las dimensiones lentamente cambiantes con Kettle (Pentaho Data Integration). En concreto, veremos en detalle el paso Dimension lookup/update. En este step se implementa de forma automatica el algoritmo para el tratamiento de las SCD del tipo I y II. Hagamos un poco de memoria:

• SCD Tipo 1: Sobreescribir: cuando hay un cambio en los valores de un atributo, sobrescribimos el valor antiguo con el nuevo sin registrar una historia. Esto significa perder toda la historia del dato, y cuando hagamos un análisis veremos la información histórica desde el punto de vista actual.
• SCD Tipo 2: Añadir fila: cuando hay un cambio, creamos un nuevo registro en la tabla. El nuevo registro tiene una nueva clave subrogada, de forma que una entidad de sistema operacional (por ejemplo, un cliente), puede tener varios registros en la tabla de la dimensión según se van produciendo los cambios. Estamos gestionando un versionado, que ademas puede incluir unas fechas para indicar los periodos de validez, numerador de registros o un indicador de registro activo o no.

Con un sencillo ejemplo entenderemos mejor el funcionamiento del paso o step. En nuestro caso, tenemos una tabla en Oracle, llamada dwd_cliente_pruebas, que contiene los datos extraidos del ERP, ya normalizados y elaborados para actualizar la tabla. Sera el origen de datos. Por otro lado, tenemos una tabla destino, la tabla de la dimensión, llamada DWD_CLIENTE_SK, cuya estructura física es la siguiente:

Ejemplo SCD - Tabla Dimension

La tabla incluye, al final, 3 campos para gestionar la versión y las fechas de validez del registro (campos VERSION, DATE_FROM y DATE_TO). En nuestro ejemplo, leeremos los datos de la tabla original, le aplicaremos algunas transformaciones a los datos recuperados (no relevantes en este caso, pues estamos modificando descripciones), y finalmente pasaremos la información al paso Dimension lookup/update (llamado Actualización Dimension Cliente), que se encargara de realizar la actualización de la dimensión cliente. El esquema de la transformación en PDI será el siguiente:

Transformacion con tratamiento de SCD

Vamos a ver en detalle cada uno de los pasos:

Lee_Clientes_ERP: con un paso del tipo Table Input (categoría Input) recuperamos todos los registros de la tabla origen, y pasamos los datos al flujo de la transformación. En la tabla dwd_clientes_pruebas ya tenemos los datos elaborados (podría ser algo así como la tabla temporal del area de Stage).

Lectura de datos de clientes del sistema Origen

Modifica Descripciones, Eliminar Campos, Renombrar Campos: irrelevantes en este ejemplo. Utilizamos los pasos Calculator y Select Values para modificar las descripciones de los clientes y así «ocultar» las denominaciones reales para este ejemplo. Estos pasos los vamos a utilizar para forzar cambios en los campos y asi ver el comportamiento del algoritmo de SCD.

Fecha Proceso: con un paso del tipo Add Constants (categoría Transform) introducimos en el flujo de datos una constante del tipo fecha, que se va a utilizar en el paso siguiente para realizar la busqueda de valores en la dimensión. Vamos a explicar esto un poco. Tal y como observamos en la imagen siguiente, tenemos una tabla origen (nuestro ERP), donde tenemos asociado a cada cliente un código (el campo CLIENTE_ID), que lo identifica en todas las transacciones que realizamos con el. Se van realizando cambios sobre el (su agente comercial asociado, el canal de venta en el que lo incluimos, el tipo de cliente o el domicilio, etc). Aunque vamos haciendo cambios, estos van siempre actualizando los datos maestros y siempre trabajamos con el mismo código.

Ejemplo SCD - Tabla Origen Vs Tabla Destino

Por otro lado, tenemos nuestro sistema DW que se alimenta de los datos anteriores. Para poder analizar mejor la información, nos queremos ir guardando la historia de los cambios en el cliente. Para ello nos inventamos las claves subrogadas, que es una numeración independiente de los clientes. Ademas, establecemos que cuando haya un cambio en alguno de los campos que consideremos importantes (SCD del tipo 2), vamos a crear un nuevo registro para el cliente. Esto generara una nueva clave subrogada (campo CLIENTE_SK en nuestro ejemplo), con el mismo código de cliente del sistema original (CLIENTE_ID) y sus correspondientes atributos. Además, tenemos el campo VERSION (que se ira incrementando conforme vayamos creando «versiones» de los datos del clientes), y los campos DATE_FROM y DATE_TO (que indican la validez temporal de los datos), que también se iran generando y actualizando conforme vayamos generando registros. Es aquí donde tiene sentido la fecha que vamos a pasar al flujo de datos. Esta fecha nos va a permitir recuperar el registro «valido», «activo» o «vigente» en ese momento, según el valor de la fecha, buscando el registro cuya date_from (fecha inicio) sea menor o igual que la fecha indicada y aquel cuya date_to (fecha_fin) sea mayor o igual. Solo ha de haber un registro valido o activo en una fecha determinada si todo esta definido correctamente.

Actualiza Dimensión Cliente: con un paso del tipo Dimension lookup/update (categoría Data Warehouse) realizaremos de forma automática la gestión de los cambios que acabamos de indicar. Veamos en detalle como se configura el step. En primer lugar, en la parte superior, indicaremos las siguientes propiedades:

• Update the dimension: este flag nos permite configurar el paso en modo lectura (solo para recuperar la clave subrogada y sus valores ) o en modo escritura (se realiza una actualización de los datos de la dimensión).
• Connection: conexión de base de datos que vamos a utilizar para el proceso.
• Target schema: esquema en la base de datos donde esta la tabla (a rellenar o no según el motor de base de datos que estemos utilizando).
• Target Table: tabla destino (sera la de la dimensión en el DW).
• Commit size: realización de un commit en base de datos tras el número de registros insertados o modificados indicado aquí.
• Enable the cache: habilitación de la cache de memoria para los procesos.
• Cache size in rows: tamaño de la cache en registros.

En la parte de enmedio de la pantalla, en la pestaña Keys, indicamos que clave vamos a utilizar para identificar al cliente (es la clave del sistema operacional). Siempre indicamos un tupla, por un lado el valor Dimension field (como se llama el campo en la tabla de la dimensión en el DW) y por otro lado el valor Field in Stream (como se llama la variable del flujo de datos de kettle que contiene el valor a analizar). Esta ultima esta llenada con los datos recuperados de la tabla origen.

Ejemplo SCD - Configuracion Dimension Lookup / Update (Paso I)

En la parte inferior, indicamos los nombres de los campos de la tabla de la dimensión que se van a utilizar para la gestión de las claves subrogadas y el versionado:

• Technical key field: es el campo de la tabla de la dimensión que contiene la clave subrogada.
• Creation of technical key: aquí indicamos la forma de creación de la clave subrogada (valor maximo de la tabla +1, una secuencia de valores que pasamos al flujo o un campo autoincrementable (según la base de datos que estemos utilizando)).
• Version field: aquí indicamos el campo de la tabla de la dimension donde se almacenará el numero de versión de registro (correlativo entre los diferentes registros de un cliente).
• Stream Datefield: campo del flujo de datos que contiene la fecha con la que obtener el registro valido (tal y como hemos explicado anteriormente).
• Date range start field: campo de la dimensión que contiene la fecha de inicio de validez del registro.
• Min year: año de referencia minimo para la creación de la fecha de inicio (por ejemplo, si aquí indicamos 1900, el primer registro se creara con fecha inicio 01/01/1900).
• Table daterange end: campo de la dimensión que contiene la fecha de fin de validez del registro.
• Max year: año de referencia maximo para la creación de la fecha de fin (por ejemplo, si aquí indicamos 2099, el primer registro se creara con fecha inicio 31/12/2099).

Toda la gestión de la actualización de estos campos (clave subrogada, version, date_from, date_to) se va a realizar de una forma automatica por parte del algoritmo que incorpora el paso Dimensión Lookup.

A continuación, en la pestaña Fields vamos a configurar sobre que campos queremos que se realize la gestión de las dimensiones lentamente cambiantes del tipo I o II. Para cada campo de la dimensión que querramos gestionar, indicaremos los siguientes valores:

• Dimensión field: nombre del campo en la tabla de la dimensión a analizar.
• Stream field to compare with: campo en el flujo de datos cuyo contenido analizaremos contra el valor existente en base de datos para determinar si hay cambio o no, y realizar la acción oportuna (que indicaremos en el siguiente campo).
• Type of dimension update: comportamiento del algoritmo en el caso de que haya un cambio en el valor del campo. Podemos establecer tres tipos de comportamientos (tal y como vemos en la imagen):

Ejemplo SCD - Configuracion Dimension Lookup / Update (Paso II)

Insert: se genera un nuevo registro en base de datos (y su correspondiente clave subrrogada). En el caso de ser la primera inserción, los registros tendrían el aspecto de la imagen siguiente (una clave subrogada en el campo cliente_sk, el valor 1 en el campo version, y las fecha inicio y fin inicializadas a los valores minimo y maximo, en este caso, 01/01/1900 y 31/12/9999. Recordar que los años los hemos definido nosotros en la configuración del step).

Ejemplo SCD - Creación de registros de la dimensión en la primera carga

En el caso de que ya existiera un registro para el cliente, se va a generar uno nuevo, con una nueva clave subrogada. El nuevo registro incrementa su numero de versión en 1, y las fechas son ajustadas (el registro existente limita su fecha final) y el registro nuevo tiene fecha inicio la indicada en el proceso y fecha final la máxima indicada (tal y como vemos en la imagen). Sería el tratamiento de las SCD del tipo II. En el ejemplo hemos puesto que el campo CLIENTE_DESC, cuando tenga un cambio, tenga un tratamiento del tipo INSERT.

Ejemplo SCD - Creación de un segundo registro (o superior ) para un cliente en SCD Tipo II

Update: no se genera un nuevo registro para el cliente cuando hay un cambio en el campo indicado. Simplemente actualizamos el valor del campo que ha sdio modificado en base de datos con el valor del flujo (el recuperado del sistema origen). Sería el tratamiento de las SCD del tipo I. Solo se actualiza el registro de base de datos activo en la fecha de referencia.

Punch through: es una variante del Update. El comportamiento es similar, pero se actualizan todos los registros que tuviera el cliente (no solo el activo). Esto puede ser util cuando queramos actualizar determinados valores (por ejemplo, el nombre del cliente, que es corregido por estar erróneo). En la imagen vemos un ejemplo de este tipo de actualización. Hemos cambiado el comportamiento del campo CLIENTE_DESC para que sea un punch through. Y al cambiar su valor se han actualizado todos los del cliente (no solo el activo como en el caso del Update).

Ejemplo SCD - Tratamiento SCD del tipo I (variante Update Through)

Esta es basicamente la forma de trabajar con este control de Pentaho Data Integration. Con un único paso nos hemos evitado todo el tratamiento e implementación del algoritmo de las SCD, que esta encapsulado dentro del componente.

Os dejo el link al fichero xml de la transformación para que vosotros mismos podais ver como quedaría configurado todo el proceso. Igualmente, os dejo el link a la entrada del blog donde vimos la forma en que Talend gestionaba el tratamiento de las dimensiones lentamente cambiantes, por si os interesa comparar una herramienta con la otra.