Multiple Data Tables in Power Pivot

Hi Matt

So you have an explanation of the Total SOH Measure in another post.
I would be interested in knowing what the second calculate is doing.
Cheers

Chris

Gran articulo te lo traduzco al español.

El modelo clásico de datos en Power Pivot.

Como se puede ver en la siguiente imagen, el modelo de datos clásico de Power Pivot consiste en 2 diferentes tipos de tablas – las tablas de dimensiones (que se muestra como [1] abajo) y la tabla de hechos de datos mostrados como [2].

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

Tablas de dimensiones

Las tablas de dimensiones (también llamadas tablas de medidas por parte de profesionales de BI) consisten siempre en 1 (y sólo 1) registro de cada elemento de la tabla. Por ejemplo.
o En la tabla de búsqueda de calendario, hay 1 fila para cada día – no hay duplicados. La clave de identificación única es la fecha en sí.
o En la tabla de búsqueda de clientes, hay 1 fila para cada cliente – no hay 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 hechos
Las tablas de hechos contienen tantos registros como desees (o como las que existen en los datos) – duplicados o no. Hay una columna “clave” que coincide con cada una de las tablas de dimensiones. Por ejemplo, hay una columna de ventas [fecha] en la tabla de ventas que corresponda a la columna de Calendario [fecha], algún punto de venta [Clave del cliente] columna que coincide con los clientes [clave del cliente] columna y así sucesivamente. Cada día puede tener muchas ventas, cada cliente puede tener muchas ventas, etc.

Unión las tablas de hechos con las tablas de dimensiones
Las tablas de hechos están siempre unidas a las tablas de dimensiones y no al revés.
La forma más fácil de pensar en esto es pensar en cómo le gustaría trabajar con tablas de este tipo en Excel. Si tienes una tabla de ventas por código de producto en Excel y quieres saber la descripción del producto, usarás BUSCARV en la tabla de ventas para ir a buscar a la descripción del producto de otra tabla que contenía la lista completa de los productos. Esta es una de las razones por las que es recomendable usar la configuración de tablas mostrada. Yo llamo a esto la metodología de diseño Collie, ya que fue inventado por Rob Collie. Esta disposición no hace ninguna referencia a la forma en que funciona el modelo de datos, sino que da una pista visual de que las tablas de la parte superior son Tablas de dimensiones.
Para unir las tablas (en la vista de diagrama) se hace clic y se arrastra la columna común de la tabla de hechos hacia la tabla de dimensiones (no al revés). Ten en cuenta que si lo haces al revés, Power Pivot detectará generalmente el error y devolverá la relación hacia a ti. Sin embargo, en algunos casos, esto no va a ocurrir produciendo resultados no deseados e incorrectas. Por esta razón es recomendable crear las uniones de la forma correcta como se ha descrito anteriormente.
Todas las relaciones en Power Pivot son del tipo Uno a Muchos SIN EXCEPCIONES
El lado de UNO de la relación tiene la flecha apuntando hacia él, y el lado MUCHOS de la relación tiene el círculo.

Power BI desktop / Excel 2016
Algunas cosas cambian en la última versión de Power Pivot que viene con Power BI Desktop y Excel 2016.
En estas nuevas versiones, también es posible tener relaciones uno a uno. Basta con hacer doble clic en la relación y entonces se puede cambiar la cardinalidad de la relación (como se muestra a continuación).

Nota: Sólo se puede cambiar esto si la relación entre sus datos es de 1 a 1.
También se puede obligar a las tablas de hechos a filtrar las tablas de dimensiones (como se muestra en el punto 2).
Finalmente, hay una serie de mejoras de visualización incluyendo la cardinalidad de la relación, siendo así ahora más fácil de entender.
La relación resaltada en rojo está actualmente en (1 es a muchos) y tiene un 1 en el [3] y un * en el [4]. Ahora también puede ver una flecha [5] que indica la dirección de la propagación de filtro automático.

