Inconsistent drillthrough behavior in Power Pivot and SSAS Tabular


When you define more than one relationship between two tables, drillthroughs may return unexpected results.

Get the file!

The problem

If two or more relationships exist between two tables, knowing which relationship is active is not enough to determine the drillthrough behavior of your model.

As a consequence, if you change the active relationship in your model, you may observe unexpected drillthrough behaviors.

Consider the following diagrams, based on the same model.

This is a simple model with two tables. There are two relationships in this model, both linking the table Data to the table Dim: one using the column Rel1, the other one using the column Rel2.

We will call these relationships Rel1 and Rel2 respectively. As you can see from the diagrams, Rel1 is active in both cases.

diagram1

diagram2

In the first case, however, drillthrough will be based on Rel1. In the second case, it will be based on Rel2.

It appears drillthrough occurs according to the bottommost relationship in the Power Pivot diagram view (this is the other way around in Visual Studio, if I recall correctly).

Setup

Here are the data in both tables.

Dim
Click here –>
Rel1 Rel2 Comment
Click here –> Drillthrough based on Rel1
Click here –> Drillthrough based on Rel2

We can create a simple pivot table like the following.

pivot_table

Just after we created the relationships, double-clicking on the cell will create a new sheet returning the following results.

[$Data].[Rel2] [$Data].[Comment]
Drillthrough is based on Rel1

The Comment column makes it easy to spot which relationship was used for the drillthrough: Rel1.

This works as expected.

Changing the active relationship in the diagram view

Let us activate Rel2 in the diagram view, using the following steps:

  1. De-activate Rel1
  2. Double-click on Rel2, toggle the Active checkbox
  3. Press OK

Take a look at the result. The active relationship is now the topmost relationship in the diagram view.

Check the drillthrough sheets. Despite the update, the results stay the same.

Let us activate Rel1 again (using the same steps as before), and check the result. The drillthrough was done according to Rel2!

$Data].[Rel2] [$Data].[Comment]
Drillthrough is based on Rel2

Let us activate Rel2 again: drillthrough was done according to Rel1.

$Data].[Rel2] [$Data].[Comment]
Drillthrough is based on Rel1

You can ad lib this.

Note that if you activate a relationship using the following steps:

  1. Right-click the relationship
  2. Mark as active

Its position in the diagram view will not change.

As a corollary, it will also have no impact on the drillthrough behavior.

Workaround

The easiest way to change the active relationship in a model while maintaining a consistent drillthrough behavior is to

  1. delete the relationships
  2. recreate them, starting with the (new) active one.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: