5 Tips & Tricks para el script ETL

Cada proyecto Business Intelligence desarrollado con QlikView o Qlik Sense es totalmente diferente a cualquier otro.

Resulta imposible encontrar dos idénticos, ya que no existe una sola forma de desarrollo para poder solucionar las preguntas sobre el negocio que se tratan de responder.

De hecho, en la primera fase de desarrollo Qlik necesitará cargar los datos dentro de su modelo asociativo para después desarrollar el análisis: gráficos, tablas, indicadores, etc.

¿Qué es el script ETL?

El proceso de carga de datos se define dentro del script ET (Extract, Transform and Load).

Para garantizar el éxito del proyecto, es fundamental realizar un análisis de cada uno de los orígenes de datos para poder construir una buena base. Se necesita desarrollar un script ETL que contenga toda las entidades necesarias para nuestros análisis y poder mejorar en la toma de decisiones de la compañía.

¿Alguna recomendación para nuestro script ETL?

En este artículos se encuentran cinco trucos y consejos que pueden aplicarse al script ETL y que garantizarán una mejor optimización, organización y simplicidad dentro de nuestra aplicación.

1) Nombre de campos y tablas

Cuando se realiza la carga de cualquier origen de datos (Ej. BBDD, Excel, etc.) los nombres definidos para los campos NO tendrán ninguna relación con la información que contienen.

Por este motivo, es fundamental definir un criterio común para los nombres de campos y tabla que contendrá el modelo de datos de la aplicación Business Intelligence.

El criterio definido en el ejemplo de este artículo es el siguiente: [Tipo + [ESPACIO] + Agrupación]. Un ejemplo:

1
2
3
4
5
6
7
8
9

     Productos:
         LOAD	 
            idprod	   as	[ID Producto],  
            COLUMNA2       as	[Nombre Producto], 
            descrp	   as	[Descripción Producto], 
            fabricante     as	[Nombre Fabricante], 
            Orig	   as	[Origen Producto]
         FROM  Datos.xlsx
         (ooxml, embedded labels, table is Productos);

Como se puede observar en la siguiente imagen, definir un criterio de nombre para campos y tablas ayudará a entender y a mejorar la organización del modelo de datos:

2) Left Join VS Mapping Load

En el momento de unir dos tablas, puede surgir la duda de desconocer que recurso utilizar:

• Left Join.
• Mapping Load / Apply Map.

Se utilizará Left Join si se ha de unir más de un campo en una tabla, sin dar importancia que no se especifique un valor por defecto para aquellos registros que no se encuentren.

Por otra parte, se utilizará Mapping Load / Apply Map cuando se quiera unir un campo e incluso se quiera asignar un valor predeterminado para aquellos registros no encontrados.

Utilizar Mapping Load es más óptimo que no Left Join, debido a que éste último necesita primero cargar la tabla, para luego realizar la comparación. En cambio, con Mapping Load se realiza una tabla temporal (disponible en tiempo de recarga del script) dónde se utiliza inmediatamente el valor mapeado.

En el ejemplo de este artículo, la tabla ‘Clientes’ es ideal para realizar un Mapping Load con la tabla ‘Importes’. Debido a los pocos registros que contiene, apenas se notará la diferencia entre Left Join o Mapping Load.

Por otro lado, si contará con millones de registros se podría observar la disminución de tiempo a la hora de realizar la recarga de la aplicación.

1
2
3
4
5
6

   mNombreCliente:
      MAPPING LOAD DISTINCT
         idcli	        as	[ID Cliente],
         nombre     	as	[Nombre Cliente]
      FROM  Datos.xlsx
      (ooxml, embedded labels, table is Clientes);

3) Agrupar varios campos en un mismo Mapping Load

Como se ha mencionado en el anterior apartado, es posible agrupar diversos campos en un mismo Mapping Load mediante la concatenación de ellos.

Para ello, se concatenarán los campos separados por un carácter para diferenciarlos. Posteriormente, mediante la función Subfield(), QlikView o Qlik Sense se encargarán de distinguir cada uno por separado.

En el ejemplo de este artículo, se realiza el Mapping Load con la concatenación de los campos separados por el carácter ‘#‘:

