Azure Analysis Services: How to update the expression for a calculated column from .NET

In a previous post, I explained that as well as querying data in your Azure Analysis Services models from custom .NET applications, it was also possible to query and expose the metadata about the model itself.

I argued that if your custom application wanted to allow the expression behind a calculated column to be updated, you'd need to be able to interrogate the metadata accordingly.

However, that post stopped at querying the metadata in a read-only fashion using ADOMD.NET. If we actually want to update an underlying expression, we have to use the AMO SDK to create a connection to the server object in order to make and process the model updates. 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.

AMO SDK

I've already provided an overview of the different integration options for Azure Analysis Services, including a simple guide to choosing the right framework for each common scenario.

The Analysis Management Objects (AMO) SDK is .NET library, allowing modelling and administration of Analysis Services instances, including Azure Analysis Services. Rather than querying data, this library exposes the inner workings and metadata of your model (tables, columns etc), as well as the higher level management objects around servers, databases and roles.

To get started, you'll need the Microsoft.AnalysisServices nuget package. This includes the Microsoft.AnalysisServices.Tabular package, which includes specific APIs for working with tabular models. As this is the only type of data model that Azure Analysis Services supports, the Microsoft.AnalysisServices.Tabular namespace will most likely be your primary entry point.

Updating the model

Once you've connected to the Azure Analysis Services instance using AMO, interrogating the model schema is fairly straightforward. You can navigate the list of Databases (which equate to the Models in Azure Analysis Services), each will contain a single tabular Model, from which you can access the list of Tables. Finding a specific column in a table to update it's properties, or adding a new column to the list is as you'd expect, as illustrated in the code sample below.

using Microsoft.AnalysisServices.Tabular;

public async Task AddOrUpdateCalculatedColumnAsync(string tableName, string columnName, string expression)
{
    using (var server = new Server())
    {
        server.Connect(this.GetConnectionString(await this.GetTokenAsync()));

        var db = server.Databases[this.modelName];
        var model = db.Model;
        var table = model.Tables[tableName];

        if (table.Columns.ContainsName(columnName) && table.Columns[columnName] is CalculatedColumn calculatedColumn)
        {
            calculatedColumn.Expression = expression;
        }
        else
        {
            table.Columns.Add(new CalculatedColumn { Name = columnName, Expression = expression });
        }

        model.RequestRefresh(RefreshType.Automatic);
        model.SaveChanges();
    }
}

Applying the changes

Remember - once you've made your changes, in order for them to take effect, the model updates need to be saved and the model needs to be re-processed. Depending on the complexity of your model, and the size of your data the processing time will vary. Using the AMO SDK, as per the code sample, means that this processing is going to be a synchronous operation, which could mean a long-running and sometimes unreliable HTTP connection.

To minimise the impact of this run-time processing an asynchronous model refresh could be performed - but the only way to do this is by using the REST API for Azure Analysis Services. I've written a post describing this process in more detail.

Conclusion

With the AMO SDK, it's possible to update the schema and metadata of your Azure Analysis Services tabular models. This post has shown how easy it is to integrate this functionality into a .NET application, giving you everything you need to get started with more complex Azure Analysis Service integration scenarios.