Using Visual Studio Code to query Power BI

In this article, I’m going to show how you can use Visual Studio Code to run queries against a Power BI model. Usually, this is the kind of task you would use the excellent DAX Studio for, but sometimes you have a requirement that doesn’t quite fit DAX Studio. Using VSCode provides a quick and flexible alternative that you can customise to suit, e.g. create customised extracts

One scenario that I did get asked about recently is how to create measures in a model dynamically based on data. This particular scenario is covered by example 2 in this article.

Client Libraries

It’s helpful to understand there are two main client libraries for Analysis Services. A client library is what you can add to any new Visual Studio Code Project to provide objects, methods and functions relevant for the tool you are building.

Make sure you download the NetCore (.Net Core) versions of these libraries when working with Visual Studio Code. There are .Net Framework versions of these libraries that are more suited to use with the full Visual Studio product.

The two Analysis Services client libraries are:

AMO

The AMO client library provides your VSCode project with functions and objects that allow you to connect to an Analysis Services model and manage the physical structure of the model. Tasks such as add/change/removing tables, columns, measures, relationships etc. My two previous articles used this client library for this and often gets referred to as using TOM or the Tabular Object Model. If you are a regular user of Tabular Editor, then consider this client library as the one you would use to automate the kinds of tasks you might perform using Tabular Editor.

AMOMD

The AMOMD client library allows you to connect to an Analysis Services model and issue DAX queries and receive results. Think of AMOMD as enabling you to perform tasks you might otherwise use DAX Studio for. Exercise 1 below introduces this client library. Of course, your project can include tasks using both libraries for some really creative fun.

Exercise 1: Use Visual Studio Code to Query Power BI Desktop

The first exercise is going to show how to run queries from Visual Studio Code against Power BI.

In this exercise, we will set up a new project in Visual Studio Code, add the AMOMD client library and then write some basic query code.

Please make sure you have already completed the first two steps from my previous article here to :

  1. Downloaded and installed Visual Studio Code
  2. Installed the latest .Net Core SDK

Step 1: Create Visual Studio Project

Create a blank folder somewhere on your machine. I created a new folder on my machine called c:\vscode\QueryTool

Open Visual Studio Code and from the File menu, select Open Folder and navigate to, and open the blank folder created previously.

Step 2: Create .Net Console Project

Once you have opened your folder in Visual Studio Code, issue a command in a PowerShell Terminal window to create the .Net project.

Once the terminal window appears, issue the following command

dotnet new console

Step 3: Install AMOMD client Libraries

The next step is to add the AMOMD client Libraries. You add the libraries using the same Terminal window from the previous step. To find the most recent version of the library, open the following URL in your browser to load details of available versions.

https://www.nuget.org/packages/Microsoft.AnalysisServices.AdomdClient.NetCore.retail.amd64

At the time of writing this blog, the latest version is 19.9.0.1-Preview. In the Nuget page, click the .Net CLI tab to show the client library interface command required and then copy this text into your clipboard.

Finally, paste the text into the terminal window back in your Visual Studio Code project.

dotnet add package Microsoft.AnalysisServices.AdomdClient.NetCore.retail.amd64 --version 19.9.0.1-Preview

This is all that is needed to get us ready to write code!

Step 4: Start writing code! 😀

Open the Program.cs file from the left-hand explorer by double-clicking the file name at (1). This action opens the file and shows 12 lines of text in the area indicated by (2).

If you see a dialog box in the bottom right-hand corner like (3), just click the Yes button (4).

The Power BI model I will connect to in this example can be downloaded here: AdventureWorksDemoPBIX

Once you have Program.cs open, paste the following code over the top of all the existing code.

Please note, you will need to update the port number in the connection string in line 14 of this example to match your instance of Power BI Desktop. How to find the port number?

using System;
using Microsoft.AnalysisServices.AdomdClient;

