Azure Analysis Services: How to execute a DAX query from .NET

In my previous post I post explained how to create Azure Analysis Services connections using the different SDKs, cmdlets and APIs available, including code samples for each of the key scenarios. Once you've successfully made a connection, the first thing you'll probably want to do is query some data! So, this post explains how to execute a DAX query from inside your custom application, using the ADOMD.NET SDK.

Using ADOMD.NET to retrieve data

ActiveX Data Objects Multi-Dimensional (ADOMD.NET) is a managed data client library used for connecting to Analysis Services servers and querying data from .NET code. If you've done anything with ADO.NET in the past, then it should all be fairly familiar – create and open a connection using the connection string for your server, and fill a data adapter or data reader with the results returned from your DAX query.  You can install the client libraries via the Microsoft.AnalysisServices.AdomdClient nuget package.

The simplest way to return tabular data (and Azure Analysis Services only supports tabular models) is to use the AdomdDataAdapter to fill a DataTable, as shown in the following example. N.B. Refer to my previous post for code samples on how to create the connection string.

using Microsoft.AnalysisServices.AdomdClient;
using System.Data;

private async Task<DataTable> ExecuteDaxQueryAsync(string query)
{
    var tabularResults = new DataTable();

    using (var connection = new AdomdConnection(this.GetConnectionString(await this.GetTokenAsync())))
    {
        connection.Open();

        var currentDataAdapter = new AdomdDataAdapter(query, connection);
        currentDataAdapter.Fill(tabularResults);
    }

    return tabularResults;
}

Centralising and encapsulating the execution of queries in your application using a method like the example above means that you can start to layer on cross-cutting, operational concerns like logging, security and telemetry around your query executions.

Constructing the DAX query

The method above takes a query parameter as a string input. The value is just a plain-old DAX string, so could be anything you want. Typically, for an application that queries data, it's going to be some kind of EVALUATE expression, and as we're dealing with tabular data models in Azure Analysis Services, a simple query might look something like the one below, which calculates the total sales (using the [TotalSales] measure) for each product category (the [Category] column in the DimProducts dimension table).

public async void CalcualteTotalSalesByCategory()
{
    string query = $@"EVALUATE(
                        SUMMARIZECOLUMNS(
                            'DimProducts'[Category],
                            ""Total Sales"", [TotalSales]
                        )
                    )";

    var results = await ExecuteDaxQueryAsync(query);

    foreach (DataRow row in results.Rows)
    {
        var category = row.Field<string>(0);
        var totalSales = row.Field<long>(1);

        // Do something with the tabular results...
    }
}

Dynamic query construction

The real benefit to integrating this functionality into your custom applications is that you now have a way to dynamically construct DAX queries at run time. Equally, you could use this to encapsulate known queries and expose them through application APIs, expose subsets of your model in different scenarios, or layer any sort of custom business logic that you need on top  of your underlying model schema.

Extending the previous example, a simple use case could be to include a parameterised filter, so that only the sales for a specific year are included. Now the the DAX query is constructed in C#, it's easy to manipulate before execution, as show below.

public async void CalcualteTotalSalesByCategory(int year)
{
    string query = $@"EVALUATE(
                        SUMMARIZECOLUMNS(
                            'DimProducts'[Category],
                            FILTER(DimDate, DimDate[Year] = {year}),
                            ""Total Sales"", [TotalSales]
                        )
                    )";

    var results = await ExecuteDaxQueryAsync(query);

    foreach (DataRow row in results.Rows)
    {
        var category = row.Field<string>(0);
        var totalSales = row.Field<long>(1);

        // Do something with the tabular results...
    }
}

Imagining an API or UI sitting over this query logic, you now have the ability to select which year's data you return based on the input parameter. Extending this further, every part of the query could be constructed dynamically - the list of measures to include, the filters, dynamic expressions etc.

Conclusion

This post has shown that using ADOMD.NET to execute DAX queries from inside your custom applications is straightforward. Being able to construct queries dynamically in C# means that you can provide total flexibility in the results sets that you return to your end users inside your application. The possibilities are endless in terms of integrating Azure Analysis Services queries into your custom applications, and with the code samples in this post, you have everything you need to get started.