SQL Server Analysis Services gets installed far less often than the Database Engine, which is exactly why it trips people up: the setup wizard asks one question you can't easily undo, the service account needs directory permissions nobody remembers to check, and then someone asks "can we just put it in the Availability Group with everything else?" The answer to that last one is no, and understanding why is as important as getting the installation right.
The One Setup Decision You Can't Casually Change
During installation, SSAS asks for a server mode: Multidimensional, Tabular, or the legacy SharePoint (PowerPivot) mode. This is written once into msmdsrv.ini as <DeploymentMode> and Microsoft does not officially support changing it afterwards, the documented path is uninstall and reinstall with the correct mode.
| Mode | Storage engine | Typical use case |
|---|---|---|
| Multidimensional (OLAP) | Cubes, MDX | Traditional data warehouse cubes, complex hierarchies |
| Tabular | In-memory columnar (VertiPaq) or DirectQuery, DAX | Modern self-service BI, Power BI-aligned models |
| SharePoint (PowerPivot) | Legacy, tied to SharePoint farm | Only relevant for old SharePoint BI deployments |
Multidimensional and Tabular use incompatible storage formats. If someone picks the wrong mode during setup and only notices once databases are already deployed, there is no in-place fix, only migration or reinstall.
Data directory has no database folders yet, editing <DeploymentMode> in msmdsrv.ini and restarting the service does correct the mode, this is what Set-sqmSsasDeploymentMode automates, including a mandatory check that refuses to proceed if it finds existing databases (unless you force it, at your own risk).
Installing SSAS with SQLSetupTool
Rather than clicking through the SSAS branch of SQL Server setup by hand, SQLSetupTool's optional-components screen lets you check "SSAS (Analysis Services)" alongside SSRS, SSIS, and SSMS in the same run. It automatically:
- Points the installer at the correct
SQL_Installsource folder for the selected SQL Server version, no separate media copy needed. - Applies the same collation/sort order as the rest of the instance, since mismatched sort orders between SSAS and the relational engine it sources data from cause silent comparison and join issues in processing queries.
- Runs SSAS installation as one step in the same orchestrated sequence as the other optional components, with the same source-file validation that blocks the whole run early if installer media is missing, rather than failing halfway through.
What it does not do, and nothing should: pick the server mode for you. That decision depends on what the workload actually is, and picking it wrong is the expensive mistake described above.
Post-Install Configuration: Directory Permissions
SSAS needs full control over four directories tied to the instance: Data, Log, Temp, and Backup, all registered in the instance's registry key under HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS*. Missing permissions here show up as cryptic file-access errors during processing or backup, well after installation, when it's least obvious why.
Test-sqmSsasDirectoryPermissions resolves the service account automatically from the Windows service, reads all four directory paths from the registry, and grants FullControl wherever it's missing. It's idempotent, safe to run repeatedly as a post-install check or after moving a directory.
Why AlwaysOn Availability Groups Do Not Apply Here
This is the question that comes up in almost every SSAS HA conversation: Availability Groups are a Database Engine feature. The "Add Database to Availability Group" wizard only ever lists relational databases, an SSAS database (Multidimensional cube or Tabular model) is not a SQL Server database in that sense at all, it's a set of files managed by the msmdsrv.exe process, and there is no AG mechanism that understands or replicates that storage.
This is also where the naming genuinely confuses people: "AlwaysOn" is a marketing umbrella covering two unrelated technologies. Availability Groups (log-stream based, listener, readable secondaries) is one of them. Failover Cluster Instances (WSFC-based, shared storage, active/passive) is the other, and SSAS can be installed as a clustered FCI resource. If your AlwaysOn conversation is really about SSAS, you're talking about the FCI half, not Availability Groups. See Failover Clustering vs. SQL AlwaysOn for the full breakdown of that distinction.
What Actually Protects SSAS
- Failover Cluster Instance (FCI): SSAS installed as a clustered role on shared storage (or S2D/storage replicas). One node is active at a time; on failure, WSFC moves the disk resource and restarts
msmdsrv.exeon the surviving node. This is the actual AlwaysOn-branded option that applies to SSAS. - Database synchronization (Multidimensional): Process cubes on a dedicated processing instance, then use
Synchronize Database(AMO or XMLA) to push the processed database to one or more query-only instances behind a load balancer. This is a scale-out/read-availability pattern, not automatic failover. - Regular backups: SSAS has its own backup command (
.abffiles via AMO/XMLA or SSMS), independent of relational-engine backups. Keep taking them; neither FCI nor synchronization replaces a backup strategy.
Common Gotchas
- Trying to add an SSAS database to an existing AG. It won't appear in the database list, if you're looking for it, you've mixed up FCI with Availability Groups.
- Wrong SERVERMODE discovered after databases exist. At that point it's migration (export/import via backup-restore, or rebuild the model) rather than a config edit.
- Mismatched collation between SSAS and the source relational instance. Causes subtle, hard-to-diagnose comparison failures during processing rather than an obvious error.
- Service account missing directory permissions after someone manually relocates
DataorBackuppost-install without re-granting access.
The Bottom Line
SSAS installation has exactly one decision that's expensive to get wrong: server mode. Get that right up front, let SQLSetupTool handle the mechanical install alongside your other components with matching collation, and use Test-sqmSsasDirectoryPermissions and Set-sqmSsasDeploymentMode from sqmSQLTool for the post-install checks and the one recoverable mistake. And when "AlwaysOn" comes up for SSAS specifically, remember it means Failover Clustering, not Availability Groups, the database engine's HA story simply doesn't extend here.