I have recently been working on SSAS Tabular 2017 for the current customer I am consulting at.

Along with this I am starting to see that Power BI Premium features are appearing to lean more towards SSAS, so I personally feel that getting into SSAS Tabular 2017 is a great way to understand and work with a similar version of Power BI Premium, which from what I can personally gather is leaning more towards Azure Analysis Services. This post also will apply to Azure Analysis Services.

Enough waffling, what this blog post will cover is how to use a Custom TSQL Server Query in SSAS Tabular 2017 and leveraging the SQL Server Database Window as shown below.

Example

In this example I am going to complete the following:

  • Connect to a SQL Server Database in SSAS Tabular
  • Create a New Expression using the Advanced Editor
  • Edit the Expression using the SQL Server Database window to edit my TSQL Query
  • Add the table to the Model
  • Creating the Data Source Connection in SSAS Tabular

I have got my SSAS Tabular Project created.

Next, I connect to my SQL Server Database as shown below.

  • I right click on Data Sources and select Import From Data Source…
  • I then select SQL Server database from the Get Data window
    • Then click Connect.
  • I then put in my SQL Server details
    • Then I click Ok
  • I then get prompted for the Credentials to use
    • NOTE: I personally set up the SSAS Tabular Service account, to use a domain account. And this same account then has access to the required data sources.
    • I then click Connect
  • Now I have created my data source connection.

Creating a new Expression

In the steps below, I am going to show how to create a new expression using the Advanced Editor, which will then enable me to use the SQL Server Database Window.

  • I right clicked on Expressions and then clicked on Edit Expressions
  • Once the Query Editor Window opens, I then clicked on Query, New Query and selected Blank Query
  • On my table called Query1, in the Query Editor, I click on View and then selected Advanced Editor
  • I now put in the following where it says Source
    • CODE:
    • Sql.Database("mydatabaseserver", "mydatabase", [Query=" Select * from MyTable", CreateNavigationProperties=false])

       

    • What the Sql.Database represents is the following:
      • “mydatabaseserver” – Change this to your SQL Server Database Name
      • “mydatabase” – Change this to your Database Name
      • The TSQL Query
        is in the section highlighted in Blue above.
    • NOTE: Ensure that in the Query section above it is a valid query to any table in your database.
  • Once that is done you should then I then saw my table loaded in the Query Editor

Editing your TSQL Query using the SQL Server Database

Now that I have created the expression, I can now edit the TSQL Query using the Window as shown below.

  • On my table under the Applied Steps I can click on the cog next to Source
  • And this now brings up the SQL Server database window, where I can now enter my TSQL Query, or view the query I created previously.
    • I then clicked Ok

Adding the table to the data model

One of the things that I had to learn when using SSAS Tabular with the Visual Studio Editor is that I NEVER know how to load an expression into the data model.

This did cause me some frustration, until I realized how easy it is. So, this is for people who have been looking for it before.

  • Right click on the table, as with my example was Query1 and select Create New Table
  • Once that is done I then clicked on Home and then Close & Update
  • This will then load the table to the data model.
  • So now I have got my data loaded from my TSQL syntax into my data model.

Conclusion

As I have demonstrated above, there is an alternative way in which you can put in Custom Queries when using SSAS Tabular 2017.

If there are any questions or comments please leave them in the section below.