While trying to set up VertiPaq Analyzer on a new computer, I ran into a problem where Excel was not letting me change the SSAS connection that was built in the workbook. It turns out I had missed one of steps in the instructions in the workbook. As a result, when I got to Connection Properties, everything was grayed out and this message was at the bottom:

Some properties cannot be changed because this connection was modified using PowerPivot Add-in.

The instructions in the workbook said:

Open the “Data” ribbon

Click “Manage Data Model”

Click “Existing Connections”

….

(I was working with Veritpaq Analyzer v1.9 downloaded from https://www.sqlbi.com/articles/data-model-size-with-vertipaq-analyzer/)

The wrong way

Working in Excel with the latest version of “Microsoft Office 365 Pro Plus”, the “Manage Data Model” option was not available under the Data ribbon so instead I went to Existing Connections in this ribbon. Notice the message at the bottom:

The right way

Interestingly, I already had Power Pivot “Ribbon” visible so I knew it was installed already. I clicked on Power Pivot ribbon and then on Manage. From there I went to “Get External Data” to “Existing Connections”

This time clicking on Existing Connections allowed me to modify the connection:

After I built the connection I clicked on Refresh and voila! I was connected to my sample AdventureWorks database.

Some notes about VertiPaq Analyzer

What I love about this tool is that it is very easy to get it setup (minus the minor issue I ran into). I love how I can quickly I can look at a database and see how much space each table/column is taking and the database itself ad since all of this is in Excel you can easily sort, filter and find what you are looking for.  My other favorite tab is the measures tab where it lists all the measures.

It is true that you can get all this information from the SSAS DMVs (dynamic management views) but this workbook has everything in it without you having to write queries in DMX language which is not quite like SQL and very limited in what you can put in a where clause or when writing joins.