El Rincon del BI

Descubriendo el Business Intelligence…

16.3.1. ETL Dimensión Tiempo con PDI.

Posted by Roberto Espinosa en 12 mayo 2010


La dimensión tiempo es una dimensión estática, pues se crea una vez (por ejemplo, para un periodo de fechas de 10 o 20 años), y no se vuelve a tocar, a no ser que queramos añadir algún atributo adicional. Podemos tener dos enfoques para la creación de la dimensión:

  • Uso de procedimientos almacenados: según el motor de base de datos que vayamos a utilizar, crearemos un procedimiento almacenado (o similar), que incluirá el código necesario para el llenado de la dimensión tiempo. En Dataprix.com tenemos un ejemplo de este enfoque para trabajar con Oracle, Sql Server y MySql. Roland Bouman también incluye un ejemplo de procedimiento almacenado en Mysql para calcular el día del año en que cae la Pascua. Esta forma de construir la dimensión puede ser útil mientras no tengamos cálculo de atributos complejos que requieran el uso de otro tipo de herramientas.
  • Uso de un proceso ETL: por otro lado, podemos construir un proceso ETL totalmente desvinculado del motor de base de datos elegido. Además, este proceso lo podremos reutilizar para todos los sistemas DW que diseñemos, solo ajustandolo a la posible elección de atributos que se decida. He encontrado dos interesantes ejemplos de construcción de la dimensión tiempo en el blog de Roland Bouman de nuevo y también en el de Fabian Schladitz, ambos utilizando Kettle. Nos van a ser de gran utilidad para diseñar nuestra propia transformación.

Transformacion para crear la dimensión tiempo en PDI - Roland Bouman

Transformación para el llenado de la dimensión tiempo.

La estructura física de la dimensión tiempo es la siguiente. Tendremos dos tablas, una principal y una derivada para los meses, debido al nivel de granuralidad de la información de presupuestos, como ya vimos.

Esquema Fïsico Dimensión Tiempo

Los calculos a realizar para llenar cada uno de los campos son los siguientes:

  • Fecha Clave: fecha en la notacion yyyymmdd para procesarla como enteros. Será la clave de la dimensión. Convertiremos la fecha a este formato (seria una especie de clave subrogada).
  • Fecha Id: fecha en la notación habitual de tipo date.
  • Fecha Desc: fecha formateada en texto ( por ejemplo: 15 de abril de 2009).
  • Dia de la semana Id: dia 1,2,3…7.
  • Dia de la semana Desc:  dia de la semana en texto (Lunes, Martes, Miercoles,..).
  • Dia del Mes Id: numero de dia de la fecha en el mes (dia 14, dia 28, dia 31).
  • Dia del Año Id: numero de día de la fecha en el año (dia 234, dia 365).
  • Semana Id: notacion año-semana para comparativas, cabeceras (YYYYSS, 200845). Es la clave que identifica cada semana.
  • Semana Desc: semana del año en formato descriptivo ( Semana 45 de 2008).
  • Numero de Semana Id: numero de semana donde se incluye la fecha (1, 12, 23). Desvinculada del año para otro tipo de análisis.
  • Numero de Semana Desc: descripción en texto del numero de semana (Semana 1, Semana 23).
  • Festivo: indicador de si la fecha es un festivo o no (S,N).
  • Fin de semana: indicador si la fecha es fin de semana o dia entre semana (S,N).
  • Mes Id: notación año-mes para comparativas,cabeceras (YYYYMM, 200811). Es la clave que va a identificar cada mes.
  • Mes Desc: mes del año en formato descriptivo ( Enero de 2008, Abril de 2010, etc).
  • Número de Mes Id: número del mes en el año (Enero = 1, Febrero = 2, etc). Desvinculado del año para otro tipo de análisis.
  • Número de Mes Desc: descripción en texto del mes (Enero, Febrero, Marzo,…).
  • Trimestre Id: notacion año-trimestre para comparativas, cabeceras (YYYYT, 20081). Es la clave que va a identificar cada trimestre.
  • Trimestre Descripcion: trimestre en formato descriptivo ( 1 Trim 2008, 2 Trim 2008, etc.).
  • Numero de Trimestre Id: trimestre donde se incluye la fecha (1,2,3 o 4). Desvinculado del año para otro tipo de análisis.
  • Numero de Trimestre Desc: descripcion en texto del trimestre (1 Trimestre,2 Trimestre, etc).
  • Año Id: año de la fecha, con 4 digitos. Es la clave que va a identificar cada año.
  • Año Ant Id: año anterior al actual, con 4 digitos.

