Configuring Microsoft SQL Server Analysis Services on Amazon RDS for SQL Server

January 7th, 2022: This blog was updated after Multidimensional mode was added to the Amazon RDS for SQL Server SSAS offering.

You can now configure Microsoft SQL Server Analysis Services (SSAS) in the Tabular or Multidimensional mode on Amazon RDS for SQL Server. SSAS works in the Single-AZ configuration for both Standard and Enterprise editions using either the 2016, 2017 or 2019 SQL Server Major version.

If you run SSAS on Amazon EC2, you can save on costs by supporting SSAS directly on Amazon RDS for SQL Server and consolidating those workloads to run on the same RDS DB instance as your SQL Server database. However, you must account for a performance impact if you decide to consolidate.

Online Analytical Processing (OLAP) solutions enhance data warehouses and other relational databases by minimizing the amount of on-the-fly processing for fast and effective analysis and reporting. This is mostly achieved by preprocessing and storing a wide range of combinations of dimensions and hierarchies before you perform any analysis.

SSAS is a Microsoft business intelligence tool for developing enterprise-level OLAP solutions. In addition to optimized analytical queries and calculations, SSAS provides semantic data models that client applications require, such as Excel, Power BI, SQL Server Reporting Services (SSRS), and other reporting and data visualization tools. The Tabular mode supports in-memory databases designed for efficient column-based queries and offers better data compression. The Multidimensional mode leverages cubes and dimensions that can be annotated and extended to support complex query constructions.

This post describes how to configure and use SSAS in the Tabular or Multidimensional mode on Amazon RDS for SQL Server DB instances.

SSAS on Amazon RDS for SQL Server

To configure SSAS on Amazon RDS for SQL Server, you must meet the following requirements:

The Amazon RDS for SQL Server instance must be SQL Server Standard or Enterprise editions under following engine versions.

For Tabular mode	For Multidimensional mode
13.00.5426.0.v1 and above	13.00.5882.1.v1 and above
14.00.3223.3.v1 and above	14.00.3381.3.v1 and above
15.00.4043.16.v1 and above	15.00 does not support Multidimensional mode

The instance must be joined to an AWS Managed Microsoft Active Directory to enable Windows Authentication. For instructions, see Setting Up Windows Authentication for SQL Server DB Instances.
You must enable Amazon S3 integration for transferring SSAS models and backups between the instance and an S3 bucket. You must have a valid login to the Microsoft SQL Server for Amazon RDS instance with permissions to download files from Amazon S3. For instructions, see Integrating an Amazon RDS for SQL Server DB Instance with Amazon S3.

Creating an option group with the SSAS option

For instructions on creating an option group with the SSAS option, see Working with Option Groups. Include the following steps:

For Option name, choose SSAS.
For Max memory, specify the upper memory percentage threshold that your SSAS occupies your instance of Microsoft SQL Server for Amazon RDS.
When setting the max memory, consider the amount of memory available on the RDS DB instance. SSAS Tabular is a memory-intensive application. If you choose a higher max memory allocated to SSAS, you potentially impact SQL Server operations residing on the same instance.
For Mode, specify the server mode of SSAS. Note that you can only use one SSAS mode at a time. For changing the mode or removing the SSAS option, make sure to delete all of the existing SSAS databases, or you will receive an error. If you do not see the Mode option, this means that your major engine version does not support Multidimensional mode (sqlserver-ee 15.00, sqlserver-se 15.00). The default value is Tabular.
For Security groups, choose from the list of your security groups or create a new one. Each DB instance must have at least one associated security group. Make sure that the associated security group allows inbound traffic for the SSAS port.

Connecting to SSAS

After you add the SSAS option, you should be able to connect to SSAS on the RDS instance. To connect to SSAS, you must be logged into a domain-joined computer as a domain user. Complete the following steps:

Launch SQL Server Management Studio (SSMS).
From the File menu, choose Connect Object Explorer…
A window appears to connect to the server instance.
For Server type, choose Analysis Services.
For Server name, enter the endpoint for your RDS instance.
For Authentication, choose Windows Authentication.
Choose Connect
After a few moments, SSMS connects to SSAS on your DB instance. If you cannot connect to SSAS, check that the instance security group allows traffic from your computer on port 2383. For more information, see Security Group Considerations.

Deploying and processing SSAS project

You cannot deploy SSAS project directly from SSDT to an RDS DB instance. To deploy SSAS project, you must move your model files to the DB instance and run the SSAS stored procedure.

Create a new project in SQL Server Data Tools (or open a project that you have created previously).
Create the required roles in your project and add the domain members to the roles.
For a domain user to perform operations on the deployed model, the user should be added to the project. Make sure that the domain user can connect to the SSAS on the RDS instance.
On the Solution Explorer tab, choose the solution and bring up properties.
Under Deployment Options, for Processing Option, choose Do Not Process.
After you deploy the model, you can process your model directly through SSMS.
On the Solution Explorer tab, build your project.
When you build the SSAS project, SQL Server Data Tools generates the file in the output folder of the Analysis Services project (the default output folder is \Bin). For deployment, you require <project name>.asdatabase and <project name>.deploymentoptions files.
Create an S3 bucket (or use an existing one). This post uses sample-s3-bucket.
Upload the project files to that bucket.
Make sure that Amazon S3 integration is already enabled for the instance.
Open SSMS and connect to your RDS SQL Server instance.