Ahora, veremos una segunda tabla de hechos.
Cuando traemos una segunda tabla de hechos en Power Pivot, podríamos pensar que debe unirse la nueva tabla de hechos a la tabla de hechos original, pero esto es incorrecto.
La forma correcta de añadir una segunda tabla de hechos a un modelo de datos es realizar un tratamiento de la nueva tabla de hechos exactamente igual que a la primera tabla de hechos.
o Asegúrate de que tiene 1 columna para cada tabla de dimensiones que sea relevante.
o Une las columnas de las tablas de hechos para cada tabla de dimensiones que sea relevante.

En el siguiente ejemplo, he importado un (SOH) de la tabla de hechos en la mano de Adventure Works.
Esta tabla contiene los recuentos de existencias realizados en cada marca al final de cada mes comercial.

Un par de cosas a tener en cuenta:
o He puesto la tabla de hechos en la parte inferior, al igual que la otra tabla de hechos.
o He unido cada una de las columnas “clave” a las tablas de dimensiones correspondientes. En este caso son columna de fecha, ProductKey, TerritoryKey pero no CustomerKey.

 
Cómo utilizar el Modelo Dentro Tablas dinámicas.
Una vez que haya establecido el modelo de datos de esta manera, es importante que siempre utilice las columnas de búsqueda en la tabla para filtrar las tablas pivote.
Las 4 tablas (resaltadas a continuación) son las tablas de dimensiones de arriba.
Sólo debe utilizar columnas de estas tablas en sus filas de las tablas dinámicas, columnas, filtros y segmentación de datos.
Los filtros siempre se propagan automáticamente a partir de las tablas de dimensiones hacia las tablas de hechos y por lo tanto haciéndolo de esta manera te asegurarás de que ambas tablas de hechos se filtran.
Nota por supuesto no hay unión entre la tabla de clientes y la tabla de SOH.

Si en vez de utilizar la tabla de dimensiones, utilizas la clave de territorio de una de las tablas de hechos, filtraras correctamente la tabla de hechos 1 pero no filtrarás la segunda tabla de hechos.
Esto se debe a que la propagación natural del filtro en Power Pivot fluye siempre desde el lado [1] de la relación hacia el lado [muchos] de la relación. Los filtros no pueden fluir automáticamente en otra dirección (aunque se puede hacer con DAX avanzado).

Esta es la segunda razón por la que recomendamos el uso de la metodología de diseño Collie para sus modelos de datos.
Como se puede “ver” en la imagen de arriba, la propagación del filtro siempre fluye “cuesta abajo”.
Los filtros no fluyen de forma automática “cuesta arriba”.
Este trazado de las tablas da una segunda indicación visual de que “filtros sólo fluyen cuesta abajo”.

Una tabla dinámica sobre múltiples Tablas de hechos
Este es un ejemplo de una tabla dinámica que trabaja a través de múltiples tablas de hechos.

Ten en cuenta que el filtro por año calendario (que se muestra como [1] arriba) y las etiquetas de las filas (Sub Categoría Producto [2]), vienen de las tablas de dimensiones.
La medida [Total de ventas] ([3]) proviene de los datos de la tabla de ventas = SUM (Sales [Extended Amount] mientras que la medida [Total SOH] (#4]) viene de la tabla de hechos SOH. La fórmula para Total SOH es un poco compleja por razones fuera del alcance de este artículo, pero aquí está por si tienes interés.
Total SOH =
CALCULATE (
SUM ( SOH[SOH] ),
LASTNONBLANK ( Calendar[Date], CALCULATE ( COUNTROWS ( SOH ) ) )
)

Crea solo tablas dinámicas que tengan sentido
De este modelo de datos, recuerda que no hay combinación entre la tabla de hechos SOH y la tabla de clientes.
Desde el punto de vista de los negocios esto tiene sentido – la acción se encuentra en un nivel de tienda y no hay ninguna relevancia a los clientes.
Sin embargo, esta implicación no tendrá sentido si creas una tabla dinámica que contenga los clientes en un filtro y también SOH en los valores. Si haces esto, los números de SOH no tienen ningún sentido.
El mensaje es que sólo se deben crear tablas dinámicas que sentido basándose en el modelo de datos.