Developing Business Intelligence Apps for SharePoint

Chapter 16. Enriching the Cube: Relationships and DAX

Relationships in PowerPivot

A relationship in PowerPivot is a connection between two tables that tells Analysis Services how the data between the tables should be correlated. When you import data from a relational database such as SQL Server, PowerPivot uses the foreign key information to infer the relationships between the tables.

Other times, such as when combining data from multiple sources, you need to manually provide the relationship information. Relationships are one of the features that make PowerPivot so powerful as they allow us to mash up data from many unrelated systems and analyze across these systems as long as the data has a definable relationship.

In the SQL Server 2012 release of PowerPivot, it is not possible to work with multiple relationships on a given table. In past releases, only one “active” relationship between tables could be used. The active relationship is shown as a solid line and the inactive relationships are shown as dotted lines in Figure 16-1.

You will notice that there are two relationships between Tickets and People in this example. One relationship is for the requestor of the ticket and one is for the person to whom the ticket is assigned. In PowerPivot version 1, it was only possible to traverse a single relationship between tables. In this release, an additional DAX function was added to allow use of the inactive relationships for calculations.

=CALCULATE([Sum of Measure], USERELATIONSHIP(DimDate[DateKey], ...