Guest Post From Dany Hoter
Intro from Rob: Ah, the international man of mystery returns! My first instincts when I think of Dany Hoter, other than “one of the most fabulous humans I have ever known,” generally can be summarized as “MDX and Cube Formulas Monster.”
But he’s far from a one-trick pony. Generally speaking, he has a level of tenacity and patience rarely encountered outside of laboratory conditions. Couple that with an insatiable drive for The Right Thing, and you get some crazy results.
Today is one such CRAZY example. Simultaneously, he shows us how to compensate for a drillthrough bug, AND delivers a working example of relationship manipulation via VBA macros.
THIS IS AN ADVANCED TOPIC POST. Feel free to skip this one. This is the deep end of the pool and even I don’t swim in these particular waters yet.
Note of course that this technique is 2013 only, and will not work with Power Pivot in 2010.
Take it away, Dany…
A Drillthrough Bug with Inactive Relationships
I started this VBA project after one of our partners wrote to me about a customer complaint regarding inactive relationships in Power Pivot:
The Sales Table has TWO Relationships to Calendar – One is Based on OrderDate (Active), and the Other is Based on ShipDate (Inactive – Dashed Line)
The scenario was the following: There are multiple relations between two tables, as pictured above. One is of course active while the others (one or more) are inactive.
Everything was working great here until the user double clicked a number in the pivot expecting to see a new sheet with the rows from the table behind the value that are part of the filter context and should sum up to the shown number (or at least explain the value).
The problem was that the rows shown in the new sheet were not the right ones.
It seemed that the drillthrough operation doesn’t follow the active the active relationship but uses another relationship.
This happens to be a known bug in the drillthrough operation and we should expect to see a fix in a future version.
In the meantime I wanted to understand the problem fully and supply a workaround if possible.
What I’ve found is that drillthrough uses the FIRST relationship instead of the active relationship. First means the relationships that comes first in the collection of relationships for the model.
The solution I came with was to reorder the relationships so the active one is also going to be first.
In order to do that I wrote some VBA code that you can see in the workbook linked from this article relationship.xlsm.
The VBA code can also be used to learn how to manipulate the relationships from code.
VBA Macros in 2013 Power Pivot
In Excel 2013 there is a new property of the application called Model. This new object includes collections for the tables and for the relationships. You will not find anything about columns or measures in the tables.
The rationale behind what is accessible thru the object model is that everything that you can do from the native Excel UI can be done from the OM. This includes manipulating tables and manipulating relationships.
The method I used is to create a little report in a new table in Excel with all the information about the relationships in the model.
I sort the table by the foreign key table and ID and I check to see cases in which there are multiple relationships and the active relationship is not the first one.
When I find such a combination, I delete all relationships between the same tables and rebuild them in the “right” order with the active relationship created first.
You probably want to insert the VBA sub into your personal.xlsb file and maybe create a button for it in your ribbon together with other useful tools.
