…brew your own Business Intelligence

Navigating Complex Tabular Models Quickly – Developer Edition

As a developer, working with large/complex tabular models – “large” in terms of the number of tables, columns, and/or measures (not data size) – can be frustrating and borderline soul-crushing. Allow me to paint a picture for you…

You’ve just inherited a large tabular model along with a large backlog of “adjustments/tweaks”… rename these columns, adjust the formatting of these measures, etc. You’re thinking “no big deal boss – changes like these are trivial – I got this!”… and then you open the model up and realize it has over 50 tables…several of which have upwards of 50+ columns, and well over 200 measures. Ugh – gross.

The problem is that it’s doing to take more time (a lot more time) to find the target object that it will to actually make the change.

This issue was addressed quite nicely with the recent release of the Tabular Model Explorer – a feature that organizes the major objects (e.g. tables, columns, relationships, measures, etc) into a tree explorer allowing you to quickly find what you’re looking for.

image

Unfortunately, the Tabular Model Explorer is only available for 2016 (compatibility 1200) tabular models – which many folks haven’t moved over to just yet (despite the overwhelming list of reasons why SQL 2016 is one of the best releases in a very long time).

Those of us stuck with 2012/2014 environments have no other option than to comb through the diagram view for that one table we’re looking for…or scan the unordered list of tables/columns in grid view, or arrow-key through a bunch of cells in the calculation pain pane to find a particular measure… or so I thought up until a few weeks ago when I discovered a better way!

Properties Window To The Rescue!

Turns out the property window can be used for more than just editing object properties… it can also help developers navigate a complex tabular model very quickly and painlessly. Here’s how…

Need to find a specific table?

  1. Open the Properties Window (shortcut key: F4)
  2. Click any table in the diagram view (or tab in the grid view)
  3. Use the dropdown to scroll through an alphabetized list of tables
  4. Choose the table you need to adjust

image

 

Need to find a specific column?

  1. Open the Properties Window (shortcut key: F4)
  2. Click any column in the diagram view (or grid view)
  3. Use the dropdown to scroll through an alphabetized list of columns for the specified table
  4. Choose the column you need to adjust

image

Need to find a specific measure?

  1. Open the Properties Window (shortcut key: F4)
  2. Click any measure in the diagram view (or grid view)
  3. Use the dropdown to scroll through an alphabetized list of measures for the entire model
  4. Choose the measure you need to adjust

image

Wrapping Up

And there you have it folks – the simple secret to quickly and efficiently navigating large tabular models lies with the Properties window.

If this helped you be sure to leave a comment letting me know what you’re now doing with all the free time you’ve just recovered…

One thought on “Navigating Complex Tabular Models Quickly – Developer Edition

Leave a Reply