namespace QueryTool
{
    class Program
    {
        static void Main(string[] args)
        {
            /*******************************************************
                Define Connection
            *******************************************************/

            AdomdConnection adomdConnection = new AdomdConnection("Data Source=localhost:50834");
           
            /*******************************************************
                Define Query (as a Command)
                - the AdomdCommant uses the above connection
                - subsitute this for your own query
            *******************************************************/

            String query = @"
            EVALUATE
                SUMMARIZECOLUMNS(
                    //GROUP BY 
                    Customer[Country-Region] ,
                    
                    //FILTER BY
                    TREATAS( {""Red"",""Blue""} , 'Product'[Color] ) ,
                    
                    // MEASURES
                    ""Sum of Sales Amount"" , SUM(Sales[Sales Amount]) ,
                    ""Sum of Order Quantity"" , SUM(Sales[Order Quantity])
		        )
            ";
            AdomdCommand adomdCommand = new AdomdCommand(query,adomdConnection);

            /*******************************************************
                Run the Query
                - Open the connection
                - Issue the query
                - Iterate through each row of the reader
                - Iterate through each column of the current row
                - Close the connection
            *******************************************************/

            adomdConnection.Open();
            
            AdomdDataReader reader = adomdCommand.ExecuteReader();

            // Create a loop for every row in the resultset
            while(reader.Read())
            {
                String rowResults = "";
                // Create a loop for every column in the current row
                for (
                    int columnNumber = 0;
                    columnNumber<reader.FieldCount;
                    columnNumber++
                    )
                {
                rowResults += $"\t{reader.GetValue(columnNumber)}";
                }
                Console.WriteLine(rowResults);
            }
            adomdConnection.Close();
        }
    }
}

Notes about the code :

  • You define your query at line 22. The @ command at the start of the string allows you to space your query over multiple lines. The ” character needs to be converted to “” as shown in lines 29, 32 and 33.
  • A query file could be read in from a file instead of by using the following at line 22:
    String query = System.IO.File.ReadAllText(@”myQuery.dax”);

Video summary of exercise

Challenges for you :

  • Change the code to read a DAX query from a file.
  • Add the Column headers to the output.
  • Write the results out to a file.

Exercise 2: Use query results to create measures!

The second exercise in this blog will build on the existing project to combine the results of a query (using AMOMD client library), with TOM to add measures to the model.

The object is to dynamically create a series of measures that perform a SUM over a particular column. In this case, it will be based on the Product Colour but could easily use other columns in the model.

Step 1: Add the AMO client libraries (TOM)

Using the existing project, add the new package. To determine the correct path, open the following URL in a browser to get the latest .Net CLI to install the package:

https://www.nuget.org/packages/Microsoft.AnalysisServices.NetCore.retail.amd64

In my case, the latest copy to install is

dotnet add package Microsoft.AnalysisServices.NetCore.retail.amd64 --version 19.9.0.1-Preview

Step 2: Write DAX to get a list of values

Over in DAX Studio, I created a simple query to generate a list of all the values in the ‘Product'[Color] column. I save this query as a file called QueryColurs.dax in the same folder as my Visual Studio Code project (c:\vscode\QueryTool).

Step 3: Write some C# code to create measures

The last step is to overwrite all the existing code in the Program.CS with the following snippet and again, take care to update the current port number at line 12.

using System;
using Microsoft.AnalysisServices.AdomdClient;
using Microsoft.AnalysisServices.Tabular;

