Level: Beginners
All of the learning examples in my books Supercharge Power BI and Supercharge Excel are built on an Adventure Works database that has a single data table. But Power Pivot is a very capable tool for building reports that combine multiple data tables. Although it is very capable, the logic of how to combine multiple tables is different in Power Pivot than most Excel users will be familiar with. This post covers the correct way to do it.
The Classic Power Pivot Data Model
As you can see in the image below, the classic Power Pivot data model consists of 2 different types of tables – lookup tables (shown as #1 below) and data table(s) shown as 2.
The difference between these 2 types of tables is very important.
Lookup Tables
The lookup tables (also called dimension tables by BI professionals) always consist of 1 (and only 1) record for every item in the table. Eg.
- In the calendar lookup table, there is 1 row for every day – no duplicates. The uniquely identifying key is the date itself.
- In the customers lookup table, there is 1 row for every customer – no duplicates. The uniquely identifying key is the customer number.
And so on for the other lookup tables.
Data Tables
The data tables (also called fact tables) contain as many records as you like (or as exist in your data) – duplicates are fine. There is a “key” column that matches with each of the lookup tables. Eg there is a Sales[date] column in the sales table that matches to the Calendar[date] column, a Sales[Customer Key] column that matches to the Customers[Customer key] column and so on. Each date can have many sales, each customer can have many sales etc.
Joining the Data Tables to Lookup Tables
Data tables are always joined to the Lookup Tables not the other way around. The easiest way to think about this is to think how you would work with tables like this in Excel. If you had a table of sales by product code in Excel and you wanted to find out the product description, you would probably write a VLOOKUP in your sales table to go and fetch the product description from another table that contained the full list of products. This is one of the reasons I recommend you lay out your tables like I have shown in the image above. I call this the Collie Layout methodology as it was invented by Rob Collie. This layout makes no difference to the way the data model works, but laying it out in this way gives you a visual clue that the tables at the top are lookup tables (ie look up, VLOOKUP).
To join the tables (in the diagram view) you click and drag the common column from the data table to the lookup table (not the other way around). Note if you do it the other way around, Power Pivot will MOSTLY detect the error and flip the relationship for you. But in some instances this will not happen potentially giving you undesired and incorrect results. For this reason I recommend you create the joins the correct way as described above.
Every join in Power Pivot (Excel 2010, 2013) is of the type One to Many – there are no exceptions. The one side of the relationship has the arrow pointing to it, and the many side of the relationship has the circle.
Power BI Desktop/Excel 2016
Several things changed in the latest version of the Power Pivot engine that comes with Power BI Desktop and Excel 2016. In these newer versions, it is also possible to have one to one relationships. Just double click the relationship and then you can change the cardinality (shown as 1 below).
Note you can only change this if your data truly is 1 to 1. You can also force the data tables to filter the lookup tables (shown in 2 above). Finally, there are a number of visualisation improvements including the cardinality of the relationship is easier to understand. The highlighted relationship is currently 1 to many and has a 1 (shown #3 above) and a * (shown as #4 above). You can also now see an arrow (shown as #5) indicating the direction of the automatic filter propagation.
Now for that Second Data Table
When you bring a second data table into Power Pivot, it is common for people to think that they should join the new data table to the original data table, but this is wrong. The correct way to join a second data table to a data model is to treat the new data table exactly the same as the first data table.
- Make sure it has 1 column for every lookup table (lookup tables that are relevant anyway)
- Join the data table columns to each lookup table (that is relevant)
In the following example, I have imported a Stock on Hand (SOH) data table for Adventure Works. This table contains the stock counts completed in each branch at the end of each trading month.
A couple of things to note:
- I have placed the data table at the bottom, just like the other data table
- I have joined each of the “key” columns to the lookup tables. In this case there is a date column, ProductKey, TerritoryKey but no CustomerKey.
How to use the Model Inside Pivot Tables
Once you have your data model set up in this way, it is important that you always use your Lookup Table columns to filter your pivot tables. The 4 tables (highlighted below) are the lookup tables from above. You should only use columns from these tables in your Pivot Table Rows, Columns, Filters and Slicers. The filters always automatically propagate from the lookup tables to the data tables and hence doing it this way will ensure that BOTH of your data tables are filtered. Note of course that there is no join between the customer table and the SOH table.
If you don’t use the lookup tables, but instead you use (for example) the Territory Key from one of the data tables, it will correctly filter that 1 data table but it will not filter the second data table. This is because the natural filter propagation in Power Pivot ALWAYS flows from the 1 side of the relationship to the many side of the relationship. Filters cannot automatically flow in the other direction (it can be done with more advanced DAX, but it is not automatic).
This is the second reason I recommend using the Collie Layout Methodology for your data models. As you can “see” in the image above, the filter propagation always flows “down hill”. Filters do not automatically flow “up hill”. Laying out your tables this way gives you a second visual cue that “filters only flow downhill”.
A Working Pivot Table over Multiple Data Tables
Here is an example of a working pivot table over multiple data tables.
Note that the Calendar Year filter (shown as #1 above) and the row labels (Product Sub Category #2) both come from the lookup tables. The measure [Total Sales] (#3) comes from the Sales data table = SUM(Sales[Extended Amount] while the measure [Total SOH] (#4) comes from the SOH data table. The formula for Total SOH is a bit complex for reasons outside the scope of this post, but here it is in case you are interested.
Total SOH =
CALCULATE(
SUM(SOH[SOH]),
LASTNONBLANK(Calendar[Date], CALCULATE(COUNTROWS(SOH)))
)
Only Create Pivot Tables that make Sense
In this data model, you will recall that there is no join between the SOH data table and the Customer Table. From a business sense this makes perfect sense – the stock is counted at a store level and there is no relevance to customers. However the implication is that it will make no sense to create a pivot table that contains customers in a filter and also SOH in values. If you do this, the SOH numbers will not make any sense. The message is that you should only create pivot tables that make sense based on your data model.
Hi Matt,
if you have multiple data tables containing sales data (for example one table for each customer) and you want to show total sales for all customers (and other KPIs) without appending the data tables in a single one. How would you do that? If possible, I would like to avoid to append tables in order to minimize memory used and processing times.
Thank you!
Giovanni
If you want to minimise memory used and processing times, then you should definitely append the tables. Appending is better than loading 2 tables.
I’m not sure what is missing and I’m not sure what you mean by “duplicate data”. It sounds like you have done it right. Key points are that you should only slice/dice (on rows, slicers) from the lookup table (never from the data tables), and you need a measure or implicit measure from the data tables.
Matt, this post makes sense to me, as it does in your book “Supercharge Excel” which I am enjoying … however, I have a Power Pivot Data Model with multiple (parallel) data tables that is generating duplicate records (employees) when shown in a PivotTable. My three data tables are all joined in a similar way to one lookup table (employee master list) but they are being treated as completely separate data. What am I still missing?
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
Very good explanation, thanks!
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.
A clear and concise explanation of some fundamental requirements for an exiting journey into the realm of Power Pivot/BI.
Thanks for the insight.