utilizó una característica de modelo compuesto en Power BI, es posible que ya haya oído hablar de otro concepto extremadamente importante y poderoso: ¡agregaciones! Esto se debe a que en muchos escenarios, especialmente con modelos a escala empresarial, las agregaciones son un “ingrediente” natural del modelo compuesto.
Sin embargo, como la función de los modelos compuestos también se puede aprovechar sin agregaciones involucradas, pensé que tendría sentido explicar el concepto de agregaciones en un artículo separado.
Antes de explicar cómo funcionan las agregaciones en Power BI y echar un vistazo a algunos casos de uso específicos, primero respondamos las siguientes preguntas:
¿Por qué necesitamos agregaciones en primer lugar? ¿Cuál es el beneficio de tener dos tablas con datos idénticos en el modelo?
Antes de aclarar estos dos puntos, es importante tener en cuenta que existen dos tipos diferentes de agregaciones en Power BI.
Las agregaciones definidas por el usuario eran, hasta hace un par de años, el único tipo de agregación en Power BI. Aquí, usted está a cargo de definir y administrar las agregaciones, aunque Power BI luego identifica automáticamente las agregaciones al ejecutar la consulta. Las agregaciones automáticas son una de las características más nuevas de Power BI. Con la función de agregaciones automáticas habilitada, puede tomar un café, sentarse y relajarse, ya que los algoritmos de aprendizaje automático recopilarán los datos sobre las consultas que se ejecutan con mayor frecuencia en sus informes y crearán automáticamente agregaciones para respaldar esas consultas.
La distinción importante entre estos dos tipos, por supuesto, además del hecho de que con las agregaciones automáticas no necesita hacer nada excepto activar esta característica en su inquilino, son las limitaciones de la licencia. Si bien las agregaciones definidas por el usuario funcionarán tanto con Premium como con Pro, las agregaciones automáticas en este momento requieren una licencia Premium.
De ahora en adelante, hablaremos únicamente de agregaciones definidas por el usuario, solo téngalo en cuenta.
Bien, aquí hay una breve explicación de las agregaciones y la forma en que funcionan en Power BI. Este es el escenario: tiene una tabla de hechos muy grande, que puede contener cientos de millones o incluso miles de millones de filas. Entonces, ¿cómo se manejan las solicitudes analíticas sobre una cantidad tan grande de datos?
¡Simplemente crea tablas agregadas! En realidad, es una situación muy rara, o digamos que es más una excepción que una regla, que el requisito analítico sea ver la transacción individual o el registro individual con el nivel más bajo de detalle. En la mayoría de los escenarios, desea realizar un análisis de datos resumidos: por ejemplo, ¿cuántos ingresos tuvimos en un día específico? ¿O cuál fue el monto total de ventas del producto X? Además, ¿cuánto gastó el cliente X en total?
Además, puede agregar los datos sobre múltiples atributos, como suele ser el caso, y resumir las cifras para una fecha, cliente y producto específicos.
Si se pregunta cuál es el punto de agregar los datos… Bueno, el objetivo final es reducir el número de filas y, en consecuencia, reducir el tamaño general del modelo de datos, preparando los datos con antelación.
Entonces, si necesito ver el monto total de ventas gastado por el cliente X en el producto Y en el primer trimestre del año, puedo aprovechar el hecho de tener estos datos ya resumidos de antemano.
“Ingrediente” clave: ¡Haga que Power BI sea “consciente” de las agregaciones!
Ok, ese es un lado de la historia. Ahora viene la parte más interesante. Crear agregaciones per se no es suficiente para acelerar sus informes de Power BI: ¡debe hacer que Power BI tenga en cuenta las agregaciones!
Sólo una observación antes de continuar: el conocimiento de la agregación es algo que funcionará únicamente si la tabla de hechos original utiliza el modo de almacenamiento DirectQuery. Pronto explicaremos cómo diseñar y administrar agregaciones y cómo configurar el modo de almacenamiento adecuado de sus tablas. En este momento, tenga en cuenta que la tabla de hechos original debe estar en modo DirectQuery.
¡Comencemos a construir nuestras agregaciones!
Como puede ver en la ilustración anterior, nuestro modelo es bastante simple: consta de una tabla de hechos (FactOnlineSales) y tres dimensiones (DimDate, DimStore y DimProduct). Todas las tablas utilizan actualmente el modo de almacenamiento DirectQuery.
Vamos a crear dos tablas adicionales que usaremos como tablas agregadas: la primera agrupará los datos por fecha y producto, mientras que la otra usará fecha y almacén para agrupar:
/*Tabla 1: Datos agregados por fecha y producto */ SELECT DateKey, ProductKey, SUM(SalesAmount) AS SalesAmount, SUM(SalesQuantity) AS SalesQuantity FROM FactOnlineSales GROUP BY DateKey, ProductKey /*Tabla 2: Datos agregados por fecha y tienda */ SELECT DateKey, StoreKey, SUM(SalesAmount) AS SalesAmount, SUM(SalesQuantity) AS SalesQuantity FROM FactOnlineSales GROUP BY DateKey, StoreKey
Cambié el nombre de estas consultas a Sales Product Agg y Sales Store Agg respectivamente y cerré el editor de Power Query.
Como queremos obtener el mejor rendimiento posible para la mayoría de nuestras consultas (estas consultas que recuperan los datos resumidos por fecha y/o producto/tienda), cambiaré el modo de almacenamiento de las tablas agregadas recién creadas de DirectQuery a Importar:
Ahora, estas tablas están cargadas en la memoria caché, pero aún no están conectadas a nuestras tablas de dimensiones existentes. Creemos relaciones entre dimensiones y tablas agregadas:
Antes de continuar, permítanme detenerme por un momento y explicar lo que sucedió cuando creamos relaciones. Si recuerda nuestro artículo anterior, mencioné que hay dos tipos de relaciones en Power BI: regulares y limitadas. Esto es importante: siempre que haya una relación entre las tablas de diferentes grupos de origen (el modo de importación es un grupo de origen, DirectQuery es otro), ¡tendrá una relación limitada! Con todas sus limitaciones y limitaciones.
¡Pero tengo buenas noticias para ti! Si cambio el modo de almacenamiento de mis tablas de dimensiones a Dual, eso significa que también se cargarán en la memoria caché y, según qué tabla de hechos proporcione los datos en el momento de la consulta, la tabla de dimensiones se comportará como modo de importación (si la consulta apunta a tablas de hechos del modo de importación) o como DirectQuery (si la consulta recupera los datos de la tabla de hechos original en DirectQuery):
Como podrás notar, ya no hay relaciones limitadas, ¡lo cual es fantástico!
Entonces, para concluir, nuestro modelo está configurado de la siguiente manera:
La tabla FactOnlineSales original (con todos los datos detallados) – Tablas de dimensiones DirectQuery (DimDate, DimProduct, DimStore) – Tablas agregadas duales (Sales Product Agg y Sales Store Agg) – Importar
¡Impresionante! Ahora tenemos nuestras tablas agregadas y las consultas deberían ejecutarse más rápido, ¿verdad? ¡Bip! ¡Equivocado!
El objeto visual de tabla contiene exactamente estas columnas que agregamos previamente en nuestra tabla Sales Product Agg. Entonces, ¿por qué Power BI ejecuta DirectQuery en lugar de obtener los datos de la tabla importada? ¡Esa es una pregunta justa!
¿Recuerda cuando le dije al principio que debemos hacer que Power BI tenga en cuenta la tabla agregada para que pueda usarse en las consultas?
Volvamos a Power BI Desktop y hagamos esto:
Haga clic derecho en la tabla Sales Product Agg y elija la opción Administrar agregaciones:
Aquí algunas observaciones importantes: para que las agregaciones funcionen, los tipos de datos entre las columnas de la tabla de hechos original y la tabla agregada deben coincidir. En mi caso, tuve que cambiar el tipo de datos de la columna SalesAmount en mi tabla agregada de “Número decimal” a “Número decimal fijo”.
Además, verá el mensaje escrito en rojo: eso significa que, una vez que cree una tabla agregada, ¡estará oculta para el usuario final! Apliqué exactamente los mismos pasos para mi segunda tabla agregada (Tienda) y ahora estas tablas están ocultas:
Regresemos ahora y actualicemos nuestra página de informe para ver si algo cambió:
¡Lindo! Esta vez no se utilizó DirectQuery y, en lugar de los casi 2 segundos necesarios para representar este objeto visual, ¡esta vez solo tomó 58 milisegundos! Además, si tomo la consulta y voy a DAX Studio para comprobar qué está pasando…
Como puede ver, la consulta original se asignó para apuntar a la tabla agregada desde el modo de importación, y el mensaje “Coincidencia encontrada” dice claramente que los datos del objeto visual provienen de la tabla Agg de productos de ventas. ¡Aunque nuestro usuario no tiene ni idea de que esta tabla existe en el modelo!
¡La diferencia de rendimiento, incluso en este conjunto de datos relativamente pequeño, es enorme!
Múltiples tablas agregadas
Ahora probablemente se esté preguntando por qué creé dos tablas agregadas diferentes. Bueno, digamos que tengo una consulta que muestra los datos de varias tiendas, también agrupados por dimensión de fecha. En lugar de tener que escanear 12,6 millones de filas en modo DirectQuery, el motor puede servir fácilmente los números del caché, ¡de la tabla que tiene solo unos pocos miles de filas!
Básicamente, puede crear varias tablas agregadas en el modelo de datos, no solo combinando dos atributos de agrupación (como hicimos aquí con Fecha+Producto o Fecha+Tienda), sino incluyendo atributos adicionales (por ejemplo, incluir Fecha y Producto y Tienda en una tabla agregada). De esta manera, aumentará la granularidad de la tabla, pero en caso de que su objeto visual necesite mostrar los números tanto del producto como de la tienda, ¡solo podrá recuperar los resultados del caché!
En nuestro ejemplo, como no tengo datos agregados previamente en el nivel que incluye tanto el producto como la tienda, si incluyo una tienda en la tabla, pierdo el beneficio de tener tablas agregadas:
Por lo tanto, para aprovechar las agregaciones, debe tenerlas definidas exactamente en el mismo nivel de detalle que requiere el elemento visual.
Precedencia de agregación
Hay una propiedad más importante que debemos comprender cuando se trabaja con agregaciones: ¡la precedencia! Cuando se abre el cuadro de diálogo Administrar agregaciones, hay una opción para establecer la prioridad de agregación:
¡Este valor “indica” a Power BI qué tabla agregada usar en caso de que la consulta pueda satisfacerse desde varias agregaciones diferentes! De forma predeterminada, está establecido en 0, pero puedes cambiar el valor. Cuanto mayor sea el número, mayor será la precedencia de esa agregación.
¿Por qué es esto importante? Bueno, piense en un escenario en el que tenga la tabla de hechos principal con miles de millones de filas. Y crea múltiples tablas agregadas en diferentes granos:
Tabla agregada 1: agrupa datos a nivel de Fecha – tiene ~ 2000 filas (5 años de fechas) Tabla agregada 2: agrupa datos a nivel de Fecha y Producto – tiene ~ 100.000 filas (5 años de fechas x 50 productos) Tabla agregada 3: agrupa datos a nivel de Fecha, Producto y Tienda – tiene ~ 5.000.000 filas (100.000 del grano anterior x 50 tiendas)
Ahora, digamos que el objeto visual del informe muestra datos agregados solo en el nivel de fecha. ¿Qué opinas: es mejor escanear la Tabla 1 (2000 filas) o la Tabla 3 (5 millones de filas)? Creo que sabes la respuesta 🙂 En teoría, la consulta se puede satisfacer desde ambas tablas, entonces, ¿por qué confiar en la elección arbitraria de Power BI?
En cambio, cuando cree varias tablas agregadas, con diferentes niveles de granularidad, asegúrese de establecer el valor de prioridad de manera que las tablas con menor granularidad tengan prioridad.
Conclusión
Las agregaciones son una de las características más potentes de Power BI, ¡especialmente en escenarios con grandes conjuntos de datos! A pesar de que tanto las características de los modelos compuestos como las agregaciones se pueden usar de forma independiente, estos dos generalmente se usan en sinergia, para proporcionar el equilibrio más óptimo entre el rendimiento y tener todos los detalles de los datos disponibles.
¡Gracias por leer!