namespace QueryTool
{
    class Program
    {
        static void Main(string[] args)
        {

            String currentPort = "50834";

            /*******************************************************
                Define Connection
            *******************************************************/

            AdomdConnection adomdConnection = new AdomdConnection($"Data Source=localhost:{currentPort}");

            /*******************************************************
                Connect to Power BI Desktop using TOM
            *******************************************************/

            Server server = new Server();
            server.Connect($"localhost:{currentPort}");
            Model model = server.Databases[0].Model;
            Table salesTable = model.Tables["Sales"];
           
            /*******************************************************
                Define Query (as a Command)
                - the AdomdCommant uses the above connection
                - subsitute this for your own query
            *******************************************************/

            String query = System.IO.File.ReadAllText("QueryColours.dax");
            AdomdCommand adomdCommand = new AdomdCommand(query,adomdConnection);

            /*******************************************************
                Run the Query
                - Open the connection
                - Issue the query
                - Iterate through each row of the reader
                - Iterate through each column of the current row
                - Close the connection
            *******************************************************/

            adomdConnection.Open();

            AdomdDataReader reader = adomdCommand.ExecuteReader();

            String measureDescription = "Auto Measure";



            /*******************************************************
                Clear any previously created "Auto" measures
            *******************************************************/
            foreach(Microsoft.AnalysisServices.Tabular.Measure m in  salesTable.Measures)
            {
                if(m.Description==measureDescription)
                {
                salesTable.Measures.Remove(m);
                model.SaveChanges();
                }

            }

            /*******************************************************
                Create the new measures
            *******************************************************/            

            // Create a loop for every row in the AMOMD resultset
            while(reader.Read())
            {
                String myColour = reader.GetValue(0).ToString();
                String measureName = $"Sum of {myColour} Sales Amount";

                Microsoft.AnalysisServices.Tabular.Measure measure = new Microsoft.AnalysisServices.Tabular.Measure()
                    {
                        Name = measureName,
                        Description = measureDescription ,
                        DisplayFolder = "Auto Measures" ,
                        FormatString = "Currency" ,
                        Expression = $@"
                            CALCULATE (
                                SUM('Sales'[Sales Amount]) ,
                                'Product'[Color] = ""{myColour}""
                            )
                        ",
                    };
                salesTable.Measures.Add(measure);
            }

            model.SaveChanges();
            adomdConnection.Close();
        }
    }
}

Notes about the code:

  • Lines 20 to 27 create a TOM connection to the model.
  • Line 35 reads a query in QueryColours.dax file that needs to be in the same folder as the code.
  • Line 58 uses the fully qualified namespace for Measure to ensure we are using the AMO version of a Measure rather than the AMOMD version.
  • Lines 58 through 66 clear out any measures previously created using this technique but leaving other measures in the model alone.

Once run in Visual Studio Code, you should see the following in your Power BI Desktop instance.

And that’s it!

A video version of this example is available here:

This exercise is an example of how you can combine the two Analysis Services client libraries (AMO and AMOMD) inside Visual Studio Code to help perform tasks.

Summary

The Analysis Services client libraries provide fantastic flexibility to create a library of utility scripts that can complete pretty much any task.

I’m not recommending this approach as a replacement to the excellent Tabular Editor, or DAX Studio as these are my go-to tools of choice. However, for edge cases, it’s nice to know that you can easily use Visual Studio to work with your model.

I’ll have had a crack at recording these as videos. Please do not expect the excellent quality of my team-members (GuyInaCube and BIPolar), but let me know in the comments if this format helped you out.

Video of Exercise 1
Video of Exercise 2

4.5 4 votes
Article Rating

Leave a Reply

8 Comments
Inline Feedbacks
View all comments
Didier Terrien
2 months ago

Hello Phil,
Thanks a lot for the blog post. The possibilities are infinite!
Is it possible to run DMV queries as well?
Best regards
Didier Terrien

M
M
2 months ago

Hello. Could we possibly use this to collaborate on .pbix files? Meaning could multiple users use this to update/change dax queries at the same time?

trackback

[…] the art of the possible with the Microsoft BI platform. And I really liked his recent article on using VS Code to query a Power BI file and generate measures. But as I was reading that blog post I could not help but think that it was going to be outside of […]

trackback

[…] This is a follow-up to my previous post on using a combination of DAX Studio and Tabular Editor to generate a series of measures based on the values in a given column. Which in turn was inspired by this excellent post by Phil Seamark. […]

Mark McAdam
Mark McAdam
1 month ago

Hi Phil,
This is working great local but when i connect to power bi service.
my Model model = server.Databases[0].Model;
model is null.
any ideas ?
thanks

Nishant
Nishant
23 days ago

Hi Phil, great blog.

The possibilities are infinite.

I was thinking of using this technique against Power BI premium node just like Tabular Editor and DAX studio can do. however they use Azure AD (I think) to authenticate the user and being a novice I am not aware how to implement this authentication. Would you know any blog / documentation that can help me implement the authentication

Thanks