Migrar de Qlik a PowerBI

En esta entrada enseñaremos cómo traducir operaciones básicas de transformación, modelado de datos y creación de medidas desde la plataforma Qlikview o QlikSense a Power BI. Lo que te permitirá migrar Qlik a Power BI teniendo claras las equivalencias entre ambos productos.

Primero introduciremos las arquitecturas de las aplicaciones y sus similitudes.

Podemos hacer un paralelismo entre las partes de las aplicaciones y sus funcionalidades:

• Data Manager y Query/M: Estos componentes se encargan de todo lo referente a extracción del dato y a la transformación del mismo.
• Data Model y Model: Son componentes destinados a marcar cómo se relacionan los datos.
• Set Analysys y DAX: Es la capa semántica por medio de la cual se crean métricas e indicadores del modelo.

Migrar Qlik a Power BI – ETL: Data Manager y Query/M

Teniendo claro los componentes de las aplicaciones, nos centraremos en la capa de extracción y transformaciones mostrando las operaciones básicas que tenemos en Qlik y cómo emularlas en Power BI.

Este primer código muestra cómo se aplica una operación de Join en Qlik:

[Hechos_2016]:
LOAD [Fecha],
    [ID_Estado_Articulo],
    [ID_Licencia],
    [Precio Unitario],
    [Venta Unidades],
    [Precio Venta],
    [ID_Campaña],
    [ID_Franquicia]
 
 FROM [lib://Hechos/data_2017.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);

LEFT JOIN (Hechos_2016)
[Dim_Campaña]:
LOAD [Campaña],
    [ID] AS ID_Campaña,
    [Tipo]
 FROM [lib://Dimensiones/Dim_Campaña.xlsx]
(ooxml, embedded labels, table is Dim_Campaña);

Lo primero que llamaría la atención a cualquier desarrollador que tenga alguna experiencia con SQL es que no sabemos por qué campo se está aplicando la left join. Se hace por medio de los campos que tienen el mismo nombre, en este caso sería ID_Campaña y además se agregarían en la tabla final todas las columnas de la tabla origen.

En Power BI (Query)  esta operación podemos hacerla desde el menú Merge Queries:

Seleccionamos las columnas por las cuales se hará el join, el tipo de los datos debe ser el mismo.

Y finalmente elegimos columnas queremos mostrar:

Esto nos sirve para todos los tipos de Joins.

Otra operación básica que tenemos en Qlik es concatenate. Para esto modificamos un poco el código anterior:

[Hechos]:
LOAD [Fecha],
    [ID_Estado_Articulo],
    [ID_Licencia],
    [Precio Unitario],
    [Venta Unidades],
    [Precio Venta],
    [ID_Campaña],
    [ID_Franquicia]
 
 FROM [lib://Hechos/data_2016.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);
Concatenate(Hechos)
LOAD [Fecha],
    [ID_Estado_Articulo],
    [ID_Licencia],
    [Precio Unitario],
    [Venta Unidades],
    [Precio Venta],
    [ID_Campaña],
    [ID_Franquicia]
 
 FROM [lib://Hechos/data_2017.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);

LEFT JOIN (Hechos)
[Dim_Campaña]:
LOAD [Campaña],
    [ID] AS ID_Campaña,
    [Tipo]
 FROM [lib://Dimensiones/Dim_Campaña.xlsx]
(ooxml, embedded labels, table is Dim_Campaña);

Esta operación une dos tablas, una debajo de otra ,en este caso une los hechos del 2016 con los hechos del 2017.

Obteniendo como resultado:

Esto lo podemos hacer en Power BI utilizando el menú Append Queries

Obteniendo como resultado la concatenación de las dos tablas:

Otra operación recurrente en Qlik es utilizar un Mapping Load:

Esta operación básicamente genera una tabla clave-valor que se guarda en memoria y se utiliza durante todo el script para reemplazar las claves en las tablas que se necesite. Es importante el orden en el script de carga, la primera columna será utilizada cómo clave para la búsqueda y las demás columnas como valor a devolver.

Por ejemplo:

[MAP_LICENCIA]:
Mapping LOAD  
    ID,
     Articulos.Licencia as Licencia_DESC
FROM [lib://Dimensiones/Dim_Licencia.xlsx]
(ooxml, embedded labels, table is Dim_Licencia);

Con esto tenemos la tabla de mapeo que se guarda en memoria para poder reutilizarla en todas las tablas que necesitemos dentro del script de carga, después de esto procedemos a aplicar los mapeos ,obteniendo como resultado las descripciones de las licencias

[Hechos]:
LOAD [Fecha],
    [ID_Estado_Articulo],
    [ID_Licencia],
    ApplyMap('MAP_LICENCIA',ID_Licencia) as Licencia_DESC,
    [Precio Unitario],
    [Venta Unidades],
    [Precio Venta],
    [ID_Campaña],
    [ID_Franquicia]
 
 FROM [lib://Hechos/data_2016.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);
Concatenate(Hechos)
LOAD [Fecha],
    [ID_Estado_Articulo],
    ApplyMap('MAP_LICENCIA',ID_Licencia) as Licencia_DESC,
    [Precio Unitario],
    [Venta Unidades],
    [Precio Venta],
    [ID_Campaña],
    [ID_Franquicia]
 
 FROM [lib://Hechos/data_2017.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);
[Hechos]:
LOAD [Fecha],
    [ID_Estado_Articulo],
    [ID_Licencia],
    ApplyMap('MAP_LICENCIA',ID_Licencia) as Licencia_DESC,
    [Precio Unitario],
    [Venta Unidades],
    [Precio Venta],
    [ID_Campaña],
    [ID_Franquicia]
 
 FROM [lib://Hechos/data_2016.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);
Concatenate(Hechos)
LOAD [Fecha],
    [ID_Estado_Articulo],
    ApplyMap('MAP_LICENCIA',ID_Licencia) as Licencia_DESC,
    [Precio Unitario],
    [Venta Unidades],
    [Precio Venta],
    [ID_Campaña],
    [ID_Franquicia]
 
 FROM [lib://Hechos/data_2017.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);

Esto lo podemos hacer en Power BI utilizando el menú merge query:

Tal vez estén pensando qué funcionalidad agregada pueda tener el mapping load sobre un left join normal, bueno se suele utilizar las tablas mapping cuando tenemos un subset de datos pequeño. Es más rápido porque utilizamos sólo dos columnas (clave y el valor) en vez de hacer una left join con una tabla completa que pesa más.

Una posible aplicación puede ser borrar de la tabla destino todas las filas que se encuentren el mappling load.

Por ejemplo, tenemos el siguiente listado de licencias que queremos borrar:

Lo cargamos dentro de Qlik cómo una tabla de mapeo poniendo a 1 un flag que indica que el registro necesita ser borrado.

[MAP_LICENCIA_BORRAR]:
Mapping LOAD 
   
    ID,
    '1' as Borrar 
FROM [lib://Dimensiones/Dim_Licencia_Borrar.xlsx]
(ooxml, embedded labels, table is Dim_Licencia);

Entonces el siguiente paso es aplicar el mapping en las tablas que necesitamos guardar en una tabla auxiliar y finalmente filtrar estos datos:

//TABLA DE BORRADO
[MAP_LICENCIA_BORRAR]:
Mapping LOAD 
   
    ID,
    '1' as Borrar 
FROM [lib://Dimensiones/Dim_Licencia_Borrar.xlsx]
(ooxml, embedded labels, table is Dim_Licencia);

Aplicamos los los mappeos
[Hechos_AUX]:
LOAD [Fecha],
    [ID_Estado_Articulo],
    [ID_Licencia],
    ApplyMap('MAP_LICENCIA',ID_Licencia) as Licencia_DESC,
    ApplyMap('MAP_LICENCIA_BORRAR',ID_Licencia) as Licencia_Borrar,
    [Precio Unitario],
    [Venta Unidades],
    [Precio Venta],
    [ID_Campaña],
    [ID_Franquicia]
 
 FROM [lib://Hechos/data_2016.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);
Concatenate(Hechos_AUX)
LOAD [Fecha],
    [ID_Estado_Articulo],
      ApplyMap('MAP_LICENCIA',ID_Licencia) as Licencia_DESC,
     ApplyMap('MAP_LICENCIA_BORRAR',ID_Licencia) as Licencia_Borrar,
    [Precio Unitario],
    [Venta Unidades],
    [Precio Venta],
    [ID_Campaña],
    [ID_Franquicia]
 
 FROM [lib://Hechos/data_2017.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);

LEFT JOIN (Hechos_AUX)
[Dim_Campaña]:
LOAD [Campaña],
    [ID] AS ID_Campaña,
    [Tipo]
 FROM [lib://Dimensiones/Dim_Campaña.xlsx]
(ooxml, embedded labels, table is Dim_Campaña);

Cogemos de la tabla auxiliar y filtramos los valores 1

HECHOS :
NoConcatenate
LOAD *
RESIDENT  Hechos_AUX
WHERE Licencia_Borrar <>'1';

Esto lo podemos aplicar en Power BI con la opción merge Queries pero seleccionando anti-row:

De esta forma en la tabla hechos sólo dejaremos las filas que no se encuentran en la tabla Dim_Licencia_borrar:

Con estas operaciones básicas podemos migrar casi cualquier aplicación que tengamos en Qlik, las demás operaciones de transformación de columnas tales como

• Transformaciones de tipo
• Rounds
• Logaritmos
• transformaciones de texto.
• etc.

Se pueden hacer mediante los menús superiores de transformación o simplemente click derecho sobre la columna a transformar y elegimos una opción.

Jerarquías:

Para implementar Jerarquías en Qlik es necesario primero aplanar la jerarquía y después agregarla cómo una tabla al modelo, uniéndola con la tabla de hechos por ID_Nodo:

Por ejemplo, agregamos una tabla inline (tabla en la cual se definen sus columnas y valores en línea de código).

ORG_TABLE:
LOAD * INLINE [
    Padre,Hijo,Nieto
    PadreA, HijoA, NietoA
    PadreB, HijoB,  NietoB
    PadreA, HijoC, NietoC
];

Y después tenemos que aplanar la tabla dejando una columna de valor y otra de identificador del nodo por cada nivel de la jerarquía, en este caso:

ITEM:
LOAD Distinct Nieto as VALUE, Hijo & '-Nieto' as NODE_ID, Hijo & '-Hijo' as PARENT_NODE_ID resident ORG_TABLE;
LOAD Distinct Hijo as VALUE,Hijo & '-Hijo' as NODE_ID, Padre & '-Padre' as PARENT_NODE_ID resident ORG_TABLE;
LOAD Distinct Padre as VALUE,Padre & '-Padre' as NODE_ID,  Padre & '-Padre' as PARENT_NODE_ID resident ORG_TABLE;

Obtendríamos como resultado la tabla:

Se ha definido cómo identificador del Nodo HijoA-Nieto (sería el hijo del HijoA), nos devuelve el valor NietoA y su padre es HijoA-Hijo.

HIERARCHY_TABLE:
Hierarchy(NODE_ID_H,PARENT_NODE_ID_H, ID, PARENT_NAME, NAME, NAME_FOR_TREE) load
NODE_ID,
NODE_ID as NODE_ID_H,
PARENT_NODE_ID as PARENT_NODE_ID_H,
VALUE as ID,
VALUE as NAME
resident ITEM;

Por medio de la sentencia hierarchy decimos qué es una jerarquía utilizando para cada nivel los identificadores de nodos y su identificador de nodo padre, además de sus valores (identificador y nombre).

La función nos devolverá la jerarquía para cada uno de los nodos, su padre y el nombre o posición dentro del árbol.

En Power BI esto se hace de una forma más sencilla:

Solo por comentarlo en Power BI también tenemos la opción de agregar una tabla a mano, utilizando la opción enter data (aunque para este ejemplo no es necesario agregar datos).

Para crear una jerarquía solo tenemos que arrastar la columna “hijo” sobre la columna “padre”.

Arrastramos a un gráfico y podemos observar la jerarquía.

Migrar Qlik a Power BI – Datos: Data Model y Model

La principal diferencia entre Modelar/Desarrollar con el Data model de Qlik y Model de Power BI (sin tener en cuenta el engine, ni la forma de comprensión de los datos) es que tienen distintos motores al momento de relacionar los datos.

Data Model:

Asocia los datos a través de la coincidencia de nombres y todas las relaciones son bidireccionales, sin tener en cuenta cardinalidades entre las tablas del modelo. Este modelo es el llamado “Asociativo” de qlikview en el que podemos asociar todos los datos sin ningún tipo de restricción con lo que esto conlleva.

Por ejemplo ,cargamos una dimensión dentro de Qlik que sabemos que tenemos duplicados:

Y mostramos los datos en una tabla:

Esto puede generar incongruencias. Como todo un gran poder conlleva una gran responsabilidad y su mal uso puede generar modelos inmantenibles.

Ejemplo de mal modelado:

De todas formas, me gustaría remarcar que las recomendaciones de Qlik consisten en hacer un modelo en estrella o en su defecto, copo de nieve:

• https://help.qlik.com/es-ES/qlikview/12.1/Subsystems/Client/Content/LoadData/best-practices-data-modeling.htm
• https://help.qlik.com/es-ES/sense/1.1/Subsystems/Hub/Content/DataModeling/BestPracticesExamplesDataModeling.htm

y un modelo tan flexible bien utilizado nos puede facilitar el trabajo.

Ejemplo de buen modelado:

Model:

En Power BI en el modelo tabular los desarrolladores tienen que decir cómo se relacionan los datos, su cardinalidad y la dirección de filtrado.Por ejemplo:

Esto ofrece la ventaja que en cada momento sabemos que se está filtrando, en qué dirección va la relación y qué tenemos en cada dimensión(cardinalidad). Es recomendable dejar un filtrado simple y siempre filtrar por los valores de la dimensión, de esta forma nos evitamos incongruencias en las medidas.

Si intentamos agregar a un modelo de Power BI una dimensión con duplicados tenemos lo siguiente:

Me gustaría recalcar, que para migrar un modelo desde Qlik a Power BI se tiene que estudiar cada caso, debido a que dependiendo de la calidad del modelo de datos deberíamos volver a modelar o simplemente darle una vuelta de tuerca para emular la “flexibilidad” en nuestro modelo tabular.

Migrar Qlik a Power BI – cálculos analíticos: Set Analysys y DAX

Las diferencias entre Set Analysis y DAX a nivel de desarrollador, simplemente es la sintaxis de los lenguajes.

Algunos ejemplos:

1.Suma de las unidades de venta
o   SA: Sum([Venta Unidades])
o   DAX: sum(Hechos[Venta Unidades])

2.Cuenta franquicias sin abeja maya
o   SA: Count({ <Franquicia-={'Abeja Maya'}>} ID_Franquicia )
o   DAX:CALCULATE(COUNT(Dim_Franquicia[ID_Franquicia]),Dim_Franquicia[Franquicia]<>"Abeja Maya")
 
3.Para dos condiciones
o   SA: Count({ <Franquicia-={'Abeja Maya'}, Segmento-={'Baby'} >} ID_Franquicia )
o   DAX:CALCULATE(COUNT(Dim_Franquicia[ID_Franquicia]),Dim_Franquicia[Franquicia]<>"Abeja Maya",Dim_Franquicia[Segmento]<>"Baby")

4.Para búsqueda dentro de un texto
Cuenta licencias que empiezan con J, ignorando selección en IdLicencia.
o SA: Count({<Licencia_DESC={"A*"}>}ID_Licencia)
o DAX: CALCULATE(COUNT(Dim_Licencia[ID_Licencia]),left(trim(Dim_Licencia[Licencia]),1)="A")
 
5.Precio de la venta
o SA:SUM([Precio Unitario])* SUM([Venta Unidades])
o DAX:SUM(Hechos[Precio Unitario])*SUM(Hechos[Venta Unidades])

6.Numero de unidades medio 
o SA:AVG([Venta Unidades])
o DAX:AVERAGE(Hechos[Venta Unidades])

Con esto llegamos al final del post, en el cual hemos ido capa por capa viendo los aspectos básicos a tener en cuenta al migrar una aplicación hecha en Qlik a Power BI.

Cualquier duda escribir un comentario. 🙂