Azure Analysis Services - How to query all the measures in a model from .NET
In my previous post I post explained how to execute a DAX query from inside your custom application using the ADOMD.NET SDK.
This allows you to expose the data insights inside Azure Analysis Services for use in your applications, or to external applications through APIs. But it doesn't have to stop there - as well as exposing the data in your model, it's also possible to query and expose the metadata about the model itself. This post explains how and why you might want to do this, and shows how easy it is to do with a code sample to get started.
Exposing model metadata?
In a previous post, I explained all the reasons why you might want to integrate Azure Analysis Services in to a custom application. Looking at just a few of these reasons - providing ultimate UI flexibility, supporting a developer community, or updating the model schema - it starts to become apparent why the model metadata is as useful as the data itself.
Your custom UI might provide the ability to pick and chose which data points are displayed, which means exposing the list (or sub set) of measures or columns in your model in the interface. Taking that a step further, layering on a GraphQL style API that exposes your model schema would allow developers and the applications they're building to ask for exactly what they need when querying over your model. And if you want to allow the expression behind a calculated column to be updated, you'll need to be able to interrogate the metadata accordingly.
Querying model measures
With the ADOMD SDK, it's possible to get access to the underlying data model, which is exposed as a CubeCollection on the AdomdConnection. Whilst the naming is slightly inconsistent, a cube essentially relates to a model in your Azure Analysis Services instance. Whilst you might have multiple models within a provisioned instance, typically you'll start with one, and even if there are more you can find the one you need easily using the Name property of each CubeDef item in the array. Assuming only one model, the code below shows how to iterate through the list of measures and return a list of their names:
using Microsoft.AnalysisServices.AdomdClient;
public async Task<List<string>> GetMeasuresAsync()
{
var measureNames = new List<string>();
using (var connection = new AdomdConnection(this.GetConnectionString(await this.GetTokenAsync())))
{
connection.Open();
// Assumes one model - if not, can use .Name property to filter
foreach (var measure in connection.Cubes[0].Measures)
{
measureNames.Add(measure.Name);
}
}
return measureNames;
}
Looking at the CubeDef class in a bit more detail, you can see that it's also possible to retrieve the Dimensions and KPIs, as well as other general metadata from the model.
Querying v.s. updating
It's important to note that at this point the metadata being queried is read-only. If we wanted to update an underlying expression we'd have to use the AMO SDK to create a connection to the server object in order to make and process model updates. For an overview of the different integration options, my previous post lays out a simple guide to choosing the right framework for each common scenario.
Conclusion
This post has shown that there's a number of reasons why you might want to query and expose elements of metadata surrounding your Azure Analysis Services model in your custom applications and processes. The code sample illustrates a quick way to get started with programmatically exploring the list of measures, but it can easily be extended for other scenarios, according to your specific requirements.
