Relaciones en Power BI y Power Pivot

Share on facebook
Share on twitter
Share on linkedin
Share on telegram
Share on whatsapp

Contenidos

Nivel: principiantes

Power Pivot es una base de datos que ha sido diseñada desde cero para ser optimizado para informes y análisis en Power BI, Power Pivot para Excel y SSAS Tabular. La tecnología es esencialmente la misma en todos estos productos, por lo que me referiré genéricamente a Power Pivot en este artículo.

Power Pivot usa un in memory base de datos columnar (Vertipaq) como tecnología básica: la misma tecnología para todas las versiones (Power Pivot, Power BI y SSAS Tabular). El motor Vertipaq es lo que hace que Power Pivot sea súper rápido y altamente comprimido. Una base de datos de Power Pivot no es lo mismo que una base de datos relacional (RDB) y no admite todos los tipos de relaciones que admiten las bases de datos relacionales. Esto puede resultar confuso para las personas que son nuevas en Power Pivot, especialmente si tienen al menos un conocimiento básico de cómo funcionan las bases de datos (como MS Access). Aquí explico lo que necesita saber para comenzar con las relaciones en Power Pivot.

Relaciones virtuales

Este artículo trata específicamente sobre las relaciones físicas, sin embargo, hay formas de crear relaciones virtuales utilizando DAX. Los ejemplos incluyen el uso de LOOKUPVALUE, FILTER, CROSSFILTER y otras técnicas. No estoy cubriendo este tipo de relaciones en este artículo.

super-charge-power-bi-ad_1-1024x128-6595595

Tipos de cardinalidad de relación

Hay 3 tipos diferentes de cardinalidad de relación física posible en el diseño de bases de datos, pero no todos son compatibles con Power Pivot.

Tipo de cardinalidad de relación Soporte de Power BI Soporte de Power Pivot para Excel
Uno a muchos
Doce y cincuenta y nueve de la noche No
Muchos a muchos No No

Una a muchas relaciones

image_thumb-6645807

La relación de uno a muchos es la base de Power Pivot. En el ejemplo anterior (de Adventure Works en Power BI Desktop), la tabla Clientes está en el lado 1 de la relación y la tabla Ventas está en el lado de muchos de la relación. Estas tablas se unen mediante un campo / columna común llamado «CustomerKey». Customer Key (también conocido como número de cliente) es un código que identifica de forma única a cada cliente. No puede haber duplicados de la clave del cliente en la tabla de clientes. Por el contrario, el cliente puede comprar tantas veces como sea necesario y, por lo tanto, la clave del cliente puede aparecer en la tabla Ventas tantas veces como sea necesario. De aquí proviene el nombre «uno a muchos»: la clave del cliente aparece una y solo una vez en la tabla Clientes, pero puede aparecer muchas veces en la tabla Ventas.

Las tablas en un lado de la relación se llaman tablas de dimensiones (las llamo tablas de búsqueda) y las tablas en el lado de la relación se llaman tablas de hechos (las llamo tablas de datos).

Todo el motor Power Pivot Vertipaq está optimizado para trabajar con este tipo de relación (de uno a muchos).

Relaciones uno a uno

La relación uno a uno solo se admite en Power BI y la versión más reciente de SSAS Tabular. En mi opinión, este tipo de relación tiene un valor limitado y, en la mayoría de los casos, es mejor combinar estas tablas en una sola tabla plana antes de cargarlas en Power BI. Considere el modelo a continuación.

image_thumb-1-1544302

La primera relación (mostrada como 1) es una relación de 1 a muchos entre la tabla Cliente (tabla de búsqueda) y la tabla Ventas (tabla de datos). La tabla de datos socioeconómicos del cliente se une a la tabla de clientes a través de una relación de 1 a 1 (como se muestra en el 2 arriba). Si hay un beneficio (para el usuario de los informes) de dividir estos datos socioeconómicos en una tabla separada, por supuesto que debería hacerlo. Si no hay ningún beneficio, le recomiendo que combine todos los datos de la tabla Datos socioeconómicos del cliente en la tabla Cliente utilizando Power Query en carga.

Toda relación tiene un «costo» en el sentido de que tendrá algún efecto en el desempeño. Es posible que el impacto en el rendimiento no sea perceptible para modelos simples, pero puede convertirse en un problema con modelos muy complejos.

Si solo recuerda una cosa de este artículo, déjela ser esta: No acepte automáticamente la estructura de la tabla que proviene de sus datos de origen. Ahora es un modelador de datos y necesita tomar decisiones sobre la mejor manera de cargar sus datos. Es probable que su sistema de origen no esté optimizado para los informes (a menos que sea una despensa de datos de informes), así que no asuma que lo que tiene es lo que necesita.

Relaciones de muchos a muchos

