In this tutorial, you’ll learn how to connect DAX Studio to Power BI and to the different variations of the SQL Server Analysis Services.
This demo will go through four processes:
- Connecting DAX Studio to Power BI
- Connecting DAX Studio to the PowerPivot Model in Excel
- Connecting DAX Studio to SQL Server Analysis
- Connecting DAX Studio to Power BI Service.
Before proceeding with this, make sure you’ve successfully installed SQL server along with its client tool and database. If you haven’t, go through the installation tutorial first.
1. DAX Studio To Power BI
There are two ways to launch DAX Studio. The first is launching it inside Power BI; while the second is launching it from outside the Power BI program.
To demonstrate how to connect DAX Studio to Power BI, you need to do the second option so open DAX Studio from your computer.
You’ll then see the three Data Source options where you can connect DAX Studio. For this instance, you want to connect to Power BI so you need to choose the PBI / SSDT Model option. Once done, click Connect.
You can then see that DAX Studio has connected to Power BI.
To check if the connection is working, you can write a simple query such as EVALUATE Customers and then Run it.
You’ll know it’s working since it was able to retrieve the data inside the Power BI model.
2. DAX Studio To The PowerPivot Model In Excel
The next process is to connect DAX studio to the PowerPivot model inside Excel.
Notice in the first process as you open DAX Studio that the PowerPivot option can’t be selected. This is because you can only connect DAX Studio to the PowerPivot model when you’re inside the Excel program. This is applicable if you’re using the portable version of DAX Studio.
Excel will only allow a connection to DAX Studio if it’s launched within the Excel program.
So, open Excel. This is the data used for this example.
You can download this file in the DAX Studio For Power BI and SSAS – Beginner to Advanced Course in the Enterprise DNA website.
Then, go to the Add-in tab and select DAX Studio.
You’ll then be redirected to DAX Studio. Notice that the option for the PowerPivot connection can now be selected.
After you select it and click Connect, DAX Studio will now show all the tables inside the PowerPivot model.
Again, to check if the connection is working properly, you can Run a simple query. You can continue to use the EVALUATE Customers code that was used in the first process. If the results show the contents of the Customer’s table, that means that the connection is working.
3. DAX Studio To SQL
The third thing to do is connect DAX Studio to SQL Server Analysis Services (SSAS) and SQL Server Data Tools.
In this example, two models have already been deployed in SSAS.
DAX Studio can’t be launched within the SQL Server Management Studio. So open DAX Studio and connect it from there by choosing the third option, Tabular Server.
In the field, write the name of your machine (or computer) and the name of the server separated by a backslash ( \ ). In this case, it’s summer/antriksh. Then, click Connect.
DAX Studio will then contain the two models in the SSAS program.
To check if both are working, run a simple query for each model.
With this, you can easily switch between two different models and access the data contained in them.
That’s for connecting to SSAS. Let’s move forward to connecting DAX Studio to the SQL Server Data Tools.
First, launch DAX Studio from your computer and then select the second option PBI / SSDT Model. If you click the drop down menu, you’ll see two options.
You can connect to either Power BI or SQL Server Data Tools. The latter is defined by the blue/purple visual studio icon. So, choose Contoso SSAS Integrated.
When you click Connect, you can see that in the Metadata pin, you’re able to view all the tables inside the SQL Server Data Tools program.
Again, to check if the connection is working, you can run a query.
4. DAX Studio To Power BI Service
The last process is to connect DAX Studio to the Power BI Service.
Go to Power BI and then click the Workspace you want DAX Studio to be connected to. Then, click on Settings.
Under the Premium option, choose Premium per user and then copy the Workspace Connection string.
Launch DAX Studio from your computer and then choose the Tabular Service option. Paste the Workspace Connection string in the field. Then, click Connect.
A new window will then appear asking you to sign in. Make sure to log-in to the account you want to use when working with DAX studio and the Power BI Service.
Once done, you can see that you’ve successfully connected to the model in the Power BI Service program.
To check if everything is working properly, run a DAX query. If the results show the contents of the table used in your code, then the connection was successful.
***** Related Links *****
Query Performance And DAX Studio Setup
DAX Query Structure In Power BI – Two Engines & DAX Studio
Materialization Ideas For Data Caches In DAX Studio
Conclusion
This tutorial showed how to connect DAX Studio to four other programs including Power BI and the SQL server analysis services.
DAX Studio is a powerful tool in report development. But when used in conjunction with other tools, more features that increase work efficiency become accessible.
Enterprise DNA Experts
