Level: Beginners
Power Pivot is a database that has been designed from the ground up to be optimised for reporting and analysis in Power BI, Power Pivot for Excel and SSAS Tabular. The technology is essentially the same across all of these products so I will generically refer to Power Pivot in this article.
Power Pivot uses an in memory columnar database (Vertipaq) as the foundation technology – the same technology for all versions (Power Pivot, Power BI and SSAS Tabular). The Vertipaq engine is what makes Power Pivot both super fast and highly compressed. A Power Pivot database is not the same as a relational database (RDB) and it does not support all the relationship types that relational databases support. This can be confusing for people that are new to Power Pivot, particularly if they have at least a basic understanding of how databases (such as MS Access) work. I explain what you need to know to get started with relationships in Power Pivot here.
Virtual Relationships
This article is specifically about physical relationships, however there are ways to create virtual relationships using DAX. Examples include using LOOKUPVALUE, FILTER, CROSSFILTER and other techniques. I am not covering these types of relationships in this article.
Relationship Cardinality Types
There are 3 different possible physical relationship cardinality types in database design but not all are supported by Power Pivot.
| Relationship Cardinality Type | Power BI Support | Power Pivot for Excel Support |
|---|---|---|
| One to Many | Yes | Yes |
| One to One | Yes | No |
| Many to Many | No | No |
One to Many Relationships
The one to many relationship is the foundation of Power Pivot. In the example above (from Adventure Works in Power BI Desktop), the Customers table is on the 1 side of the relationship and the Sales table is on the many side of the relationship. These tables are joined using a common field/column called “CustomerKey”. Customer Key (aka customer number) is a code that uniquely identifies each customer. There can be no duplicates of the customer key in the customer table. Conversely the customer can purchase as many times as needed and hence the customer key can appear in the Sales table as many times as necessary. This is where the name “one to many” comes from – the customer key occurs once and only once in the Customers table but can appear many times in the Sales table.
Tables on the one side of the relationship are called Dimension tables (I call them Lookup tables) and the tables on the many side of the relationship are called Fact tables (I call them Data tables).
The entire Power Pivot Vertipaq engine is optimised to work with this (one to many) type of relationship.
One to One Relationships
The One to One relationship is only supported in Power BI and the newest version of SSAS Tabular. In my view this relationship type has limited value and in most cases it is better to combine these tables into a single flat table prior to loading to Power BI. Consider the model below.
The first relationship (shown as 1) is a 1 to many relationship between the Customer table (Lookup table) and the Sales table (Data table). The Customer Socio Economic Data table is joined to the Customer table via a 1 to 1 relationship (shown as 2 above). If there is a benefit (to the user of reports) of splitting this Socio Economic data into a separate table then of course you should do so. If there is no benefit, I recommend you combine all the data from Customer Socio Economic Data table into the Customer table using Power Query on load.
Every relationship has a “cost” in that it will have some affect on performance. The performance impact may not be noticeable for simple models but may become an issue with very complex models.
If you only remember 1 thing from this article, then please let it be this: Don’t automatically accept the table structure coming from your source data. You are now a data modeller and you need to make decisions on the best way to load your data. Your source system is probably not optimised for reporting (unless it is a reporting datamart) so please don’t assume that what you have got is what you need.
Many to Many Relationships
The many to many relationship type is not supported in Power Pivot. This is a deliberate design decision that has been made as a trade off to ensure optimum database performance. If you have data that is logically related with a many to many cardinality, there are modelling techniques you can use to solve the problem in Power Pivot that are covered in my many to many pattern article here.
UI Differences
There are a few differences in the Power BI Desktop/Excel 2016 UI (relationship view) and that of Excel 2010/2013.
Excel 2010/2013
The early UI has an arrow pointing to the lookup table (the one side of the relationship) and a dot on the many side. This is unfortunate as the arrow is pointing in the opposite direction of the filter propagation. This only exists in Excel 2010/2013 (and the early version of SSAS Tabular).
Power BI/Excel 2016
The UI has been significantly improved with Power BI Desktop and Excel 2016. As you can see below, the 1 to many relationship is now clearly shown, and there is also a new arrow showing the automatic filter propagation direction.
One Active Relationship
It is possible to have more than 1 relationship between tables in Power Pivot, but only 1 can be active at a time. An example of when you may want multiple relationships is if you have a Sales[Order Date] and a Sales[Ship Date] in your data table.
In this scenario (shown above in Excel 2013) you may want to join both Sales Date columns to your Calendar table so you can use time intelligence in your data model on both Order Date and Ship Date.
The active relationship is shown as a solid line (above) and the inactive relationship is shown as a dashed line (in this case it is highlighted in blue above). The active relationship is used by default in all DAX measures however you can over-ride this default and use the inactive relationship (when needed) by using the USERELATIONSHIP() function. Full details on this are covered in my article here.
Cross Filtering Behaviour
Power Pivot is optimised to work with one to many relationships and to automatically propagate filters (filter context) from the one side to the many side. In all versions of Power Pivot for Excel, this is the ONLY type of filter propagation that is available.
Power BI supports bi-directional cross filtering behaviour (shown right below) as well as single direction (shown left below).
In fact bi-directional filtering is the default behaviour for Power BI Desktop. There are many people (including me) that think this is a bad idea as bi-directional cross filtering comes at a cost – there is an overhead of constantly cross filtering the lookup table based on the contents of the data table at time when it is actually not required. Sure if you have a simple model and you need this behaviour and you don’t know how to handled it with the many to many pattern then turn it on. But surely this should not be turned on by default. In addition, if you have more than 1 data table, bi-directional cross filtering can cause circular references causing further confusion to unsuspecting users.
I think Microsoft is trying to make Power BI more user friendly for the “lay user” however in this case I think Microsoft has made a mistake. Do yourself a favour and turn off bi-directional cross filtering unless you explicitly need it. To change it just double click on the arrow and set the cross filter direction to single.
update: Last week 17th Feb 2017 I noted that new models I built were single directional by default – seems Microsoft has listened and changed the default behaviour.
Wrap Up
Hopefully this article has helped you understand more about how Power Pivot works. Let me know in the comments below if there is anything I have missed.
Hi Al, you are indeed well on the way to becoming a DAX Ninja – I can tell by this excellent question. The reason this works as it does is due to the more complex topic of expanded tables. There is actually no such thing as filter propagation under the hood, but you can consider it to be that way based on normal usage. Under the hood what is actually happening is ‘expanded tables’. I explain this topic at the bottom of my article about many to many relationships here. https://exceleratorbi.com.au/many-many-relationships-dax-explained/ Have a read and then let me know if it then makes sense.
Hi Matt,
A question on filter propagation.
I have the following calculated column on the Sales table from the AdventureWorks DB:
Sales[Test1]=CALCULATE(COUNTROWS(‘Calendar’))
There is a 1-to-many relationship with the ‘Calendar’ table, the many on the Sales table. The result is a 1 in every row.
I would however expect the result in every row to be the number of rows of the ‘Calendar’ table: 2556. I am forcing context transition with the CALCULATE but since the filter originating from the context transition in the Sales table does NOT propagate “uphill” (from the many to the 1 side of the relationship), ‘Calendar’ should not be filtered at all. Can you please explain what is going on?
Many thanks
Amazing explanation and technical thoughts, Matt!
I too did not understand the need for a One-To-One relationship until I needed one. I created a “Customer Attributes” Table on an Excel sheet and linked it to Power Pivot. It has a One-To-One relationship to the “Customers” table in my data model based on Customer Number. The attributes table has columns for fields I added because they do not exist anywhere else in my SQL tables. This gives me a very easy way to increase reporting capabilities in a way that most any user will be able to maintain the data going forward.
Sort of related to this, as someone that has used Access for years as my backend and Excel as the front end reporting tool via Pivot Tables or just Tables with functions written to get necessary data, the think that I really struggle with in PowerPivot is the inability to use Left/Right joins in relationships, so if an item does not appear in the Item Master table, then I can force it to not show up in my report even if the data table has it.
You’re right Ed, this can’t be done in Power Pivot. But it can be done in Power BI/Power Query. Merge any 2 tables together on a key and you have the full set of single join types including left outer, right outer, full outer, inner etc.
Yes. I’ve done that, but I am not sure how efficient it is. I had a table with over 500,000 records in it from SQL Server with lots of calcs and mods added, then a master file with under 2,000 records. Doing the Inner Join just to get the 500,000 record table to be what was in the master table doubled the processing time as the merge had to recalc. I did it from a duplicate though. Maybe from a Reference? Still learning!
Hi Ed and Matt,
Ed I’m not sure that I fully understand the problem your are describing when you write:
“so if an item does not appear in the Item Master table, then I can force it to not show up in my report even if the data table has it.”
If you mean that you want to suppress the showing of the “unknown member” that is represented by a blank on one of the pivottable axis there is a simple but perhaps not so well known remedy for this. You just have to insert either DISTINCT() or ALLNOBLANKROW() into the filter context.
Here is a simple example. You want to show sales for countries. That would be a simple measure like this (where the table with country names is represented on either of the pivottable axis):
Sum(sales[amount)
Suppressing the unknown member:
Calculate(sum(sales[amount]), distinct(countries[countryname]))
Alternatively:
Calculate(sum(sales[amount]), allnoblankrow(countries[countryname]))
Excel or rather the NON EMPTY feature of the backend (Analysis server backend in Excel) will remove the lines with all blank values because the measures above will result in having a filter context with an empty-set element and one single empty-set filter element will make the entire measure fail with a blank as result and blanks are not returned by the backend.
Again I’m not sure that is what you meant and I’m sorry for interrupting if this is not what you intended.
Ed, doesn’t NATURALLEFTOUTERJOIN accomplish this? Although in the context of measure evaluation in an analysis model, I struggle to see an instance where you would want to do this. The new join functions are designed around calculated tables primarily.
Thank you for a great article as usual!
However, I did not get a clear chime as to when I would use Power Pivot rather than Power BI and vice versa?
Thank you,
Tamir
I never really intended this article to be about when to use one or the other. Relationships as similar in both, but not identical so I needed to cover both. But that does sound like a good blog post for another time – I will add it to my list :-).
Thanks Matt, this one somewhat non-technical and could be gulped down at one go.
Thanks for the excellent blog, Matt!
As usual, it is very clear and understandable.
Very clear and complete, as usual!
Thanks Matt.
Single Directional relationship by default is definitely a proof that Microsoft pays attention to the community – much better now !