1
2
3
4
5
6

       mNombreCliente:
       MAPPING LOAD DISTINCT
           idcli	           as	[ID Cliente],
           nombre&#&vip	           as	[Nombre VIP Cliente]
       FROM  Datos.xlsx
       (ooxml, embedded labels, table is Clientes);

A continuación, se realiza el Apply Map en la tabla que se quiera asignar los campos. En este caso en la tabla ‘Importes’:

1
2
3
4
5
6
7
8
9
10
11

        Importes_TMP:
        LOAD  
            id	                                        as  [ID Importe],
            idcli	                                as  [ID Cliente], 
            ApplyMap(mNombreCliente,idcli,Sin Valor)    as  [Nombre-VIP Cliente],
            idmayor					as  [ID Mayorista],
            imp_eur					as  [Importe EUR], 
            idprod					as  [ID Producto], 
            uni						as  [Unidades]
        FROM  Datos.xlsx
        (ooxml, embedded labels, table is Importes);

Finalmente, se separan los campos ‘Nombre’ y ‘VIP’ mediante una RESIDENT de la tabla:

1
2
3
4
5
6

      Importes:
         LOAD
             *,
             Subfield([Nombre-VIP Cliente],#,1)	as  [Nombre Cliente],
             Subfield([Nombre-VIP Cliente],#,2)	as  [VIP Cliente]
         RESIDENT  Importes_TMP;

4) Sum() VS Count()

En diferentes ocasiones, seguro que nos hemos planteado si las expresiones que utilizamos son las más óptimas o no.

Como dijo el genio Albert Einstein ‘Si buscas resultados distintos, no hagas siempre lo mismo‘. Por ello, siempre se ha de tratar de innovar, para evaluar si es mejor o no que lo anterior.

En QlikView o Qlik Sense NO todas las funciones son igual de óptimas, como por ejemplo las funciones Sum() y Count().

En el ejemplo de este artículo, para calcular el KPI ‘Número total de mayoristas‘ se podría utiliza la función Count() por el campo ‘Nombre’ o ‘ID’, la cual realizará comparaciones de los diferentes valores del campo para obtener el resultado.

En cambio, si se le añade el campo ‘Total Mayorista’ a la tabla ‘Mayorista’:

1
2
3
4
5
6
7
8

      Mayorista:
         LOAD 
           idmayor		as	[ID Mayorista],  
           nombre		as	[Nombre Mayorista], 
           u7bi2c		as	[Ubicación Mayorista], 
           1		as	[Total Mayorista]
         FROM   Datos.xlsx
         (ooxml, embedded labels, table is Mayoristas);

Se consigue poder realizar la función Sum() que obtendrá el mismo resultado que Count() pero con un tiempo de cálculo menor:

5) Excel para métricas

Definir las variables des de QlikView o Qlik Sense de cada una de las expresiones puede resultar una tarea monótona, poco intuitiva y con una alta probabilidad de error.

La mejor solución consiste en gestionar todas las expresiones de la aplicación mediante un fichero externo, concretamente con un Excel.

En el ejemplo de este artículo, se define la siguiente tabla:

1
2
3
4
5
6

        Métricas:
          LOAD 
             Nombre, 
             Formula
          FROM   Metricas.xlsx
          (ooxml, embedded labels, table is Metricas);

Dónde se le asignan las variables definidas en el fichero Excel mediante el siguiente bucle:

1
2
3
4
5

        Let vNumberOfRows = NoOfRows(Metricas);
        For v_i = 0 to (vNumberOfRows - 1)
	      Let vVariable_Name = Peek(Nombre,v_i,Nombre);
	      Let [$(vVariable_Name)] = Peek(Formula,v_i,Formula);
        Next

De esta forma, se conseguirá mejorar la gestión de todas las expresiones del documento (especialmente cuando son muchas) centralizándolo en un sólo fichero.

¿Dónde me puedo descargar el material mostrado?

Todo el material mostrado en el artículo (Aplicación, Insights, Script ETL, etc.) se puede descargar a través del siguiente enlace:

Ahora que conoces estos consejos y trucos para tu script ETL para QlikView…

¿Los estás aplicando en tu documento? ¿A qué esperas para implementarlos?