Varias tablas de datos en Power Pivot

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

Contenidos

Nivel: principiantes

Todos los ejemplos de aprendizaje en mis libros. Supercarga Power BI y Supercarga Excel se basan en una base de datos de Adventure Works que tiene una única tabla de datos. Pero Power Pivot es un herramienta muy capaz para crear informes que combinan varias tablas de datos. Aunque es muy capaz, la lógica de cómo combinar varias tablas es diferente en Power Pivot de lo que la mayoría de los usuarios de Excel estarán familiarizados. Esta publicación cubre la forma correcta de hacerlo.

El modelo de datos clásico de Power Pivot

Como puede ver en la imagen a continuación, el modelo de datos clásico de Power Pivot consta de 2 tipos diferentes de tablas: tablas de búsqueda (mostradas como # 1 a continuación) y tablas de datos mostradas como 2.

image_thumb-1310171

La diferencia entre estos 2 tipos de tablas es muy importante.

Tablas de búsqueda

Las tablas de búsqueda (también llamadas tablas de dimensiones por los profesionales de BI) siempre constan de 1 (y solo 1) registro para cada elemento de la tabla. P.ej.

  • En la tabla de búsqueda de calendario, hay 1 fila para cada día, sin duplicados. La clave de identificación única es la fecha en sí.
  • En la tabla de búsqueda de clientes, hay 1 fila para cada cliente, sin duplicados. La clave de identificación única es el número de cliente.

Y así sucesivamente para las otras tablas de búsqueda.

Tablas de datos

Las tablas de datos (también llamadas tablas de hechos) contienen tantos registros como desee (o como existan en sus datos): los duplicados están bien. Hay una columna de «clave» que coincide con cada una de las tablas de búsqueda. Por ejemplo, hay una venta[date] columna de la tabla de ventas que coincide con el calendario[date] columna, a Ventas[Customer Key] columna que coincide con los clientes[Customer key] columna y así sucesivamente. Cada fecha puede tener muchas ventas, cada cliente puede tener muchas ventas, etc..

Uniendo las tablas de datos a las tablas de búsqueda

Las tablas de datos siempre se unen a las tablas de búsqueda y no al revés. La forma más fácil de pensar en esto es pensar cómo trabajaría con tablas como esta en Excel. Si tuviera una tabla de ventas por código de producto en Excel y quisiera averiguar la descripción del producto, probablemente escribiría una VLOOKUP en su tabla de ventas para ir a buscar la descripción del producto de otra tabla que contenga la lista completa de productos. Esta es una de las razones Te recomiendo que coloques tus tablas como te he mostrado en la imagen de arriba.. Yo llamo a esto el Metodología Collie Layout como fue inventado por Rob Collie. Este diseño no hace ninguna diferencia en la forma en que funciona el modelo de datos, pero exponiéndolo de esta manera le da una pista visual de que las tablas en la parte superior son tablas de búsqueda (es decir, busque, BUSCARV).

Para unirse a las mesas (en la vista de diagrama) haga clic y arrastre la columna común de la tabla de datos a la tabla de búsqueda (no al revés). Tenga en cuenta que si lo hace al revés, Power Pivot PRINCIPALMENTE detectará el error y cambiará la relación por usted. Pero en algunos casos, esto no sucederá potencialmente y le dará resultados no deseados e incorrectos. Por esta razón, le recomiendo que cree las uniones de la manera correcta como se describe arriba.

Todas las uniones en Power Pivot (Excel 2010, 2013) son del tipo Uno a muchos; no hay excepciones.. El un lado de la relación tiene la flecha apuntando hacia él, y el lado múltiple de la relación tiene el círculo.

Power BI Desktop / Excel 2016

Varias cosas cambiaron en la última versión del motor Power Pivot que viene con Power BI Desktop y Excel 2016. En estas versiones más nuevas, también es posible tener relaciones uno a uno. Simplemente haga doble clic en la relación y luego podrá cambiar la cardinalidad (que se muestra como 1 a continuación).

image_thumb-1-4519097

Tenga en cuenta que solo puede cambiar esto si sus datos realmente son 1 a 1. También puede forzar que las tablas de datos filtren las tablas de búsqueda (que se muestran en 2 arriba). Finalmente, hay una serie de mejoras de visualización, incluida la cardinalidad de la relación que es más fácil de entender. La relación resaltada es actualmente de 1 a muchos y tiene un 1 (mostrado el # 3 arriba) y un * (mostrado como el # 4 arriba). Ahora también puede ver una flecha (mostrada como # 5) que indica la dirección de la propagación automática del filtro.

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

Ahora para esa segunda tabla de datos

Cuando trae una segunda tabla de datos a Power Pivot, es común que las personas piensen que deberían unir la nueva tabla de datos a la tabla de datos original, pero esto es incorrecto. La forma correcta de unir una segunda tabla de datos a un modelo de datos es tratar la nueva tabla de datos exactamente igual que la primera tabla de datos..

  • Asegúrese de que tenga 1 columna para cada tabla de búsqueda (tablas de búsqueda que son relevantes de todos modos)
  • Une las columnas de la tabla de datos a cada tabla de búsqueda (que sea relevante)

En el siguiente ejemplo, he importado una tabla de datos Stock on Hand (SOH) para Adventure Works. Esta tabla contiene los recuentos de acciones completados en cada sucursal al final de cada mes de negociación.

image_thumb-2-6435879

Un par de cosas a anotar:

  • He colocado la tabla de datos en la parte inferior, al igual que la otra tabla de datos.
  • He unido cada una de las columnas «clave» a las tablas de búsqueda. En este caso hay una columna de fecha, ProductKey, TerritoryKey pero no CustomerKey.

Cómo utilizar las tablas dinámicas internas del modelo

Una vez que haya configurado su modelo de datos de esta manera, es importante que siempre use las columnas de la tabla de búsqueda para filtrar las tablas dinámicas. Las 4 tablas (resaltadas a continuación) son las tablas de búsqueda de arriba. Solo debe usar columnas de estas tablas en las filas, columnas, filtros y segmentaciones de la tabla dinámica. Los filtros siempre se propagan automáticamente desde las tablas de búsqueda a las tablas de datos y, por lo tanto, si lo hace de esta manera, se asegurará de que AMBAS tablas de datos se filtren. Tenga en cuenta, por supuesto, que no hay unión entre la tabla de clientes y la tabla SOH.

image_thumb-3-5847483

Si no usa las tablas de búsqueda, sino que usa (por ejemplo) la clave de territorio de una de las tablas de datos, filtrará correctamente esa 1 tabla de datos pero no filtrará la segunda tabla de datos. Esto es porque la propagación del filtro natural en Power Pivot SIEMPRE fluye desde el lado 1 de la relación al lado múltiple de la relación. Los filtros no pueden fluir automáticamente en la otra dirección (se puede hacer con DAX más avanzado, pero no es automático).

image_thumb-4-5537744

Esta es la segunda razón por la que recomiendo utilizar la metodología de diseño de Collie para sus modelos de datos. Como puede «ver» en la imagen de arriba, tLa propagación del filtro siempre fluye «cuesta abajo». Los filtros no fluyen automáticamente «cuesta arriba». La disposición de las tablas de esta manera le brinda una segunda señal visual de que “los filtros solo fluyen cuesta abajo”.

Una tabla dinámica de trabajo sobre varias tablas de datos

A continuación, se muestra un ejemplo de una tabla dinámica de trabajo sobre varias tablas de datos.

image_thumb-5-4501920

Tenga en cuenta que el filtro de año calendario (que se muestra como el n. ° 1 arriba) y las etiquetas de fila (subcategoría de producto n. ° 2) provienen de las tablas de búsqueda. La medida [Total Sales] (# 3) proviene de la tabla de datos de Ventas = SUM (Ventas[Extended Amount] mientras la medida [Total SOH] (# 4) proviene de la tabla de datos SOH. La fórmula para Total SOH es un poco compleja por razones fuera del alcance de esta publicación, pero aquí está en caso de que esté interesado.

Total SOH =
CALCULATE(
    SUM(SOH[SOH]),
    LASTNONBLANK(Calendar[Date], CALCULATE(COUNTROWS(SOH)))
)

Solo cree tablas dinámicas que tengan sentido

En este modelo de datos, recordará que no existe una unión entre la tabla de datos SOH y la tabla de clientes. Desde el punto de vista comercial, esto tiene mucho sentido: el stock se cuenta a nivel de tienda y no es relevante para los clientes. Sin embargo, la implicación es que no tendrá sentido crear una tabla dinámica que contenga clientes en un filtro y también SOH en valores. Si hace esto, los números de SOH no tendrán ningún sentido. El mensaje es que solo debe crear tablas dinámicas que tengan sentido según su modelo de datos.

!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ú.

error: Alert: Content is protected !!