5 Tips para la modelización de modelos Import en Power BI

Conseguimos pasar de un modelo que solamente el pbix ocupaba 341 Mb a 17 Mb

De 68 tablas y 1103 columnas a 11 tablas y 87 columnas…

¡Y de un tamaño de 1,85 Gb a 87 Mb!

Por lo que, una vez claro el impacto que tiene modelar, saber que debemos definir correctamente las tablas de dimensiones y las tablas de hechos. Para ello voy a nombrar una frase del libro Fundamentos de modelado en estrella de Toni Jurado:

 “Cualquier cosa que queramos poner en una fila o columna de una tabla o en un eje de un gráfico DEBE SER un atributo de una dimensión y cualquier cosa que se deba representar como valor, tanto en una tabla como un gráfico, debe estar en una tabla de hechos.”

¿Cómo llegamos a un resultado como este? De la siguiente manera:

1. Intentar llegar siempre que podamos a un modelo en estrella.
2. Definir las relaciones correctamente entre tablas, es decir, la cardinalidad.
3. Definir correctamente las granularidades, por ejemplo, si el análisis del modelo va a ser semanal, la granularidad del modelo debe corresponderse con la misma y no ser diaria, o a nivel de segundos como era el caso del modelo ya que recogía las producciones de las plantas productivas. Este punto también va muy de la mano con el tercer tip.
4. Optimizar los tipos de datos de las columnas, es decir, una correcta identificación del tipo de dato ayudará al motor VertiPaq a la compresión de las columnas.

Esta parte son los cimientos de nuestro modelo, de nuestra casa, y es donde debemos dedicarle el máximo mimo y cuidado. Nos ahorrará mucho tiempo en el futuro…

Conclusión: No por traernos todas las tablas al modelo sin modelar, este va a rendir mejor.

Tip 2: Desactivar la inteligencia de tiempos automática

¿Por qué desactivar la inteligencia de tiempos automática? Por qué  Power BI “Nos va a crear una tabla de fechas oculta en nuestro modelo para cada CAMPO (Columna) que tenga un tipo de datos de fecha o fecha y hora.

¿De verdad que es necesario este ajuste para optimizar el modelo? ¿Tanto ocupan esas tablas ocultas? Veámoslo con un ejemplo. 

Podemos ver que dependiendo de la cantidad de históricos que sean necesarios traernos, puede llegar a ocupar más 50% del modelo, en este caso, estamos hablando de casi un 40% de espacio. Y esto implica podemos moverlo con un tipo de licencia u otro… Como se suele decir, cada mega, cuenta.

Para desactivar la inteligencia automática para la fecha y la hora en Opciones y Configuración – Opciones

Y lo podemos hacer tanto a nivel global:

o de proyecto: 

Conclusión: No por tener la fecha y hora automática, perdemos funcionalidades. Desactivándolas, nuestro modelo ocupa menos y puede marcar la diferencia entre qué tipo de licencia tendremos que usa.

Lanzo una pregunta, si en mi modelo no uso X columnas o filas, ¿para qué me las voy a traer a mi modelo? Sólo me van a ocupar espacio y no aportan valor a mi modelo ni responden preguntas de negocio.

¿Cómo eliminamos o evitamos tener columnas o filas innecesarias en nuestro modelo?

En nuestro proceso de extracción de la información, si nuestro origen nos lo permite, trayéndonos la información ya filtrada mediante sentencias SQL, DAX,.. Por ejemplo, si nuestro origen de datos es una base de datos relacional en SQL Server, podemos escribir la consulta para que se ejecute en origen y nos traiga las columnas que queremos, incluso, podemos generar nuestras vistas si nuestro DBA no nos la genera en DWH.

Si nuestro origen no nos permite la opción de filtrado, estas debemos eliminarlas mediante Power Query lo antes posible. Para ello, en el menú de Power Query, disponemos de las opciones Elegir Columnas y Quitar Columnas. Si hacemos click sobre “Elegir Columnas” se nos despliega un menú en el que seleccionamos las columnas necesarias para nuestro modelo.

Nota: Como buena práctica se recomienda usar el paso de “Elegir Columnas” ya que nos permite volver y reajustar las opciones de filtrado cosa que si optamos por “Quitar Columna” no nos lo permite.