El tipo de relación de muchos a muchos no se admite en Power Pivot. Esta es una decisión de diseño deliberada que se ha tomado como compensación para garantizar un rendimiento óptimo de la base de datos. Si tiene datos que están relacionados lógicamente con una cardinalidad de muchos a muchos, existen técnicas de modelado que puede utilizar para resolver el problema en Power Pivot que se tratan en mi artículo de patrones de muchos a muchos aquí.

Diferencias de UI

Hay algunas diferencias en la interfaz de usuario de Power BI Desktop / Excel 2016 (vista de relaciones) y la de Excel 2010/2013.

Excel 2010/2013

La interfaz de usuario inicial tiene una flecha que apunta a la tabla de búsqueda (un lado de la relación) y un punto en el lado de muchos. Esto es lamentable ya que la flecha apunta en la dirección opuesta a la propagación del filtro. Esto solo existe en Excel 2010/2013 (y la versión anterior de SSAS Tabular).

image4-7510991

Power BI / Excel 2016

La interfaz de usuario se ha mejorado significativamente con Power BI Desktop y Excel 2016. Como puede ver a continuación, la relación de 1 a muchos ahora se muestra claramente y también hay una nueva flecha que muestra la dirección de propagación automática del filtro.

image_thumb-2-6557230

Una relación activa

Es posible tener más de una relación entre tablas en Power Pivot, pero solo una puede estar activa a la vez. Un ejemplo de cuando puede querer múltiples relaciones es si tiene un departamento de ventas.[Order Date] y una venta[Ship Date] en su tabla de datos.

image_thumb6-7706148

En este escenario (que se muestra arriba en Excel 2013), es posible que desee unir ambas columnas de Fecha de venta a su tabla de Calendario para poder usar la inteligencia de tiempo en su modelo de datos tanto en la Fecha de pedido como en la Fecha de envío.

La relación activa se muestra como una línea continua (arriba) y la relación inactiva se muestra como una línea discontinua (en este caso, se resalta en azul arriba). Sin embargo, la relación activa se utiliza de forma predeterminada en todas las medidas DAX puede anular este valor predeterminado y usar la relación inactiva (cuando sea necesario) mediante la función USERELATIONSHIP (). Los detalles completos sobre esto están cubiertos en mi artículo aquí.

Comportamiento de filtrado cruzado

Power Pivot está optimizado para trabajar con una a varias relaciones y para propagar filtros automáticamente (contexto de filtro) de un lado a otro. En todas las versiones de Power Pivot para Excel, este es el ÚNICO tipo de propagación de filtro que está disponible.

Power BI admite el comportamiento de filtrado cruzado bidireccional (que se muestra a la derecha a continuación), así como una dirección única (que se muestra a la izquierda a continuación).

image_thumb-3-9949318

De hecho, el filtrado bidireccional es el comportamiento predeterminado de Power BI Desktop. Hay muchas personas (incluyéndome a mí) que piensan que esta es una mala idea, ya que el filtrado cruzado bidireccional tiene un costo: hay una sobrecarga de filtrado cruzado constante de la tabla de búsqueda en función del contenido de la tabla de datos en el momento en que es en realidad no es necesario. Seguro que si tiene un modelo simple y necesita este comportamiento y no sabe cómo manejarlo con el patrón de muchos a muchos, enciéndalo. Pero seguramente esto no debería estar activado por defecto. Además, si tiene más de una tabla de datos, el filtrado cruzado bidireccional puede generar referencias circulares y causar más confusión a los usuarios desprevenidos.

Creo que Microsoft está tratando de hacer que Power BI sea más fácil de usar para el «usuario no especializado», sin embargo, en este caso, creo que Microsoft ha cometido un error. Hágase un favor y desactive el filtrado cruzado bidireccional a menos que lo necesite explícitamente. Para cambiarlo, simplemente haga doble clic en la flecha y establezca la dirección del filtro cruzado en simple.

Actualización: la semana pasada, 17 de febrero de 2017, noté que los nuevos modelos que construí eran unidireccionales de forma predeterminada; parece que Microsoft ha escuchado y cambiado el comportamiento predeterminado.

image_thumb-4-8088109

Envolver

Esperamos que este artículo le haya ayudado a comprender mejor cómo funciona Power Pivot. Déjame saber en los comentarios a continuación si hay algo que me haya perdido.

!function(f,b,e,v,n,t,s)
{if(f.fbq)return;n=f.fbq=function(){n.callMethod?
n.callMethod.apply(n,arguments):n.queue.push(arguments)};
if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version=’2.0′;
n.queue=[];t=b.createElement(e);t.async=!0;
t.src=v;s=b.getElementsByTagName(e)[0];
s.parentNode.insertBefore(t,s)}(window,document,’script’,
‘https://connect.facebook.net/en_US/fbevents.js’);
fbq(‘init’, ‘639916389503636’);
fbq(‘track’, ‘PageView’);

Suscribite a nuestro Newsletter

No te enviaremos correo SPAM. Lo odiamos tanto como tú.