Intro

Try this little experiment: open your search engine of choice and look for “SQL Server Analysis Services .NET example”. Go on, I’ll wait. What you’ll find is a beautiful mess - a Stack Overflow answer from 2014 here, half a sample referencing AMO there, a Microsoft doc that quietly assumes you already know what a Tabular Object Model is, and roughly forty blog posts that each cover exactly one tenth of what you actually need. Nobody, it seems, has sat down and said “here’s how you do all of it from C#”.

So that’s what today’s post is about. We’re going to take a whirlwind tour of SQL Server Analysis Services (SSAS for short) from a .NET developer’s chair, and by the end you’ll know how to query it, refresh it, manage its security, and even pause the whole server to save your client some money. No 47-tab research session required. 🙂

A quick disclaimer before we start: this is a tour, not a manual. I’ll keep the code trimmed and link to the full proof-of-concept repository at the end (the link is also at the very bottom, as always 😉).

So… what is SSAS?

If you’ve never met it, SSAS is Microsoft’s analytics engine. You feed it data from your operational databases, it builds a tabular model (think: tables, relationships and pre-aggregated measures living in a column-store, optimised for “give me total sales per region for the last 5 years” in milliseconds), and then your reports - usually Power BI - query that model instead of hammering your transactional database. You talk to it in DAX (or MDX if you’re feeling vintage), and these days it lives in three different places:

• On-premises / localhost - the classic, install-it-on-a-server flavour.
• Azure Analysis Services (AAS) - the same engine, hosted, reachable via an asazure:// endpoint.
• Microsoft Fabric / Power BI Premium - the new kid, reachable via a powerbi:// endpoint.

Here’s the catch, and the reason the .NET story is so scattered: depending on what you want to do, Microsoft makes you reach for a different client library. Want to query? That’s ADOMD.NET. Want to manage databases, roles or kick off a refresh? That’s TOM (the Tabular Object Model). Want to pause or scale the server? Now you’re in the Azure Resource Manager SDKs. Three different mental models, three different ways of holding it.

The whole point of the repo behind this post is to hide that zoo behind a single, friendly interface - and as a nice bonus, the same code runs against localhost, Azure AS, and Fabric. Regular readers will recognise the pattern from my decoupling posts: one interface to rule them, the concrete chaos stays in one DLL.

One interface to rule them all

Here’s the surface we’re going to be poking at for the rest of the post:

public interface ISsas
{
    // Querying
    IEnumerable<T> Query<T>(string query, object param = null, CancellationToken ct = default);
    IEnumerable<T> Query<T>(DaxQuery query, CancellationToken ct = default);
    T Scalar<T>(DaxQuery query, CancellationToken ct = default);

    // Processing (a.k.a. "refresh the data")
    int Process(string script, CancellationToken ct = default);
    int Process(Action<SsasProcessScriptBuilder> configurator, CancellationToken ct = default);

    // "What is the server doing right now?"
    IEnumerable<SsasLock> GetSsasLocks(string databaseName = null, CancellationToken ct = default);
    bool IsProcessing(string databaseName, CancellationToken ct = default);
    void CancelProcessing(string databaseName, CancellationToken ct = default);

    // Metadata & security
    ISsasDatabaseStructure DatabaseStructure(string databaseName = null);
    ISsasRoleManager ManageDatabaseRoles(string databaseName);
    IEnumerable<SsasDatabase> GetDatabases(CancellationToken ct = default);

    // Server lifecycle (the money-saving stuff)
    Task<bool> PauseServerAsync(CancellationToken ct = default);
    Task<bool> StartServerAsync(CancellationToken ct = default);
    Task<bool> ScaleAsync(string skuTier, CancellationToken ct = default);

    // Escape hatch
    ValueTask<string> SendXmlaRequestAsync(XmlaSoapRequest request, CancellationToken ct = default);
}

That’s the entire deal. Behind it, ADOMD does the querying, TOM does the managing, the Azure SDKs do the lifecycle, and you never have to know which is which. Let’s wire it up and then walk through the fun bits.

Wiring it up (and the localhost / AAS / Fabric trick)

Registration is plain old IServiceCollection. The simplest case:

// Default factory - works for localhost and Azure Analysis Services
services.RegisterAnalysisServices(config);

// Targeting Microsoft Fabric? Just swap the factory.
services.RegisterAnalysisServices<SsasSettings, FabricSsasFactory>(config);

If you juggle more than one server (very common - a localhost dev box and an Azure one, say), register them as keyed singletons:

services.AddSsasInstance(config, "localhost", factory =>
    factory.WithConnection((cfg, svc) =>
    {
        var settings = svc.GetRequiredService<IOptions<SsasSettings>>().Value;
        // For Azure, the second argument carries subscription/resource-group info.
        // For localhost it can be an empty AzureResource.
        cfg.UsingConnectionString(settings.ConnectionString, new AzureResource());
    })
    .Create());

…and pull one out by its key:

var ssas = serviceProvider.GetRequiredKeyedService<ISsas>("localhost");

Now the neat part. You never tell the library which kind of server you’re on - the connection string does it for you. The data source prefix is the tell:

Internally there’s a tiny bit of sniffing going on, nothing magical:

internal bool IsAzureAnalysisServices() => DataSource.StartsWith("asazure://", ...);
internal bool IsFabricPowerBIEndpoint()  => DataSource.StartsWith("powerbi://", ...);
internal bool IsCloudAnalysisServices()   => IsAzureAnalysisServices() || IsFabricPowerBIEndpoint();

If it’s a cloud endpoint, the library quietly grabs an Azure AD token via Azure.Identity (service principal, UID/PWD in the connection string, whatever you configured), reuses the credential so MSAL’s internal cache does its job, and even re-fetches on OnAccessTokenExpired. You don’t write a line of that. For localhost, it just connects. Same ISsas, three worlds. 🌍

Querying: Dapper, but for DAX

This is the part you’ll use every single day, so it’s the part that should feel nice. If you’ve ever used Dapper, you already know the API:

public class Region
{
    public const string Query = """
        EVALUATE
            SELECTCOLUMNS(
                'Region',
                "RegionId", Region[Region Id],
                "RegionName", Region[Region Name]
            )
        """;

    public string RegionId { get; set; }
    public string RegionName { get; set; }
}

var regions = ssas.Query<Region>(Region.Query);

var california = regions.Where(r => r.RegionName == "California").ToList();

You hand it a DAX string and a POCO, you get back strongly-typed objects. Under the hood it’s an AdomdDataReader being mapped onto your type, but instead of slow reflection on every row it uses FastMember’s TypeAccessor (cached per type), so the mapping is quick even on chunky result sets. The execution is also deferred - it’s a yield return loop, so nothing happens until you start enumerating, and your CancellationToken is wired straight to AdomdCommand.Cancel(). Cancel the token, cancel the query mid-flight.

DAX has a habit of wrapping column names in brackets like [RegionName], which makes for ugly property names. Two attributes smooth that over:

public class Region
{
    [DaxColumnName("Region Name")]   // map a friendly DAX column to this property
    public string Name { get; set; }

    [DaxNotMapped]                   // computed locally, ignore when reading rows
    public string DisplayLabel => $"📍 {Name}";
}

Parameters work too - pass any object as param and its public members become DAX query parameters. And if you have a parameter you sometimes want to leave out (a nullable filter, say), there’s a [SkipDaxQueryParameter] attribute with a Skip / SkipIfNull condition so you don’t have to build two versions of the same query. Need a single value back instead of a list? Scalar<T> does exactly what it says.

var query = new DaxQuery
{
    Query  = "EVALUATE ...",
    Param  = new { RegionId = "CA" },
    Settings = new DaxQuerySettings
    {
        Database = "Sales",       // run against a specific DB
        EffectiveUserName = "jane@contoso.com", // run as a user, for row-level security
        Timeout = 120
    }
};

var rows = ssas.Query<Region>(query);

That EffectiveUserName is worth a mention - it lets you execute a query as if you were a particular user, so any row-level security defined in the model is applied. Great for “give me what this user would see in their report” scenarios.

Refreshing the data without writing TMSL by hand

SSAS models don’t update themselves - you process (refresh) them so they pull the latest data from their sources. The native way to ask for this is a chunk of JSON called TMSL, which is fine until you’re concatenating strings to build it. So instead there’s a little fluent builder:

// Refresh one specific table...
ssas.Process(builder => builder
    .CreateObject(o => o.Database("Sales").Table("FactSales")));

// ...or the whole database in one line.
ssas.Process(SsasProcessScriptBuilder
    .CreateFullRefreshScript("Sales")
    .Build());

You describe what to refresh (database, table, or even a single partition), it serialises the correct TMSL refresh script, and ships it. If you already have a hand-crafted script, the Process(string script) overload takes it as-is. Both honour cancellation.

“Is the server busy right now?”

Processing a large model can take a while, and the last thing you want is two refreshes stepping on each other - or to delete a role while a refresh is mid-flight. SSAS exposes its internal state through a couple of $SYSTEM DMVs (think system views), and querying those is the one place a regular relational tool fits, so the library leans on plain Dapper for it:

public IEnumerable<SsasLock> GetSsasLocks(string databaseName = null, ...)
{
    using var connection = GetConnection();
    connection.Open();

    return from @lock in connection.Query<SsasLock>("SELECT * FROM [$SYSTEM].[DISCOVER_LOCKS]")
           join session in connection.Query<SsasSession>("SELECT * FROM [$SYSTEM].[DISCOVER_SESSIONS]")
               on @lock.SPID equals session.SESSION_SPID
           where string.IsNullOrWhiteSpace(databaseName)
                 || session.SESSION_CURRENT_DATABASE == databaseName
           select @lock with { Session = session };
}

On top of that you get the friendly helpers: IsProcessing("Sales") tells you whether a refresh is in progress, and CancelProcessing("Sales") finds the sessions holding read/write locks and politely cancels them. The management methods we’ll see next all call IsProcessing first and refuse to mutate a model that’s busy - which has saved me from more than one self-inflicted wound. 😅

Managing roles and security from code

Need to grant someone read access to a model, or spin up a role as part of an onboarding flow? ManageDatabaseRoles gives you a small, sane surface over TOM’s role model:

var roles = ssas.ManageDatabaseRoles("Sales");

roles.CreateRole("Analysts", "Read-only analysts", SsasRolePermission.Read);

// Add Azure AD members to the role
roles.AddExternalMembers("Analysts", "jane@contoso.com", "john@contoso.com");

// Inspect, update, remove - it's all there
var current = roles.Roles;
roles.RemoveMembers("Analysts", "john@contoso.com");
roles.DeleteRole("Analysts");

Behind the scenes this is TOM walking db.Model.Roles, mutating it, and calling SaveChanges(). Reads are cached for a few minutes with a MemoryCache (roles don’t change every second), and the cache entry is dropped the moment you write, so you never read stale data right after a change. External members are added as AzureAD identities, which is exactly what you want for AAS and Fabric.

The bit that actually saves money: pause, resume, scale

Here’s a feature on-prem SSAS never had and cloud SSAS very much does: you pay for it while it’s running. An Azure Analysis Services or Fabric capacity sitting idle overnight is just money on fire. So why not pause it from .NET when nobody’s looking?

// Scale up before the nightly refresh, scale back down after.
await ssas.ScaleAsync("S1");

// Park the server overnight...
await ssas.PauseServerAsync();

// ...and bring it back in the morning.
await ssas.StartServerAsync();

For Azure Analysis Services these map onto the Azure.ResourceManager.Analysis SDK - Suspend, Resume, and an Update for the SKU tier. For Fabric, there’s a FabricCapacityManager doing the equivalent against the Fabric APIs. And there’s a lovely quality-of-life touch: when the server is configured for Fabric, simply connecting will auto-resume a paused capacity, so a query that arrives after bedtime wakes the server up instead of throwing. Pause aggressively, query without fear.

(Note: PauseServerAsync checks IsProcessing first and refuses to pull the rug out from under a running refresh - because of course it does.)

The escape hatch

No abstraction covers 100% of a system this big, and pretending otherwise is how you end up fighting your own framework. For the 1% of cases where you need to send raw XMLA - some obscure command the nice methods don’t wrap - SendXmlaRequestAsync lets you do exactly that, and it still handles the Azure-vs-on-prem transport difference and EffectiveUserName injection for you. The clean API for everyday work, the trapdoor for the weird day.

Here’s a genuinely fun one from the trenches: say your client wants one of those slick front-end pivot-table components - the kind where users drag-and-drop dimensions and slice a cube live in the browser. Those grids speak XMLA natively and are perfectly happy chatting with your SSAS on their own… right up until the day you need them to do something they don’t support out of the box - inject an EffectiveUserName for row-level security, sneak in an extra filter, or otherwise massage the request before it reaches the server. So you proxy that traffic through your backend, intercept the raw XMLA the grid generated, add your secret sauce, and forward it on with SendXmlaRequestAsync. The pivot table is none the wiser, and your client gets the “extra feature” the off-the-shelf widget swore wasn’t possible. 😎

Ending notes

And that’s the tour! In one interface we covered querying with DAX, refreshing models, watching what the server is doing, managing security, and pausing the whole thing to save money - across localhost, Azure Analysis Services, and Fabric, with the same code.

A few ideas if you want to take it further:

• Wrap pause/resume in a scheduler. Remember decoupling schedulers? A nightly job that scales up, refreshes, then pauses the capacity is the single highest-ROI thing you can build here.
• Push refresh progress to the client. Pair IsProcessing with a socket-based notification (throttling system 👀) and you’ve got real-time “your report is updating…” status bars.
• Add MDX or query-result caching in front of Query<T> for dashboards that ask the same thing every five seconds.
• Generate your POCOs from the model metadata - DatabaseStructure already exposes tables, columns and partitions, so half the work is done.

Now, the next time someone needs SSAS touched from a .NET app, you won’t have to open forty tabs. You’ll just open one interface.

💾 See the full example on Github!

Thanks for reading!