Teniendo en cuenta todo esto, vamos a preparar el proceso ETL utilizando PDI. El proceso va a generar todos los datos vistos para cada fecha, desde el 01 de Enero de 2005 hasta el 31 de Diciembre de 2020. El diseño de nuestra transformación será el siguiente:

Dim Tiempo - Transformacion PDI

Vamos a ver un poco en detalle cada uno de los pasos.

  • Generar Fecha: con un paso del tipo Generate Row (categoría Input), generamos 10 mil veces un valor del tipo fecha, con el valor 20050101, que corresponde a la fecha inicial que queremos procesar. Esta fecha es el punto de partida de la generación de toda la secuencia de fechas hasta llegar al 31 de diciembre de 2020.

Dim Tiempo - Genera Fecha

  • Añadir 1 dia: con un paso del tipo Add Secuence (categoría Transform) generamos un valor númerico, que empieza por 1, y que se va incrementando en cada ejecución. Este valor será el que sumaremos más adelante a la fecha de partida del paso anterior para generar cada una de las fechas deseadas.

Dim Tiempo - Añadir 1 Dia

  • Calcular Fechas: con un paso del tipo Calculator (categoría Transform) empezamos a realizar varias operaciones sobre la fecha. La mas importante, como hemos indicado, es sumar la secuencia del paso anterior a la fecha inicial, para generar las fechas. Ademas, vamos a realizar otras operaciones, como la conversión de la fecha a número para generar la clave subrogada (pasos 2 y 3, utilizando una variable auxiliar). Ademas, con las diferentes operaciones que nos proporciona el paso, obtenemos el año de una fecha, el año anterior, el dia del mes y del año, la semana, el  número de mes de la fecha, etc.

Dim Tiempo - Calcular Fechas

  • Filter Rows: con un paso del tipo Filter Rows (categoría Flow) filtramos los registros generados por la fecha, para evitar que se genere ninguna fecha superior al 31 de diciembre de 2020. Las fechas que no cumplen la condición son desechadas a un paso Dummy (que no hace nada).
  • Calculo de Textos: con un paso del tipo Formula (categoría Scripting), calculamos algunos campos más, como el identificador de la semana y su descripción, el mes, el dia de la semana, etc.  El paso Formula lo utilizaremos cuando los cálculos son mas complejos, y no nos vale el step Calculator. Como bien indica Maria Carina Roldan en su libro sobre PDI, ese paso utiliza la librería LibFormula, cuya sintaxis esta basada en el estandar OpenFormula standard, del que podeís ampliar información en el siguiente link. Como podeís ver en la imagen, el  paso tiene un montón de formulas disponibles, y cuando seleccionamos alguna tenemos una pequeña ayuda con la sintaxis de cada una.

Dim Tiempo - Calcular Textos

  • Calculo Resto Atributos: igualmente,  para aquellos cálculos mas complejos, siempre nos queda la opción de “picar” nuestro propio código. Para ello, con el paso Modified Java Script Value (categoría Scripting) podemos definir nuestro propio código Javascript. En nuestro caso, hemos utilizado la localización en español para trabajar con las fechas y asi poder extraer las descripciones de los meses del año y de los días de la semana, así como para obtener otros valores como los trimestres. Podeís ver la documentación  Online de Java para el formateo de fechas y obtención de los diferentes atributos de estas. Como información adicional, indicar que PDI utilizar como motor JavaScript el producto Rhino, de Mozilla. Es una implementación Open Source del nucleo del lenguaje JavaScript. Podeís ampliar información en la web de Mozilla.

Dim Tiempo - Calcular Resto Atributos

  • Ordena Valores: con el paso Select / Rename values (categoría Transform), seleccionamos los campos que queremos pasar al siguiente paso, y el orden. Estamos ordenando los registros para que esten igual que los campos de la base de datos. Con este paso también podriamos seleccionar que campos queremos eliminar ( y no pasar en el flujo al siguiente paso).

Dim Tiempo - Ordenar Campos

  • Carga DWD_TIEMPO: como paso final en la transformación, utilizamos el step Table Output (categoría Output) para insertar en la correspondiente tabla de la base de datos los registros generados. En concreto, insertamos en la tabla DWD_TIEMPO. Previamente, hemos definido en PDI la conexión a base de datos para poder seleccionar la tabla destino (también podemos realizar  navegación entre las diferentes tablas del catalogo).

Dim Tiempo - Carga en Tabla BD

Os dejo el link al fichero xml de la transformación para que vosotros mismos la podaís analizar o utilizar en el caso que os sea de utilidad.

Como paso final, llenaremos la tabla DWD_TIEMPO_MES, que no será mas que la ejecución de la siguiente sentencia Sql (recordar que esta dimensión no es mas que un subconjunto de datos de la tabla principal de la dimensión tiempo). La sentencia sería la siguiente:

insert into dwd_tiempo_mes
    select distinct mes_id, mes_desc, mesn_id, mesn_desc,
                    trim_id, trim_desc,
                    trimn_id, trimn_desc,
                    anyo_id, anyo_ant_id from dwd_tiempo order by mes_id;

Otras consideraciónes a tener en cuenta.

Os recomiendo la lectura del Blog de BI Facil donde se habla de los diferentes enfoques para realizar la numeración de semanas. Igualmente, en el blog Enfoque Práctico de Jose Cano también se hacen algunas reflexiones interesantes sobre la dimensión tiempo.

12 comentarios to “16.3.1. ETL Dimensión Tiempo con PDI.”

  1. adrian said

    me gusto mucho este articulo https://churriwifi.wordpress.com/2010/05/12/16-3-1-dimension-tiempo/ veo q utilizas varias herramientas como el generator rows entre otras pero hago click en el enlace y no veo la forma de como descargarlos para hacerlo gracias

  2. victor said

    Hola
    Quiero pedirte ayuda dado que ya intente todo y no pude lograr pasar un array como parametro.
    Te cuento:
    requiero usar en kettle “Web services lookup”, para conectarme a un servicio web que recibe como parametro un array y un string
    el array en php es:

    $user_auth = array(
    “user_name” => ‘admin’,
    “password” => MD5(‘contraseña’),
    “version” => ‘1’
    );
    y el string el nombre de la aplicaciòn: “callcenter”

    Lei en un post que debe usarse un string con los datos separados por comas, asi que intento conectarme de las siguientes maneras:
    Paso de Array como parametro:
    “admin”, “b3fxxxxxxxxxxxx”, “01”
    {“admin”, “b3fxxxxxxxxxxxx”, “01”}

    Ninguna de las 2 funciona, siempre deelve el mismo error de usuario o contraseña.

    Esto me funciona muy bien desde php pero no el kettle.

    saludos!

  3. Victor said

    Roberto,
    gracias por el dato excelente libro, ayer en la noche empece a probar enviando el array de la forma como lo describe el ejemplo, los resultados fueron los mismos, continuare intentando esta vez con el control HTTP y despues lo hare en Talend.
    apenas tenga resultados lo comentare aqui.

    saludos!

    • erluak said

      Buenas tardes,

      Soy nuevo con kettle y tengo una duda.

      Tengo 3 pasos.
      1.- En el primero con “información del sistema” obtengo la fecha del primer día del mes anterior.
      2.- En el segundo, con js, creo las variables dia,mes y anyo, donde relleno su valor con sus correspondientes valores. (supongamos dia=01, mes=01, anyo=2011).
      3.- En el tercer paso, creo un “Get data from XML” e intento utilizar el valor de anyo y mes para localizar una serie de ficheros concretos.
      Para ello utilizo el apartado “Selected files” y en la columna “Wildcard (RegExp)” pongo lo siguiente:
      120_sent_${anyo}${mes}04.xml
      Pero me arroja un error:
      Caused by: java.util.regex.PatternSyntaxException: Illegal repetition near index 9
      120_sent_${anyo}0404.xml

      Veo que no coge el valor de la variable, y supongo que será porque no la paso bien.

      ¿me podéis ayudar a utilizar el valor de la variable obtenida en el paso anterior?

      Muchas gracias.

      Un saludo,
      Ismael.

  4. jorge said

    hola amigo estoy haciendo un dim_linea de producto y lo que pasa es debe eliminar los datos
    cargados anteriores y en caso existen , poder cargarlos de nuevo .

    me puede explicar . como desarrollarlo

  5. jorge said

    primero esta haciendo con Execute row SQL script para eliminar los datos de la tabla dimension
    despues input y saco los datos de base de transaccional y luego lo ingreso a la dimension q creado
    .pero me sale un error en Execute row SQL script , por favor ayudarme

  6. joel said

    Tengo problemas para utilizar el webservices lookup. Necesito saber como utilizarlo con servicios web en .NET

  7. David said

    Hola Roberto, el archivo está caído. ¿Podrías dar un nuevo enlace para ver la transformación más en detalle? Muchas gracias.

  8. Por favor, puede enviar archivos a mi correo electrónico? mkt.msantana@gmail.com
    Gracias!

  9. alejandro said

    Hola,
    Mi dimensión tiempo cuando la trabajo con el saiku analytics viene en formato de fecha y hora.
    ¿Se puede configurar para que el formato sea solo de fecha?. He buscado pero no encuentro la forma.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

 
A %d blogueros les gusta esto: