VBA to retrieve data model info

[macfuller]

As our organization slowly moves toward the PowerPivot world, I'm starting to get other people's spreadsheets with data models and being asked to help with DAX and relationship issues.

When I receive a spreadsheet I want a tool that tells me everything about the data model: queries, relationships, calculated columns, measures, etc. I use the PP Utilities add-in and recommend it highly to everyone, but I need more info than the Excel 2013 queries in the tool can provide. Now that our organization is moving to 2016 I can use the data model to enumerate model elements, but there's scant information about how to get everything.

One seemingly minor model element I'd like to know is the Sort By Column setting. For example, when I receive someone else's date tables I want to know if they've sorted the text day of the week column (if they have one) by a numeric day of week column or if I need to add one.

The MSDN resources on this are pretty meager and definitely lacking in examples. If anyone can point me to a helpful resource on this I would greatly appreciate it!

 

[Matt Allington]

I vaguely remember Marco Russo telling that the sort by column feature I. Both PP and PBI are not what they seem. The PP on (from memory) is an Excel UI trick rather than part of the data model. My guess is you won’t be able to extract it, but I can’t be sure.

 

[macfuller]

I guess the thing to do is create a simple model of 2 tables with a relationship and use DAX Studio to dump the various DMV tables to see what can be gleaned. I've tried bits and pieces of that approach in the past but the various numeric values in the columns are hard to decipher without a roadmap. That assumes of course that the DMV tables are the sum total of PP knowledge, as you warn they may not be.

I'm not sure how it is in your practice, but for me as the models I receive get further from data that I'm familiar with it takes much longer to figure out what the intent of the model is. Are the measures trying to expand on the existing data or overcome its limitations? Is the data presented in a certain way because its sequence derives from something in the data or because the users are accustomed to seeing it that way?