August 29, 2021|developer tip|

how to debug visual studio tabular model projects in power bi.

introduction.

A simple and proven way to debug and examine efficacy of tabular models in Visual Studio, is to use the Analyze in Excel function. It is, however, also feasible to test the project in Power BI without deploying the model to an (Azure) Analysis Services or Power BI Premium instance. This blog post shows you how.
As a quick recap, here is how to directly connect Excel to the Visual Studio tabular model:

1. Open the Tabular Model Explorer of the tabular project
2. Click on the Excel icon or if you can’t find it for some reason, you can also go via Extensions > Model > Analyze in Excel

3. After choosing on how you would like to connect to the model (see this to find out more if you’d like to connect with different roles to check perspectives), Excel will open with a ready to use PivotTable:

While this is an easy way to see how adjustments in the project affect the model, you might be more comfortable with checking your changes directly in Power BI. After all, there are some features that Excel PivotTable Fields displays differently than Power BI Fields. So, how can we debug a model without deploying it to Analysis Services or Power BI Premium?


prerequisites.

  1. Visual Studio
  2. Power BI Desktop


plan of action.

  1. Find the workspace server in Visual Studio or Excel
  2. Open Power BI and select Analysis Services as data source
  3. Connect to server


1. find the workspace server in visual studio or excel.

Choose the Solution Explorer tab, right-click on Model.bim, select Properties, scroll down in the Properties pane, and find Workspace Server. Copy the string.

If you happen to have connected Excel to the model (as shown in the introduction), you could also get the Workspace Server from there. Select the Data ribbon in Excel, press Queries & Connections, double-click the connection, and copy the server part of the Connection name:


2. open power bi and select analysis services as data source.

Open Power BI Desktop, click on Get Data, and select Analysis Services


3. connect to server

a. Paste the connection string into the server field and click ok

b. Expand the navigation, select Model, and click ok

Now, the model including its tables, measures, calculation groups etc. should be displayed on the right-hand side – as you would connect to Analysis Services or to a dataset in Power BI Premium.


end.

The great thing is that you can immediately debug and check your changes on the model (i.e. you might have just created a new calculation group or maybe you want to see how your developed advanced dax code behaves), just by pressing the refresh button in Power BI. Then, if you are fine with your alignments, just deploy it to Analysis Services or Power BI.
If you are developing Power BI datasets, you could also be interested in other stuff from this blog, like how to refresh datasets from azure data factory with managed identity, how to make a service principal the owner of a dataset or how to automate the management of custom partitions for power bi datasets. Feel free to check them out!

2 Comments

  1. Mark September 13, 2022 at 9:26 am - Reply

    Thanks for your blog, nice to read. Do not stop.

    • tackytechtom September 17, 2022 at 9:15 am - Reply

      Thanks Mark! :-)

Leave A Comment