Troubleshooting PowerPivot Relationships

So you are working with multiple tables with PowerPivot, press the create relationship button and then you get the dreaded “No relationships detected”:

What can you do ? Here are some tips to troubleshoot you relationships:

  • First thing to check is that you have 2 columns that can be related, columns like a productcode or Orderdate. Make sure your key is unique for each row in the table.
  • A relationship is always between a table and a lookup table. Think of a lookup table as a dimension table. A lookup table has to contain unique values with a unique key (like a Primary Key in a database). When you want to join multiple tables always use a lookup table in between.
  • Make sure your columns have identical  (or similar) names, PowerPivot uses the name to automaticallyrelate columns
  • When you have multiple columns that make a key you should create a new column, use CONCATENATE or & to create a composite column, and that will serve as your key.
  • You want to create a relationship between two tables that doesn’t contain a unique key:  
    You can createa lookup table by copying the keys to a Excel sheet and use the Excel remove duplicates function to create a unique table, use create linked table to load te data to PowerPivot. Then use this table as intermediate table.
  • When relationship detection is to no avail, you can try to add a relationship by hand in the PowerPivot window. Some relationship will not be detected automaticly although they are valid (like Boolean, datetime or currency type colums)
  • You can only create one relationship at the time between two tables, like the adv works FactInternetSales: OrderDate, DueDate, and ShipDate that joins with Dimdate. To be able to do this in PowerPivot create multiple copies of the Dimdate table.
  • I want a Many-to-Many relationships in PowerPivot, Marco Russo created a blog post describing this in detail.
  • You cannot create a realtionship loop, for example Table1 -> Table2. Table2 -> Table3. A relationship between Table1 -> Table3 is not possible.

I hope this post will help you solve your relationship problem.