Quirks of Visual Studio Query Editor working with Analysis Services


Visual Studio 2017 and the SQL Server Data Tool (SSDT), a tool that comes with Visual Studio, finally integrates the now familiar Query Editor (Power Query) capability to work with the Analysis Services Tabular Model. Query Editor is a data ingestion and transformation tool used by Excel Power Pivot as well as Power BI. Now, there are some differences when working on Query Editor within Visual Studio and Power BI / Power Pivot, however, and I’d like to discuss the biggest difference I saw:

All of your queries do not show up in the Query Editor! Let me explain what that means:

As an example, below image shows 5 tables in a model.

If you want to get back to Query Editor within Visual Studio, the only way that I have found so far is to

  1. Get to the Table Properties screen

  1. Click on the Design button, which will launch the Query Editor screen

The Query Editor screen seems peculiar, however. If you are used to Power BI or Power Pivot, you would expect all your tables to show up, but you will only see the single table that you requested the Table Properties for. See that big empty space. Where did all my tables go?

After posting this “issue” on Microsoft’s Yammer site, we got an answer basically saying – it is what it is, and I should use Expressions. For those of you not familiar with what an Expression is, a (Shared) Expression is encapsulation of “complex or frequently used logic through parameters, functions, or queries “. [1] The expressions show up in the folder structure of your Analysis Services model.

The natural question is, then, how do you create an Expression? An option to “Create an Expression” does not exist so you start out by creating a table.

  1. Import a new table.

  1. From the Query Editor window, ensure that a new table does not get created with the newly imported table (I know. I know.) by unchecking the Create New Table option.

  1. Close & Apply out of Query Editor

Now, you will see a new “shared” Expression ready to be used.

If you were to open the Query Editor screen for another table in the Tables folder, you will not see any other tables, but you will see the new (shared) Expression. With this, you can merge, append, or do whatever else you’d like.

With all this in mind, I have started a new way of creating the tables. I am NOT saying, this is the right way, but this has reduced the pain of not being able to see all the tables.

  1. I create ALL tables as Expressions first.
  2. I create the tables, the ones that will show up in the Tables folder, by creating a query with the source that references the expression as shown below

I hope this little trick helps as it has definitely helped me. Have fun BI-ing!

[1] https://blogs.msdn.microsoft.com/analysisservices/2016/12/30/evaluating-shared-expressions-in-tabular-1400-models/

4 comments on “Quirks of Visual Studio Query Editor working with Analysis Services”

  1. Thanks for this. I’ve successfully used it to allow me to mostly promote Power BI models into analysis services (via lots of copy paste). Is this still your preferred method for creating tables or has Microsoft slipped something new in yet?

    1. There is no easy way to promote Power BI models to Analysis Services… There was a feature in Azure to restore Power BI to Azure AS but it’s been depricated… There is a great tool – Tabular Editor, that has many cool features.. Maybe there is a way to back up from Power BI and restore to AS but I have not done it yet myself… Tabular editor does allow to bulk cut and paste from one model to another so it might be worth a try

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s