Si tenemos columnas que son el resultado de la combinación de otras y que podemos calcularlas mediante métricas, no nos aportan, por lo que podemos prescindir de ellas. Por ejemplo, las columnas “TotalCost” y “SalesAmount” son el resultado del producto UnitCost por Quantity y UnitPrice por Quantity por lo que las podemos eliminar y calcularlas mediante iteradores.

La granularidad de la información. Otra técnica eficaz para reducir el tamaño de nuestro modelo es la posibilidad de agrupar/resumir los datos de nuestro modelo.  Si el requisito del modelo es un análisis por ejemplo semanal y los datos en nuestra tabla de hechos esta con una granularidad de día, podemos agruparlo por semana reduciendo el nivel de detalle de la información y considerablemente el peso de nuestro modelo.

Conclusión: No por traernos toda la información, nuestro modelo va a ser menos funcional. 

Tip 4: Métricas VS Columnas Calculadas

Una columna calculada no está tan optimizada como una columna nativa que nos traemos desde origen. Generalmente tienen una tasa de compresión más baja en comparación con las columnas nativas de la tabla porque no participa en el algoritmo que usa VertiPaq para encontrar el orden de clasificación óptimo de los datos en cada segmento.

Muy importante a tener en cuenta que si puede evitar una columna calculada creando el mismo valor con una columna nativa en el origen de datos al completar la tabla, por ejemplo, mediante una instrucción SQL o una transformación de Power Query, debemos implementarlo.

Una vez dicho esto, las columnas calculadas se calculan en tiempo de actualización, es decir, una vez terminado la carga de datos al modelo y se almacena en el modelo de datos en memoria mientras que las medidas o métricas se calculan dinámicamente, o lo que es lo mismo, no consumen casi casi memoria sino CPU.

Por ejemplo, en la imagen podemos ver nuestra tabla de hechos con 2 métricas y con 2 columnas calculadas, como podemos ver, el hecho de disponer de 2 columnas calculadas el modelo ha aumentado en 10 MB en una tabla de sólo 12 millones de registros.

Ojo, con esto no quiero demonizar a las columnas calculadas, ya que, si por el hecho de hacer una columna calculada nos puede ahorrar una métrica DAX súper compleja y laboriosa, la podemos hacer, pero a poder siempre en el origen o en Power Query.

Las buenas prácticas para cuando las columnas calculadas deben considerarse opciones viables generalmente en estas dos situaciones:

1. Agrupar o filtrar datos: si una columna calculada devuelve un valor utilizado para agrupar o filtrar datos, no hay otra alternativa que crear el mismo valor antes de importar datos al modelo de datos.
2. Precalcular fórmulas complejas: una columna calculada puede almacenar el resultado de un cálculo complejo que no es sensible a los filtros realizados en el momento de la consulta

Conclusión: Cuantas menos columnas calculadas con DAX en nuestro modelo, mejor.

Tip 5:  Uso de Variables

Hasta ahora, casi todo lo que hemos visto estaba orientado a la optimización de modelo en cuanto a espacio, ahora vamos a ver qué podemos hacer para la experiencia del usuario con el informe sea completamente satisfactoria. Y es el uso de variables.

Las variables tienen 4 objetivos, que son:

Rendimiento mejorado: las variables pueden hacer que las medidas sean más eficaces porque eliminan la necesidad de que Power BI evalúe varias veces la misma expresión.

Mejora de la legibilidad: las variables tienen nombres cortos y autodescriptivos (o deberían), y se usan en lugar de una expresión ambigua de varias palabras. Al usar variables, es posible que le resulte más fácil leer y comprender las fórmulas.

Depuración simplificada: puede usar variables para depurar una fórmula y probar expresiones, lo que puede resultar útil durante la solución de problemas.

Complejidad reducida: las variables no requieren el uso de las funciones DAX EARLIER o EARLIEST, que son difíciles de comprender. Estas funciones eran necesarias antes de que se introdujeran las variables y se escribieron en expresiones complejas que incorporaban nuevos contextos de filtro. Ahora que puede usar variables en lugar de esas funciones, puede escribir menos fórmulas complejas.

Vamos a verlo con un pequeño ejemplo: