Skip to content
James Broome By James Broome Director of Engineering
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.

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).

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.

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.

Azure Weekly is a summary of the week's top Microsoft Azure news from AI to Availability Zones. Keep on top of all the latest Azure developments!
Azure Analysis Services - How to process an asynchronous model refresh from .NET

Azure Analysis Services - How to process an asynchronous model refresh from .NET

James Broome

Integrating Azure Analysis Services into custom applications doesn't just mean read-only data querying. But if your application changes the underlying model, it will need to be re-processed before the changes take effect. This post describes how to use the REST API for Azure Analysis Services inside a custom .NET application to perform asynchronous model refreshes, meaning your applications can reliably and efficiently deal with model updates.
Azure Analysis Services - integration options using .NET, REST APIs and PowerShell

Azure Analysis Services - integration options using .NET, REST APIs and PowerShell

James Broome

With a variety of integration support through client SDKs, PowerShell cmdlets and REST APIs, it can be hard to know where to start with integrating Azure Analysis Services into your custom applications. This posts walks through the options, and lays out a simple guide to choosing the right framework.
Testing Power BI Reports using SpecFlow and .NET

Testing Power BI Reports using SpecFlow and .NET

James Broome

Despite being inherently difficult to test, the need to validate data modelling, business rules and security boundaries in Power BI reports is important, as well as the need for ensuring that quality doesn't regress over time as the insights evolve. This post explains that, by connecting to the underlying tabular model, it is possible to execute scenario-based specifications to add quality gates and build confidence in Power BI reports, just as any other software project.

James Broome

Director of Engineering

James Broome

James has spent nearly 20 years delivering high quality software solutions addressing global business problems, with teams and clients across 3 continents. As Director of Engineering at endjin, he leads the team in providing technology strategy, data insights and engineering support to organisations of all sizes - from disruptive B2C start-ups, to global financial institutions. He's responsible for the success of our customer-facing project delivery, as well as the capability and growth of our delivery team.