Run the following stored procedure to download the project files from the S3 bucket to the local Amazon S3 folder on the instance:

exec msdb.dbo.rds_download_from_s3 @s3_arn_of_file='arn:aws:s3:::sample-s3-bucket/testmodel.asdatabase' , @rds_file_path='d:\S3\testmodel.asdatabase' , @overwrite_file=1
exec msdb.dbo.rds_download_from_s3 @s3_arn_of_file='arn:aws:s3:::sample-s3-bucket/testmodel.deploymentoptions' , @rds_file_path='d:\S3\testmodel.deploymentoptions' , @overwrite_file=1

To track the status of your Amazon S3 integration tasks, call the rds_fn_task_status function until their status changes to SUCCESS. The function takes two parameters. Set the first parameter to NULL and the second parameter to the task ID (if you pass 0 as the task ID, it shows a list of all tasks). See the following code:
```
SELECT * FROM msdb.dbo.rds_fn_task_status(null,2);
```

Call the SSAS_DEPLOY_PROJECT stored procedure to deploy the model into SSAS. See the following code:

exec msdb.dbo.rds_msbi_task @task_type='SSAS_DEPLOY_PROJECT', @file_path='d:\S3\testmodel.asdatabase';

After the SSAS_DEPLOY_PROJECT task status changes to SUCCESS in SSMS, connect to the Analysis Services and use Windows Authentication to connect to SSAS. You will then see your deployed project under Databases as you see in the screenshots below.
If you cannot see your database in the Databases section, it probably means that your domain user doesn’t have database-level access to the database. You must add your username to the model and redeploy the updated model or add an admin user through the SSAS_ADD_DB_ADMIN_MEMBER stored procedure.
To process the data, update the connection string of the SSAS database.

For Tabular mode:

1. Expand the Connections tab.
2. Open the context (right-click) menu for the connection object, and then choose Properties.
3. Update the user name and password in the connection string.

For Multidimensional mode:

1. Expand the Data Sources tab.
2. Open the context (right-click) menu for the data source object, and then choose Properties.
3. Update the user name and password in the connection string.

In SSMS, open the deployed SSAS database (right-click).
Choose Process Database.
Processing time depends on the amount of data imported from the data source.

Other operations

The following section discusses other operations you can perform, such as adding an admin member to a role, and backing up and restoring an SSAS database.

Adding an admin member to a role

To grant DB-level admin access to a domain user for a particular database, enter the following code:

exec msdb.dbo.rds_msbi_task @task_type='SSAS_ADD_DB_ADMIN_MEMBER', @database_name='TestModel', @ssas_role_name='Sample', @ssas_role_member='domain\user';

The command creates a role if it doesn’t exist.

Backing up an SSAS database

You can directly back up the SSAS database through SSMS to the Amazon S3 folder or run the SSAS_BACKUP_DB stored procedure. See the following code:

exec msdb.dbo.rds_msbi_task @task_type='SSAS_BACKUP_DB', @database_name='TestModel', @file_path='d:\S3\test-model.abf', @ssas_overwrite_file=1;

Restoring an SSAS database

To restore an SSAS database from a backup file, call the SSAS_RESTORE_DB stored procedure. See the following code:

exec msdb.dbo.rds_msbi_task @task_type='SSAS_RESTORE_DB', @database_name='NewTestModel', @file_path='d:\S3\test-model.abf';

You cannot restore a database if another database with the same name already exists.

Summary

This post showed you how to configure SSAS in the Tabular or Multidimensional mode on Amazon RDS for SQL Server. If you run SSAS on Amazon EC2, you can save on costs by consolidating those workloads to run on the same RDS DB instance as your SQL Server database. You can easily process data from data warehouses and other relational databases (on premises or cloud-based), perform analytical queries, and back up or restore your SSAS databases. Try out SSAS today in the AWS Console, and share your thoughts and experiences in the comments.

About the Authors

Pooya Amini is a Software Development Engineer with Amazon Web Services. He works with the AWS RDS team, focusing on commercial database engines and SQL Server. Before joining AWS, Pooya was a Software Development Engineer at Amazon Payments and Alexa. He enjoys working on technical challenges and is passionate about learning new technologies.

Richard Waymire is a Principal Outbound Architect at Amazon Web Services. He works with AWS customers to provide guidance and technical assistance on database projects, helping them improve the value of their solutions when using AWS.

Kyle Kim is a Software Development Engineer with Amazon Web Services. He works with the AWS RDS team, focusing on commercial database engines and SQL Server. Kyle joined AWS following his graduation at the University of Toronto, Applied Science and Engineering. He now leads developing critical solutions across AWS, with focus towards back-end, distributed systems.