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: