Manipulating Relationships in VBA (in 2013)

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:

image

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.

Download the Macro Workbook and Example

2 Responses to Manipulating Relationships in VBA (in 2013)

  1. Matt Allington says:

    Great post Danny – thanks for sharing. I wasn’t aware that VBA had the data model as part of the object model in Excel 2013 (my version). I think I saw some material related to Excel 2010 that explicitly called out that there was no VBA integration, and hence I just had this burned in my head. I don’t have any use cases myself for this atm, but I will keep your workbook example for when I need it in the future.

  2. This is earth shattering, this is front page news! (Have I given myself away as a complete nerd now? :-) Thanks Dany for the post. What I am surprised is I learnt about the new Object Model not from CNN (okay the Power BI official blog perhaps) but from this guest post. I will try out the object model the first chance I get, but perhaps it’s still work in progress and that is why hasn’t been publicized.

    To add to this statement:

    “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.”

    Looking at the Excel 2013 Object Model: http://msdn.microsoft.com/en-us/library/office/ff194068(v=office.15).aspx
    There are other objects which you may be able to use to pull information on Tables, Columns and Measures: ModelTables, ModelTableColumns, ModelMeasureNames.

    As for the object model, it does not seem to be at the aspirational point that Dany puts forth:
    “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.”
    But I am happy as long as things do move in that direction.

Leave a Comment or Question