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

In my previous post I discussed 8 reasons why you might want to integrate Azure Analysis Services into your own custom applications.

With a variety of support through client SDKs, PowerShell cmdlets and REST APIs, the opportunities are endless, and the subsequent series of technical how-to's will go into more detail around some of the core aspects.

But with this level of choice, it can be hard to know where to start, or which is the right fit for your scenario. This posts walks through the options, and lays out a simple guide to choosing the right framework.

ADOMD.NET

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. The libraries have been around since pre-Azure Analysis Services for use with SQL Server Analysis Services, and therefore support multi-dimensional querying (i.e. MDX) which isn't supported in the Azure hosted service.

However, it can also absolutely be used for connecting to Azure Analysis Services instances and executing DAX queries over tabular models.

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. I'll go into more detail in a subsequent post but, to get started, you'll need the Microsoft.AnalysisServices.AdomdClient nuget package, and the documentation for retrieving data from an analytical data source.

AMO

Analysis Management Objects (AMO) is another .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.

MSOLAP

It's worth pointing out that the Analysis Services OLE DB provider (MSOLAP DLL) is used indirectly by both ADOMD.NET and AMO, delegating connection requests to the data provider. As such, it's installed automatically by most tools and client applications used to access Analysis Services databases - for example Power BI and Excel. Whilst you can also call the OLE DB Provider directly from application code, it's not recommended if you have access to either of the above managed .NET SDKs.

Azure Management REST API

The Azure Management API includes a set of operations for the purposes of administering Azure Analysis Services servers. These APIs provide similar functionality to what you can achieve using the Azure Portal UI - i.e. creating a new server instance, and suspending, resuming, updating and deleting it.

The REST APIs are still rather limited, but if you're doing a lot of administration in the Portal, then this allows for programmatic automation, using any HTTP based client. A good way to get started is with the API playground, allowing you to easily try out the service (after authenticating with your Azure Active Directory credentials).

REST API for Azure Analysis Services

Alongside the general purpose Azure management API, there is a specific REST API for Azure Analysis Services, which deals solely with asynchronous data-refreshes of tabular models. This API is part of Azure Analysis Services itself - i.e. it's cloud-only, so isn't available for your on-premise SQL Analysis Services instances. The key part to this is that it's asynchronous - in fact, it's the only way to trigger an asynchronous refresh. All the other methods (using the previous mentioned SDKs, or PowerShell) rely on a long-running HTTP connection which is error prone if data volumes are large, or models contain complex calculations and data manipulations.

It's not clear if this API will evolve over time to include other useful Azure Analysis Services-specific features, but whilst it currently only serves a single purpose, it fills a gap that the other integrations don't support. To get started, this introduction post from the product team explains more about the specific use cases.

Azure PowerShell Az module

If you need to automate Azure Analysis Services through scripting, then the new Azure PowerShell Az module includes cmdlets that can be used to perform server and database management tasks. The functionality supported is similar to that of the management REST API - creating, suspending and deleting servers. This is because under the covers, it's calling the same REST API to perform these operations, however using first-class cmdlets simplifies scripting out these management tasks.

To get started, you'll need the Az.AnalysisServices module, and the associated reference documentation.

SQL Server PowerShell module

Whilst the Az PowerShell module is specifically targeted at Azure Analysis Services, it only supports server management operations. If you want to query data, then you'll need to use the general SQL Server module - Azure Analysis Services database operations use the same SqlServer module as SQL Server Analysis Services. Not all cmdlets are supported for Azure Analysis Services, but the Invoke-ASCmd cmdlet accepts a Tabular Model Scripting Language (TMSL) query or script, allowing you to query data, or manipulate the model itself.

Conclusion

This posts has shown that there's many different ways to integrate Azure Analysis Services into your custom applications and processes. However, each integration mechanism has it's own strengths and weaknesses - as they're designed to target different integration scenarios. To help you make the right choice on which framework to use, the table below summarises all the options detailed above.

Summary of Azure Analysis Services integrations options: